参考:https://blog.csdn.net/qq_43948440/article/details/110877496
一,通用方法
/** * 无参查询,sql语句 * @param querySql sql * @param pageable Pageable.unpaged() 代表不分页 * @param destType 需要映射的类型 * @param <U> * @return */ @Override public <U> Page<U> findPage(String querySql, Pageable pageable,final Class<U> destType) { Query query=em.createNativeQuery(querySql); Page<U> page=findPage(query,pageable,destType); return page; } /** * 无参查询,sql语句 * @param quer javax.persistence.Query * @param pageable Pageable.unpaged() 代表不分页 * @param destType 需要映射的类型 * @param <U> * @return */ @Override public <U> Page<U> findPage(Query query, Pageable pageable, Class<U> destType) { String countSql = "SELECT COUNT(*) FROM ( "+ ((NativeQueryImpl) query).getQueryString() +" ) getcount"; Query countQuery = em.createNativeQuery(countSql); for (Parameter param : query.getParameters()) { countQuery.setParameter(param.getName(),query.getParameterValue(param.getName())); } BigInteger totalCount = (BigInteger) countQuery.getSingleResult(); if(pageable.isPaged()) { query.setFirstResult((int) pageable.getOffset()); query.setMaxResults(pageable.getPageSize()); } query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List resultList = query.getResultList(); Page<U> page = new PageImpl<>(resultList,pageable,totalCount.longValue()); return page; }
二,调用
//分页 Pageable pageable = PageRequest.of(pageIndex, pageSsize(), Sort.Direction.DESC, "create_time"); //不分页 Pageable pageable=Pageable.unpaged() ; public Page<entityDTO> findMachineToPlan(String a,Integer b,Pageable pageable) { String sql = "select * from table where a=:a and b=:b"; Query query = em.createNativeQuery(sql); query.setParameter("a", a); query.setParameter("b", b); Page<mac_machine_screen_res> page = findPage(query,pageable,entityDTO.class); return page; }