Spring Boot JPA分页

常用方法

总记录数 ：page.getTotalElements()  
当前第几页：page.getNumber()  
总页数：page.getTotalPages()
当前页面的List：page.getContent()
当前页面的记录数：page.getNumberOfElements()

基本分页单元

public class Meta {
	private int  page; //当前页数
	private int pages; //总页数
	private int perpage;//每页个数
	private long total	;//总记录数
	private String sort	;//排序
	private String field;//排序属性名
}

场景一

1. Controller部分

只传递数据内容tableData.getContent()，否则前台分页模版不能解析。tableData还包括分页信息

2. DAO部分

sql为原生sql，pageable为分页对象，map为查询参数

查询参数

红线为模糊查询

3. 工具类

其中orderBy和total方法为工具类

4. 工具类源码

@Repository
public class EntityManagerUtil {

    @PersistenceContext
    private EntityManager entityManager;

    public int updateSql(String sql, Object... objects) {
        Query query = entityManager.createNativeQuery(sql);
        for (int i = 0; i < objects.length; i++) {
            query.setParameter(i + 1, objects[i]);
        }
        return query.executeUpdate();
    }

    public List resultList(String sql, Object... objects) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); // 设置返回值类型
        for (int i = 0; i < objects.length; i++) {
            query.setParameter(i + 1, objects[i]);
        }
        return query.getResultList();
    }
    public List<Map<String, Object>> resultList(String sql, Map<String, Object> params) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); // 设置返回值类型
        for (String key : params.keySet()) {
            query.setParameter(key, params.get(key));
        }
        return query.getResultList();
    }
    public <T> List<T> resultList(String sql, Map<String, Object> params, Class<T> resultClass) {
        Query query = entityManager.createNativeQuery(sql, resultClass);
        for (String key : params.keySet()) {
            query.setParameter(key, params.get(key));
        }
        return query.getResultList();
    }

    public Map<String, Object> resultMap(String sql, Object... objects) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); // 设置返回值类型
        for (int i = 0; i < objects.length; i++) {
            query.setParameter(i + 1, objects[i]);
        }
        return (Map<String, Object>) query.getSingleResult();
    }

    public Page resultPage(String sql, Pageable page, Object... objects) {
        sql = orderBy(sql, page.getSort());
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); // 设置返回值类型
        for (int i = 0; i < objects.length; i++) {
            query.setParameter(i + 1, objects[i]);
        }
        query.setFirstResult(page.getOffset());
        query.setMaxResults(page.getPageSize());
        List list = query.getResultList();
        Long total = total(sql, objects).longValue();
        return new PageImpl(list, page, total);
    }
    public Page<Map<String, Object>> resultPage(String sql, Pageable page, Map<String, Object> params) {
        sql = orderBy(sql, page.getSort());
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); // 设置返回值类型
        for (String key : params.keySet()) {
            query.setParameter(key, params.get(key));
        }
        query.setFirstResult(page.getOffset());
        query.setMaxResults(page.getPageSize());
        List<Map<String, Object>> list = query.getResultList();
        Long total = total(sql, params).longValue();
        return new PageImpl(list, page, total);
    }

    private String orderBy(String sql, Sort sort) {
        if (sort == null) return sql;
        Iterator<Sort.Order> orders = sort.iterator();
        if (!orders.hasNext()) return sql;
        StringBuilder orderBy = new StringBuilder(" order by ");
        boolean flag = false;
        while (orders.hasNext()) {
            if (flag) {
                orderBy.append(",");
            } else {
                flag = true;
            }
            Sort.Order order = orders.next();
            orderBy.append(order.getProperty());
            if (order.getDirection().isDescending()) {
                orderBy.append(" ").append(Sort.Direction.DESC.toString());
            }
        }
        if (flag) {
            sql = orderBy.insert(0, sql).toString();
        }
        return sql;
    }

    private BigDecimal total(String sql, Object[] objects) {
        sql = "select count(1) from (" + sql + ")";
        Query query = entityManager.createNativeQuery(sql);
        for (int i = 0; i < objects.length; i++) {
            query.setParameter(i + 1, objects[i]);
        }
        return (BigDecimal) query.getSingleResult();
    }
    private BigDecimal total(String sql, Map<String, Object> params) {
        sql = "select count(1) from (" + sql + ")";
        Query query = entityManager.createNativeQuery(sql);
        for (String key : params.keySet()) {
            query.setParameter(key, params.get(key));
        }
        return (BigDecimal) query.getSingleResult();
    }
}

个人博客 > 欢迎来访