jpa mysql分页查询,spring data jpa 分页查询

@Service

@Transactionalpublic class IncomeService{/*** 实体管理对象*/@PersistenceContextEntityManager entityManager;public PagefindIncomeDailysByPage(PageParam pageParam, String cpId, String appId, Date start, Date end, String sp) {

StringBuilder countSelectSql= newStringBuilder();

countSelectSql.append("select count(*) from IncomeDaily po where 1=1 ");

StringBuilder selectSql= newStringBuilder();

selectSql.append("from IncomeDaily po where 1=1 ");

Map params = new HashMap<>();

StringBuilder whereSql= newStringBuilder();if(StringUtils.isNotBlank(cpId)){

whereSql.append(" and cpId=:cpId ");

params.put("cpId",cpId);

}if(StringUtils.isNotBlank(appId)){

whereSql.append(" and appId=:appId ");

params.put("appId",appId);

}if(StringUtils.isNotBlank(sp)){

whereSql.append(" and sp=:sp ");

params.put("sp",sp);

}if (start == null)

{

start= DateUtil.getStartOfDate(newDate());

}

whereSql.append(" and po.bizDate >= :startTime");

params.put("startTime", start);if (end != null)

{

whereSql.append(" and po.bizDate <= :endTime");

params.put("endTime", end);

}

String countSql= newStringBuilder().append(countSelectSql).append(whereSql).toString();

Query countQuery= this.entityManager.createQuery(countSql,Long.class);this.setParameters(countQuery,params);

Long count=(Long) countQuery.getSingleResult();

String querySql= newStringBuilder().append(selectSql).append(whereSql).toString();

Query query= this.entityManager.createQuery(querySql,IncomeDaily.class);this.setParameters(query,params);if(pageParam != null){ //分页

query.setFirstResult(pageParam.getStart());

query.setMaxResults(pageParam.getLength());

}

List incomeDailyList =query.getResultList();if(pageParam != null) { //分页

Pageable pageable = newPageRequest(pageParam.getPage(), pageParam.getLength());

Page incomeDailyPage = new PageImpl(incomeDailyList, pageable, count);returnincomeDailyPage;

}else{ //不分页

return new PageImpl(incomeDailyList);

}

}/*** 给hql参数设置值

*@paramquery 查询

*@paramparams 参数*/

private void setParameters(Query query,Mapparams){for(Map.Entryentry:params.entrySet()){

query.setParameter(entry.getKey(),entry.getValue());

}

}

}