EntityManager 使用 Pageable 分页

参考：https://blog.csdn.net/qq_43948440/article/details/110877496

 

一，通用方法

public <U> Page<U> findPage(String sql,Map<String, Object> params, Pageable pageable, Class<U> destType) {
        String countSql = "SELECT COUNT(*)  FROM ( "+ sql +" ) getcount";

        Query query=em.createNativeQuery(sql);
        Query queryCount = em.createNativeQuery(countSql);
        //设置条件参数
        if(params!=null && params.size()>0) {
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                query.setParameter(entry.getKey(), entry.getValue());
                queryCount.setParameter(entry.getKey(), entry.getValue());
            }
        }
        //查询总条数
        BigInteger totalCount = (BigInteger) queryCount.getSingleResult();
        //分页参数
        if(pageable!=null) {
            query.setFirstResult(pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());
        }
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(destType));
        List resultList = query.getResultList();
        Page<U> page = new PageImpl<>(resultList,pageable,totalCount.longValue());
        return page;
    }

 

二，调用

    @Override
    public Page<SfOrder_Res> findPage() {
        String sql="select * from sf_order where state=:state";
        Map<String, Object> params=new HashMap<String, Object>();
        params.put("state","3");
        Pageable pageable = new PageRequestDto(1, 10, Sort.Direction.DESC, "createTime");
        Page<SfOrder_Res> page=findPage(sql,params,pageable, SfOrder_Res.class);
        return page;
    }

 

三，常规调用方式

@Override
    public Page<Model> findWithAlone(int pageIndex, int pageSize) {
        String sql = "select * from Tab1 where Tab1.参数1=:参数1";
        String sql1 = "select count(1) from Tab1 where Tab1.参数1=:参数1";
        Query query = em.createNativeQuery(sql);
        query.setFirstResult((pageIndex-1)*pageSize);
        query.setMaxResults(pageSize);
        Query query1 = em.createNativeQuery(sql1);
        query.setParameter("参数1", 参数1);
        query1.setParameter("参数1", 参数1);

        Object count = query1.getSingleResult();
        Integer total = count != null ? Integer.parseInt(count.toString()) : 0;
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(Model.class));
        List<Model> list = query.getResultList();
        Page<Model> page = new PageImpl<>(list, new PageRequest(pageIndex-1, pageSize), total);
        return page;
    }