spring data jpa多表聚合分组查询

 

spring data jpa用于单表操作较为友好，多表关联，而且涉及到聚合，分组，排序，分页，可使用原生sql形式，对于结果的映射接收，没有相应的entity可以接收，定义DTO无法接收。

 

故手动去封装结果集。

 

 

       
    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;
    }