spring data jpa多表聚合分组查询

 

spring data jpa用于单表操作较为友好，对于实现部分查询，以及多表关联，而且涉及到聚合，分组，排序，分页，可使用原生sql形式，对于结果的映射接收，普通的DTO对象无法正常接收，可以手动映射或者定义Entity；

 

1）手动去封装结果集

       
    public List<StatisticItemNameVo> statisticItemName(String modality, Timestamp queryDate) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT e.item_name as itemName,count( * ) as itemNameCount ");
        sb.append("FROM ris_examine_info e ");
        sb.append("LEFT JOIN ris_report_info r ON e.examine_no = r.examine_no ");
        sb.append("WHERE ");
        sb.append("e.modality = ? ");
        sb.append("AND to_days(r.audit_date) = to_days(?) ");
        sb.append("GROUP BY e.item_name ");
        String sql = sb.toString();
        Query nativeQuery = this.entityManager.createNativeQuery(sql.toString());
        nativeQuery.setParameter(1, modality);
        nativeQuery.setParameter(2, queryDate);
        List resultList = nativeQuery.getResultList();
        ArrayList<ItemNameAndCount> listQuery = Lists.newArrayList();
        for (Object row : resultList) {
            ItemNameAndCount itemNameAndCount = new ItemNameAndCount();
            Object[] cells = (Object[]) row;
            String name = (String) cells[0];
            BigInteger count = (BigInteger) cells[1];
            itemNameAndCount.setItemName(name);
            itemNameAndCount.setItemNameCount(count.longValue());
            listQuery.add(itemNameAndCount);
        }

        List<StatisticItemNameVo> list = getStatisticItemNameVos(listQuery);
        return list;
    }
　　

 

 

 

2）将查询结果自定义成Entity

    @Test
    public void test1(){

        String sql = " select id, count(*) as count, age, sex from student group by age,sex ";
        Query nativeQuery = entityManager.createNativeQuery(sql,TrialQueryResult.class);
        List<Student> resultList = nativeQuery.getResultList();

    }

 

@Data
@Entity
public class Student {

    @Id
    private Integer id;

    private Integer age;

    private Integer sex;
    
    private Integer count;
}

数据库表并没有student这张表，是自定义的类，但是需要标识为Entity，并且需要标识主键id；
此外sql的查询结果需要满足这个类的全部字段，否则报错；
此外还要注意字段名称要跟sql结果列的名称一致，不一致需要@Column进行映射