As anyone who has used JPA knows, we can easily receive the results of a single table Query that returns a field or an entity class using the @Query annotation. However, if we carry out multi-table associated query, the queried fields are not necessarily in the same table, so it cannot be received by entity class. For such query results, there are three ways to deal with them.

Receiving with Object[] looks poor and has to be evaluated by an array index. It’s too low and a bunch of code doesn’t look good. If only do some statistics class queries can barely accept.

  1. Receive with Map
    ,>

This method is relatively better than Object[], because the map key is the name of the field we query, and then we can directly use the GET method to value, which is more convenient, but it is still worse than using a POJO directly to receive the results. How to convert the result into a custom object, there are some articles on the web that do this by first receiving a map, then translating the map into JSON, and finally parsing the JSON into a custom POJO (see JPA custom Object receiving query result set _klayer_cong blog -CSDN blog). This may seem like a bit of a hassle, but it’s easier to understand, and it does turn into a custom POJO. It makes sense if it’s just a single query result, but if it’s a large number of queries, it will affect performance, which is not good.

  1. Use the @ NamedNativeQuery + @ SqlResultSetMapping processing

The concrete implementation is as follows:

(1) the pojo

@NamedNativeQuery( name = "querySummary", query = " select t2.city_cd as cityCd,t2.city_name as cityName,DATE_FORMAT(t1.create_time,'%Y-%m-%d') as createDate,t1.sale_type as saleType " + " from tb_project_sale t2 " + " left join tb_project_sale_core t1 on t2.id = t1.id " + " where t1.sale_type is not null ", resultClass = SummaryDto.class, resultSetMapping = "summaryMapping")
@MappedSuperclass
@SqlResultSetMapping( name = "summaryMapping", classes = @ConstructorResult( targetClass = SummaryDto.class, columns = { @ColumnResult(name = "cityCd", type = String.class), @ColumnResult(name = "cityName", type = String.class), @ColumnResult(name = "createDate", type = String.class), @ColumnResult(name = "saleType", type = String.class), } ) )
@AllArgsConstructor
@NoArgsConstructor
@Setter
@Getter
public class SummaryDto {

    private String cityCd;

    private String cityName;

    private String createDate;

    private String saleType;

}
Copy the code

(2) the repository

@Repository
@Transactional
public class SummaryRpt {

    @PersistenceContext
    private EntityManager entityManager;

    public List<SummaryDto> getSummary(a) {
        return entityManager.createNamedQuery("querySummary") .getResultList(); }}Copy the code

(3) Test classes

@RunWith(SpringRunner.class @SpringBootTest public class SummaryServiceImplTest { @Autowired private SummaryRpt summaryRpt; @Test public void test() { List
      
        list = summaryRpt.getSummary(); return; }}
      Copy the code

Note: The comments in the POJO above are useful, don’t cut them out

This paper reference: my.oschina.net/joryqiao/bl…