Spring的JdbcTemplate實現分頁

 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";
}</pre>