/** * 分页工具类 * @param sql 要执行的sql * @param pageNo 当前页码 * @param pageSize 页面数据条数 * @param template 要调用的JdbcTemplate对象 * @param params sql中的预处理参数 * @return */
public static Pages getJdbcPage(String sql, int pageNo, int pageSize, JdbcTemplate template, Object... params) { // 要返回的分页实体对象 Pages page = new Pages(); int pageNotemp = pageNo == 0 ? 1 : pageNo; long totalCount = 0; long startIndex = 0; long endIndex = 0; String countSql = StringHelper.getString("select count(*) from (", sql, ")"); try { totalCount = template.queryForObject(countSql, Long.class, params); } catch (DataAccessException e) { e.printStackTrace(); } // 计算总页数 long pageTotal = totalCount%pageSize==0 ? totalCount/pageSize : totalCount/pageSize + 1; // 总记录数 page.setTotalCount(totalCount); // 总页数 page.setPageTotal(pageTotal); page.setPageNo(pageNotemp); page.setPageSize(pageSize); if (totalCount <= 0) { page.setData(new ArrayList<>()); } else { // 每页起始位置 startIndex = (pageNotemp-1)*pageSize; // 每页结束为止 endIndex = pageNotemp*pageSize; //select * from (select t1.*,rownum rn from () t1 where rownum<= ) where rn > String pageSql = StringHelper.getString("select * from (select t1.*,rownum rn from (", sql, ") t1 where rownum <=", endIndex, ") where rn >", startIndex); List<Map<String, Object>> list = null; try { list = template.queryForList(pageSql, params); } catch (DataAccessException e) { e.printStackTrace(); list = new ArrayList<>(); } page.setData(list); } return page; }
/** * 分页实体类 */ public class Pages { // 当前页码 private int pageNo = 1; // 页面数据条数 private int PageSize = 10; // 总记录数 private long totalCount = 0; // 总页数 private long pageTotal = 0; // 封装返回数据 private Object data; public int getPageNo() { return pageNo; } public void setPageNo(int pageNo) { this.pageNo = pageNo; } public int getPageSize() { return PageSize; } public void setPageSize(int pageSize) { PageSize = pageSize; } public long getTotalCount() { return totalCount; } public void setTotalCount(long totalCount) { this.totalCount = totalCount; } public long getPageTotal() { return pageTotal; } public void setPageTotal(long pageTotal) { this.pageTotal = pageTotal; } public Object getData() { return data; } public void setData(Object data) { this.data = data; } }
注意:只需编写正常sql,传入对应参数,调用getJdbcPage方法即可。新手勿喷,欢迎大家提出意见建议,作为技术爱好者,希望能为大家提供些许帮助~~~