Spring的JdbcTemplate实现分页

jopen 9年前

 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>&nbsp;&nbsp;                    <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";    }