內容: [J2ME]使用Spring的JdbcTemplate實現分頁功能作者：潘順昌版權聲明：本文可以自由轉載，轉載時請務必以超鏈接形式標明文章原始出處和作者信息及本聲明作者:潘順昌(http://www.matrix.org.cn/user.shtml?username=panshunchang)原文:http://www.matrix.org.cn/resource/article/43/43976_Jdbc_Template.html關鍵字:sping JdbcTemplate最近使用了spring中的JdbcTemplate實現數據庫的查詢和插入操作，發現spring的JdbcTemplate 不象HibernateTemplate那么好，已經實現了分頁功能。所以要自己實現，使用getJdbcTemplate().queryForList(string sql)得到的結果集是所有的。如果你的查詢有10000條記錄，或者更多，速度肯定慢了，當然你可以通過resultset中的游標控制查詢的起始和結束。我這里用的是Oracle數據庫，使用偽列ROWNUM來實現分頁。我的分頁代碼如下：package com.deity.ranking.util;import java.util.List;import org.springframework.jdbc.core.JdbcTemplate;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; /** * 每頁顯示10條記錄的構造函數,使用該函數必須先給Pagination設置currentPage，jTemplate初值 * @param sql oracle語句 */ 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); } /**分頁構造函數 * @param sql 根據傳入的sql語句得到一些基本分頁信息 * @param currentPage 當前頁 * @param numPerPage 每頁記錄數 * @param jTemplate JdbcTemplate實例 */ public Pagination(String sql,int currentPage,int numPerPage,JdbcTemplate jTemplate){ 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); //計算總記錄數 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 ' + startIndex); //裝入結果集 setResultList(getJdbcTemplate().queryForList(paginationSQL.toString())); } /** * @param args */ public static void main(String[] args) { // TODO Auto-generated method stub } 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 List getResultList() { return resultList; } public void setResultList(List resultList) { this.resultList = resultList; } 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 ; } }}在我的業務邏輯代碼中： /** * find season ranking list from DC * @param areaId 選手區域id * @param rankDate 賽季 * @param category 類別 * @param characterName 角色名 * @return List */ public List findSeasonRankingList(Long areaId, int rankYear,int rankMonth, Long categoryId,String characterName) { //SQL語句 StringBuffer sql = new StringBuffer(' SELECT C.USERID userid,D.POSNAME posname,C.GAMEID gameid,C.AMOUNT amount,C.RANK rank FROM '); //表 sql.append(' (SELECT B.USERID USERID,'); sql.append(' B.POSID POSID,'); sql.append(' A.DISTRICT_CODE DISTRICTCODE,'); sql.append(' A.GAMEID GAMEID,'); sql.append(' AMOUNT AMOUNT,'); sql.append(' RANK RANK '); sql.append(' FROM TB_FS_RANK A '); sql.append(' LEFT JOIN TB_CHARACTER_INFO B '); sql.append(' ON A.DISTRICT_CODE = B.DISTRICT_CODE '); sql.append(' AND A.GAMEID = B.GAMEID '); //附加條件 if(areaId != null && areaId.intValue() != 0){sql.append(' and A.DISTRICT_CODE = ' + areaId.intValue()); } if( rankYear> 1970 && rankMonth> 0){//hql.append(' and sas.id.dt>= to_date('' + rankYear + '-' + rankMonth + '-01 00:00:00',' + 'YYYY-MM-DD HH24:MI:SS');//hql.append(' and sas.id.dt