JPA自定义查询@Query分页

关键字:countQuery

持久层：

@Query(value = "SELECT area_id,org_name,update_name,update_user,sum(success_count) AS success_count," +
            "sum(error_count) AS error_count,sum(all_call) AS all_call,sum(all_count) AS all_count FROM V_PHOTO_CALLBACK a " +
            "WHERE area_id=?1 AND end_time>=?2 AND end_time<=?3 GROUP BY area_id,org_name,update_name,update_user",
            countQuery = "select count(*) from (SELECT area_id,org_name,update_name,update_user,sum(success_count) AS success_count," +
                    "sum(error_count) AS error_count,sum(all_call) AS all_call,sum(all_count) AS all_count FROM tableName a " +
                    "WHERE area_id=?1 AND end_time>=?2 AND end_time<=?3 GROUP BY area_id,org_name,update_name,update_user)",
            nativeQuery = true)
    Page<PhotoCallback> query(String areaId, String beginTime, String endTime, Pageable pageable);

服务层：

@Override
    public Map<String,Object> query(String areaId, String beginTime, String endTime, Pageable pageable) {
        Map<String,Object> map = new LinkedHashMap<>();
        int pageMin = pageable.getPageNumber() * pageable.getPageSize();
        int pageMax = pageMin + pageable.getPageSize();
        Pageable page = PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), Sort.Direction.DESC, "update_user");
        Page<PhotoCallback> all = photoCallbackRepository.query(areaId, beginTime, endTime, page);
        map.put("content",all.getContent());
        map.put("totalElements",all.getTotalElements());
        return map;
    }

 

遇到的问题：

1、手工分页，无查询总条数返回。使用Page<T> 、countQuery="select count(*) from ..."获得总条数

2、当多表查询、嵌套查询时，Pageable使用遇到问题，总是会获取第一个表的别名为查询字段的前缀。（不建议使用：查询结果返回List，用subList截取结果）

int fromIndex = pageable.getPageNumber() * pageable.getPageSize();
int toIndex = fromIndex + pageable.getPageSize();
maps.put("content", lists.subList(fromIndex, lists.size() > toIndex ? toIndex : lists.size()));
maps.put("totalElements", lists.size());