內(nèi)容: [J2ME]使用Spring的JdbcTemplate實現(xiàn)分頁功能作者：潘順昌版權(quán)聲明：本文可以自由轉(zhuǎn)載，轉(zhuǎn)載時請務(wù)必以超鏈接形式標(biāo)明文章原始出處和作者信息及本聲明作者:潘順昌(http://www.matrix.org.cn/user.shtml?username=panshunchang)原文:http://www.matrix.org.cn/resource/article/43/43976_Jdbc_Template.html關(guān)鍵字:sping JdbcTemplate最近使用了spring中的JdbcTemplate實現(xiàn)數(shù)據(jù)庫的查詢和插入操作，發(fā)現(xiàn)spring的JdbcTemplate 不象HibernateTemplate那么好，已經(jīng)實現(xiàn)了分頁功能。所以要自己實現(xiàn)，使用getJdbcTemplate().queryForList(string sql)得到的結(jié)果集是所有的。如果你的查詢有10000條記錄，或者更多，速度肯定慢了，當(dāng)然你可以通過resultset中的游標(biāo)控制查詢的起始和結(jié)束。我這里用的是Oracle數(shù)據(jù)庫，使用偽列ROWNUM來實現(xiàn)分頁。我的分頁代碼如下：package com.deity.ranking.util;import java.util.List;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.support.JdbcDaoSupport;/** * 分頁函數(shù) * * @author allenpan */public class Pagination extends JdbcDaoSupport{ public static final int NUMBERS_PER_PAGE = 10; //一頁顯示的記錄數(shù) private int numPerPage; //記錄總數(shù) private int totalRows; //總頁數(shù) private int totalPages; //當(dāng)前頁碼 private int currentPage; //起始行數(shù) private int startIndex; //結(jié)束行數(shù) private int lastIndex; //結(jié)果集存放List private List resultList; //JdbcTemplate jTemplate private JdbcTemplate jTemplate; /** * 每頁顯示10條記錄的構(gòu)造函數(shù),使用該函數(shù)必須先給Pagination設(shè)置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); } /**分頁構(gòu)造函數(shù) * @param sql 根據(jù)傳入的sql語句得到一些基本分頁信息 * @param currentPage 當(dāng)前頁 * @param numPerPage 每頁記錄數(shù) * @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. '); } //設(shè)置每頁顯示記錄數(shù) setNumPerPage(numPerPage); //設(shè)置要顯示的頁數(shù) setCurrentPage(currentPage); //計算總記錄數(shù) StringBuffer totalSQL = new StringBuffer(' SELECT count(*) FROM ( '); totalSQL.append(sql); totalSQL.append(' ) totalTable '); //給JdbcTemplate賦值 setJdbcTemplate(jTemplate); //總記錄數(shù) setTotalRows(getJdbcTemplate().queryForInt(totalSQL.toString())); //計算總頁數(shù) setTotalPages(); //計算起始行數(shù) setStartIndex(); //計算結(jié)束行數(shù) setLastIndex(); System.out.println('lastIndex='+lastIndex);////////////////// //構(gòu)造oracle數(shù)據(jù)庫的分頁語句 StringBuffer paginationSQL = new StringBuffer(' SELECT * FROM ( '); paginationSQL.append(' SELECT temp.* ,ROWNUM num FROM ( '); paginationSQL.append(sql); paginationSQL.append(' ) temp where ROWNUM ' + startIndex); //裝入結(jié)果集 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; } //計算總頁數(shù) 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; } //計算結(jié)束時候的索引 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 ; } }}在我的業(yè)務(wù)邏輯代碼中： /** * find season ranking list from DC * @param areaId 選手區(qū)域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