EntityManager 使用 Pageable 分页

参考：Springboot+JPA+Hibernate动态查询及分页，使用Pageable以及entityManager_扶我起来，我要学java的博客-CSDN博客_hibernate pageable

一，通用方法

	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;
    }