JPA 多表查询映射异常

博客索引

最近用JPA多表查询出现一个问题javax.persistence.PersistenceException: org.hibernate.MappingException: Unknown SqlResultSetMapping [Record]。由于使用多表连接查询想返回一个自定义的对象。

环境： JPA 2.2.8

还原场景：表A,B

@Entity
public Class A{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name ;
    private String age;
    ...省略get set
}

@Entity
public Class B{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long a_id;//对应A表的id
    private String like ;
    private String hate;
    ...省略get set
}

返回自定义对象Record:各包含A,B部分属性

public Class Record{
    private Long a_id;
    private Long b_id;
    private String name ;
    private String like;
    ...省略get set
}

DAO如下：

@PersistenceContext
private EntityManager em;

String SQL ="select a.id as a_id,a.name as name , b.id as b_id, b.like as like from A a,B b where a.id =b.a_id;";
List<Record> adminLists = em.createNativeQuery(SQL).getResultList();

然后问题出现了，javax.persistence.PersistenceException: org.hibernate.MappingException: Unknown SqlResultSetMapping [Record]。这应该是无法将查询结果转换为自定义的Record。于是第一种方法就是返回一个Object对象或者Object[] DAO修改为：

List<Object[]> adminLists = em.createNativeQuery(SQL).getResultList();

然后手动遍历，去给自定义的类赋值。

第二个思路：我想一个框架应该会考虑到这些问题，然后通过一番搜索，stackoverflow上面有相关问题发现有个注解@SqlResultSetMapping能解决问题，查看该注解源码：

/** 
 * Specifies the mapping of the result of a native SQL query or stored 
 * procedure.
 *
 * <pre>
 *    Example:
 *
 *    Query q = em.createNativeQuery(
 *        "SELECT o.id AS order_id, " +
 *            "o.quantity AS order_quantity, " +
 *            "o.item AS order_item, " +
 *            "i.name AS item_name, " +
 *        "FROM Order o, Item i " +
 *        "WHERE (order_quantity &gt; 25) AND (order_item = i.id)",
 *    "OrderResults");
 *    
 *    &#064;SqlResultSetMapping(name="OrderResults", 
 *        entities={ 
 *            &#064;EntityResult(entityClass=com.acme.Order.class, fields={
 *                &#064;FieldResult(name="id", column="order_id"),
 *                &#064;FieldResult(name="quantity", column="order_quantity"), 
 *                &#064;FieldResult(name="item", column="order_item")})},
 *        columns={
 *            &#064;ColumnResult(name="item_name")}
 *    )
 * </pre>
 *
 * @see Query
 * @see StoredProcedureQuery
 * @see NamedNativeQuery
 * @see NamedStoredProcedureQuery
 *
 * @since 1.0
 */
@Repeatable(SqlResultSetMappings.class)
@Target({TYPE}) 
@Retention(RUNTIME)
public @interface SqlResultSetMapping { 
···
}

发现注释上写着指定的映射原生SQL查询结果或者储存过程，也就是可以将查询出来的结果按照对应的规则进行映射。注释正好有个demo。

不卖关子了，直接上解决流程：修改类Record;

@SqlResultSetMapping(name = "findRecords",
        entities = {
                @EntityResult(entityClass = com.xxx.Record.class, fields = {
                        @FieldResult(name = "a_id", column = "a_id"),
                        @FieldResult(name = "b_id", column = "b_id"),
                        @FieldResult(name = "name", column = "name"),
                        @FieldResult(name = "like", column = "like")
                })}
)
@Entity
public Class Record{
    @Id
    private Long a_id;
    private Long b_id;
    private String name ;
    private String like;
    ...省略get set
}

DAO修改如下:

String SQL ="select a.id as a_id,a.name as name , b.id as b_id, b.like as like from A a,B b where a.id =b.a_id;";
List<Record> adminLists = em.createNativeQuery(SQL,"findRecords").getResultList();

至于原理，暂时还没有时间去研究。下次有时间补上。但是我猜测：查询的结果是一个Object[]类型，然后通过@SqlResultSetMapping注解定义的返回值类型，把结果给自定义类型赋值。

如果还有疑问，请留言或者通过邮箱联系我creazycoder@sina.com

记录一个jpa 多表查询返回自定义对象异常

JPA 多表查询映射异常

记录一个jpa 多表查询返回自定义对象异常