使用Spring的JdbcTemplate实现分页功能

内容: [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