【IT168技术文档】最近使用了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 <= " + lastIndex); paginationSQL.append( " ) WHERE num > " + 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 <= to_date('" + rankYear + "-" + rankMonth + "-" + TimeTool.findMaxDateInMonth(rankYear,rankMonth) + " 23:59:59'," + "YYYY-MM-DD HH24:MI:SS"); sql.append( " and A.DT = fn_time_convert(to_date(' " + rankYear + " - " + rankMonth + " ', " + " 'YYYY-MM')) " ); } if (categoryId != null && categoryId.intValue() != 0 ){ sql.append( " and A.CID = " + categoryId.intValue()); } if (characterName != null && ! characterName.trim().equals( "" )){ sql.append( " and A.GAMEID = ' " + characterName.trim() + " ' " ); } sql.append( " ORDER BY RANK ASC) C " ); sql.append( " LEFT JOIN TB_FS_POSITION D " ); sql.append( " ON C.POSID = D.POSID " ); sql.append( " ORDER BY C.RANK " ); System. out .println( " hql= " + sql.toString()); /// / // 使用自己的分页程序控制结果集 Pagination pageInfo = new Pagination(sql.toString(), 1 , 10 ,getJdbcTemplate()); return pageInfo.getResultList(); // return getJdbcTemplate().queryForList(sql.toString()); }
扫一扫
219
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
