JPA multi-table query mapping is abnormal

Blog index

Recently with the JPA multi-table query a problem javax.mail. Persistence. PersistenceException: org. Hibernate. MappingException: Unknown SqlResultSetMapping [Record]. Because you want to return a custom object using a multi-table join query.

Environment: JPA 2.2.8

Restore scenario: Tables A,B

@Entity public Class A{ @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String name ; private String age; . Omit the getset} @Entity public Class B{ @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private Long a_id; // Id of table A private String like; private String hate; . Omit the getset
}
Copy the code

Return A custom object Record: each contains A,B part of the attributes

public Class Record{ private Long a_id; private Long b_id; private String name ; private String like; . Omit the getset
}
Copy the code

The DAO is as follows:

@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();
Copy the code

Then the question arises, javax.mail. Persistence. PersistenceException: org. Hibernate. MappingException: Unknown SqlResultSetMapping [Record]. This should make it impossible to convert query results into custom records. So the first method is to return an Object Object or Object[] DAO and change it to:

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

Then manually traverse to assign values to custom classes.

Second thought: I thought a framework would take these issues into account, and then I did a search on StackOverflow and found a @sqlResultsetMapping annotation to solve the problem.

/** 
 * 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 * / @ the Repeatable (SqlResultSetMappings. Class) @ Target ({TYPE}) @ Retention (RUNTIME) public @ interface SqlResultSetMapping {...}Copy the code

The discovery comment contains the specified mapping of native SQL query results or stored procedures, that is, the results of the query can be mapped according to the corresponding rules. Comments happen to have a demo.

Don’t keep in suspense, directly on the solution process: modify class 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; . Omit the getset
}
Copy the code

The DAO is modified as follows:

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();
Copy the code

As for the principle, there is no time to study it. I’ll have time to make it up next time. But my guess is that the query results in an Object[] type and then assigns the result to the custom type via the @SQLResultSetMapping annotation that defines the return value type.

If you have any further questions, please leave a message or contact me at [email protected]