项目场景:
项目场景:使用JPA做多表联合查询,使用PageImpl<>(content,pageable,total)封装分页数据;
问题描述:
在访问最后一页时,数据总是虚高,数据总条数总是会变多(ps:这里没注意当前返回的条数,不然可能就不会有这个错误了)
ServiceImpl:错误的原生语句查询
@Override
public Page<DTO> findRecordsByPage(DTO recordDTO) {
if(recordDTO==null){
recordDTO=new DTO();
}
Pageable pageable=PageRequest.of(1,2);
StringBuilder selectSql = new StringBuilder("...查询语句");
StringBuilder countSelectSql = new StringBuilder("...查询总数sql");
Query countQuery = entityManager.createNativeQuery(countSelectSql.toString());
Query selectQuery = entityManager.createNativeQuery(selectSql.toString());
Long total = Long.parseLong(countQuery.getSingleResult().toString());
if(recordDTO.getPage() - 1<=0){
recordDTO.setPage(1);
}
int limit = (recordDTO.getPage() - 1) * recordDTO.getSize();
selectQuery.setFirstResult(limit);
selectQuery.setMaxResults(recordDTO.getSize()); selectQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(DTO.class));
List<DTO> resultList = selectQuery.getResultList();
Page<DTO> page = new PageImpl<>(resultList, pageable, total.longValue());
return page;
}
原因分析:
JPA默认的分页是从0开始的,如果用的sql是原生Sql且分页按照日常分页逻辑从1开始(即(page-1)size),那么在进行分页的时候,new PageImpl(list,pageable,total)分页当访问最后一页,JPA为了缓解数据的不一致,total会按照默认的从0开始自动去算出应该有的条数,即会变化为(pagesize+该页的条数)。
解决方案:
使用自带的Pageable或者在进行分页的时候跳过的条数使用page*size
@Query(value=“selectSql”,countQuery=“countSql”,nativeQuery = true)
Page<Map<String,Object>> findRecordsByPage(String sceneName, BigInteger plantId, Pageable pageable);
不过返回结果的字段必须和实体类中完全一致,若不一致会报错
可以采用Page<Map<String,Object>>来接收返回值,然后用CGlib的BeanMap.create()方法封装,示例代码:
public <T> T map2Bean(Map<String, Object> map,Class<T> clazz) throws Exception {
T bean = clazz.newInstance();
BeanMap beanMap = BeanMap.create(bean);
beanMap.putAll(map);
return bean;
}
参考: https://docs.spring.io/spring-data/commons/docs/2.4.6/api/