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