Spring的JdbcTemplate实现分页
jopen
10年前
1、分页类代码： import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; /** * * 分页函数 * * * * @author allenpan */ public class Pagination extends JdbcDaoSupport{ public static final int NUMBERS_PER_PAGE = 10; // 一页显示的记录数 private int numPerPage; // 记录总数 private int totalRows; // 总页数 private int totalPages; // 当前页码 private int currentPage; // 起始行数 private int startIndex; // 结束行数 private int lastIndex; // 结果集存放List private List resultList; // JdbcTemplate jTemplate private JdbcTemplate jTemplate; //private Pagination pagination = new Pagination(); /** * 每页显示10条记录的构造函数,使用该函数必须先给Pagination设置currentPage，jTemplate初值 * * @param sql * oracle语句 */ public Pagination() { } /*public Pagination(int currentPage){ this.currentPage = currentPage; }*/ public Pagination(String sql) { if (jTemplate == null) { throw new IllegalArgumentException( "com.deity.ranking.util.Pagination.jTemplate is null,please initial it first. "); } else if (sql.equals("")) { throw new IllegalArgumentException( "com.deity.ranking.util.Pagination.sql is empty,please initial it first. "); } new Pagination(sql, currentPage, NUMBERS_PER_PAGE, jTemplate, null); } /** * 分页构造函数 * * @param sql * 根据传入的sql语句得到一些基本分页信息 * @param currentPage * 当前页 * @param numPerPage * 每页记录数 * @param jTemplate * JdbcTemplate实例 */ public Pagination(String sql, int currentPage, int numPerPage, JdbcTemplate jTemplate,RowMapper rowMapper) { this.currentPage = currentPage; if (jTemplate == null){ throw new IllegalArgumentException( "com.deity.ranking.util.Pagination.jTemplate is null,please initial it first. "); } else if (sql == null || sql.equals("")) { throw new IllegalArgumentException( "com.deity.ranking.util.Pagination.sql is empty,please initial it first. "); } // 设置每页显示记录数 setNumPerPage(numPerPage); // 设置要显示的页数 setCurrentPage(currentPage); System.out.println("Pagination currentPage="+currentPage); // 计算总记录数 StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( "); totalSQL.append(sql); totalSQL.append(" ) totalTable "); // 给JdbcTemplate赋值 setJdbcTemplate(jTemplate); // 总记录数 setTotalRows(getJdbcTemplate().queryForInt(totalSQL.toString())); // 计算总页数 setTotalPages(); // 计算起始行数 setStartIndex(); // 计算结束行数 setLastIndex(); System.out.println("lastIndex=" + lastIndex);// //////////////// // 构造oracle数据库的分页语句 StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( "); paginationSQL.append(" SELECT temp.* ,ROWNUM num FROM ( "); paginationSQL.append(sql); paginationSQL.append(" ) temp where ROWNUM <= " + lastIndex); paginationSQL.append(" ) WHERE num > " + startIndex); System.out.println("sql:"+paginationSQL.toString()); // 装入结果集 setResultList(getJdbcTemplate().query(paginationSQL.toString(),rowMapper)); } /** * @param args */ public int getCurrentPage() { return currentPage; } public void setCurrentPage(int currentPage) { this.currentPage = currentPage; } public int getNumPerPage() { return numPerPage; } public void setNumPerPage(int numPerPage) { this.numPerPage = numPerPage; } public int getTotalPages() { return totalPages; } // 计算总页数 public void setTotalPages() { if(totalRows % numPerPage == 0){ this.totalPages = totalRows / numPerPage; }else{ this.totalPages= (totalRows / numPerPage) + 1; } } public int getTotalRows() { return totalRows; } public void setTotalRows(int totalRows) { this.totalRows = totalRows; } public int getStartIndex() { return startIndex; } public void setStartIndex() { this.startIndex = (currentPage - 1) * numPerPage; } public int getLastIndex() { return lastIndex; } public JdbcTemplate getJTemplate() { return jTemplate; } public void setJTemplate(JdbcTemplate template) { jTemplate = template; } // 计算结束时候的索引 public void setLastIndex() { System.out.println("totalRows=" + totalRows);// ///////// System.out.println("numPerPage=" + numPerPage);// ///////// if (totalRows < numPerPage) { this.lastIndex = totalRows; } else if ((totalRows % numPerPage == 0) || (totalRows % numPerPage != 0 && currentPage < totalPages)) { this.lastIndex = currentPage * numPerPage; } else if (totalRows % numPerPage != 0 && currentPage == totalPages) {//最后一页 this.lastIndex = totalRows; } } public List getResultList() { return resultList; } public void setResultList(List resultList) { this.resultList = resultList; } } 2、页面： <%@ page language="java" import="java.util.*,java.net.*" pageEncoding="UTF-8"%> <%@ taglib prefix="s" uri="/struts-tags"%> <script src="js/fenye.js" type="text/javascript"></script> <%! String code; String contractCode; %> <% code = (String)request.getAttribute("code"); contractCode = URLEncoder.encode(code);//为解决乱码问题而设计的，所传参数没有中文不需要 request.setAttribute("contractCode",contractCode); %> <table class="listViewPaginationTdS1"><tr> <div> <s:if test="pagination.totalPages != 0"> <s:url action="%{#request.url}" id="first"> <s:param name="pagination.currentPage" value="1"></s:param> <s:param name="contract.contractCode" value="#request.contractCode"/> </s:url> <s:url action="%{#request.url}" id="next" > <s:param name="pagination.currentPage" value="pagination.currentPage+1"> </s:param> <s:param name="contract.contractCode" value="#request.contractCode"/> </s:url> <s:url action="%{#request.url}" id="prior" > <s:param name="pagination.currentPage" value="pagination.currentPage-1"></s:param> <s:param name="contract.contractCode" value="#request.contractCode"/> </s:url> <s:url action="%{#request.url}" id="last"> <s:param name="pagination.currentPage" value="pagination.totalPages"></s:param> <s:param name="contract.contractCode" value="#request.contractCode"/> </s:url> <s:if test="pagination.currentPage == 1"> <td> <span class="current">首页</span> <span class="current">上一 页</span></td> </s:if> <s:else> <td> <s:a href="%{first}">首页</s:a> <s:a href="%{prior}">上一页</s:a> </td> </s:else> <s:if test="pagination.currentPage == pagination.totalPages || pagination.totalPages == 0"> <td> <span class="current">下一页</span> <span class="current">末u-26507 </span> </td> </s:if> <s:else> <td> <s:a href="%{next}">下一页</s:a> <s:a href="%{last}">末页</s:a> </td> </s:else> <td> <s:form action="%{#request.url}?contract.contractCode=%{#request.contractCode}" theme="simple" cssStyle="display:inline"> <span class="jumplabel">跳转到</span> <s:hidden name="pagination.totalPages" value="%{pagination.totalPages}"></s:hidden> <input type="text" name="pagination.currentPage" size="2" onblur="selectPage(this)" /> <span class="jumplabel">页</span> </s:form> </td> <td> <span class="jumplabel">共<s:property value="pagination.totalRows" />条</span> <span class="jumplabel">当前是第 <s:property value="pagination.currentPage" />/<s:property value="pagination.totalPages"/>页</span> </td> </s:if> </div> </tr></table> fenye.js的内容如下 function trim(str){ return str.replace(/(^\s*)|(\s*$)/g, ""); } function selectPage(input){ var value = trim(input.value); if(value == ""){ return; } if(/\d+/.test(value)){ input.form.submit(); return; } alert("请输入正确的页数"); input.focus(); } 注意：pagination.totalRows、pagination.totalPages、pagination.currentPage等的.后面的属性写法一定要和类里面的属性一致，否则在页面无法显示 3、在dao里面调用 public List<ContractEntryDetail> queryDetail(String code,int currentPage) { String sql = "......"; List<ContractEntryDetail> list = null; pagination = new Pagination(sql,currentPage,5,jdbcTemplate,new ContractEntryDetailRowMapper()); list = pagination.getResultList();//pagination 是dao的一个属性，dao内包含其get、set方法 return list; } 4、Action里调用 public String execute(){ list = queryContractFenluDetailService.queryDetaiList(newSc3,pagination.getCurrentPage()); pagination = queryContractFenluDetailService.getPagination(); ActionContext ac = ActionContext.getContext(); ac.put("code", newSc3); ac.put("url","fenlu.action"); ac.put("pagination",pagination); ac.put("list", list); return "success"; }