使用Spring的JdbcTemplate实现分页功能

转载 2006年06月23日 13:02:00
最近使用了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());
        }


相关文章推荐

springMVC详细配置+Mysql+jdbcTemplate实现分页功能

本例子使用到的相关技术有:springMVC+Mysql+jquery+jdbcTemplate+jstl+json+bootstrap等 (P:网上很少找到完整的springMVC+mys...

springMVC详细配置+Mysql+jdbcTemplate实现分页功能

本例子使用到的相关技术有:springMVC+mysql+jQuery+jdbcTemplate+jstl+json+bootstrap等 (P:网上很少找到完整的springMVC...

基于Spring JdbcTemplate 的分页处理

1.JdbcPaginationHelper.java import java.sql.ResultSet; import java.sql.SQLException; import java.ut...

spring JdbcTemplate实现通用的泛型dao一:主功能实现

http://www.dexcoder.com/selfly/article/429 在项目中一直使用Mybaits,最近想自己搞个小项目,通过对比之后发现mybatis的优势并不是十分明显...

Spring架构使用JdbcTemplate进行数据库功能开发

配置如下Bean组件 dataSource 为userDAO和transactionManager提供数据源 transactionManager 为userDAO的事务管理策略对象Use...
  • Angel_G
  • Angel_G
  • 2016年10月13日 16:23
  • 179

SSM(Spring,springmvc,mybatis)+maven+AJAX实现分页功能

SSM(Spring,springmvc,mybatis)+maven+AJAX实现分页功能简介经过这段时间的工作,有一种强烈的写博客的想法,或是为了分享,或是为了记录自己的点滴,也是为了加深印象,也...

Spring+Hibernate实现分页功能

Spring+Hibernate实现分页功能 本实例采用Spring+Hibernate实现简单的分页功能 最关键的是运用Hibernate的query里面的两个方法: query....
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:使用Spring的JdbcTemplate实现分页功能
举报原因:
原因补充:

(最多只允许输入30个字)