jdbctemplate分页mysql_用Spring的JdbcTemplate实现分页功能 | 学步园

package com.deity.ranking.util;import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.support.JdbcDaoSupport;/** * 分页函数 *　* @author allenpan*/publicclassPagination extends JdbcDaoSupport{publicstaticfinalintNUMBERS_PER_PAGE=10;//一页显示的记录数privateintnumPerPage;//记录总数privateinttotalRows;//总页数privateinttotalPages;//当前页码privateintcurrentPage;//起始行数privateintstartIndex;//结束行数privateintlastIndex;//结果集存放ListprivateList resultList;//JdbcTemplate jTemplateprivateJdbcTemplate jTemplate;/**

* 每页显示10条记录的构造函数,使用该函数必须先给Pagination设置currentPage，jTemplate初值

* @param sql oracle语句*/publicPagination(String sql){if(jTemplate==null){thrownewIllegalArgumentException("com.deity.ranking.util.Pagination.jTemplate is null,please initial it first.");

}elseif(sql.equals("")){thrownewIllegalArgumentException("com.deity.ranking.util.Pagination.sql is empty,please initial it first.");

}newPagination(sql,currentPage,NUMBERS_PER_PAGE,jTemplate);

}/**分页构造函数

* @param sql 根据传入的sql语句得到一些基本分页信息

* @param currentPage 当前页

* @param numPerPage 每页记录数

* @param jTemplate JdbcTemplate实例*/publicPagination(String sql,intcurrentPage,intnumPerPage,JdbcTemplate jTemplate){if(jTemplate==null){thrownewIllegalArgumentException("com.deity.ranking.util.Pagination.jTemplate is null,please initial it first.");

}elseif(sql==null||sql.equals("")){thrownewIllegalArgumentException("com.deity.ranking.util.Pagination.sql is empty,please initial it first.");

}//设置每页显示记录数setNumPerPage(numPerPage);//设置要显示的页数setCurrentPage(currentPage);//计算总记录数StringBuffer totalSQL=newStringBuffer("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=newStringBuffer("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*/publicstaticvoidmain(String[] args) {//TODO Auto-generated method stub　　　　}publicintgetCurrentPage() {returncurrentPage;

}publicvoidsetCurrentPage(intcurrentPage) {this.currentPage=currentPage;

}publicintgetNumPerPage() {returnnumPerPage;

}publicvoidsetNumPerPage(intnumPerPage) {this.numPerPage=numPerPage;

}publicList getResultList() {returnresultList;　　　　}publicvoidsetResultList(List resultList) {this.resultList=resultList;

}publicintgetTotalPages() {returntotalPages;

}//计算总页数publicvoidsetTotalPages() {if(totalRows%numPerPage==0){this.totalPages=totalRows/numPerPage;

}else{this.totalPages=(totalRows/numPerPage)+1;

}

}publicintgetTotalRows() {returntotalRows;

}publicvoidsetTotalRows(inttotalRows) {this.totalRows=totalRows;

}publicintgetStartIndex() {returnstartIndex;

}publicvoidsetStartIndex() {this.startIndex=(currentPage-1)*numPerPage;

}publicintgetLastIndex() {returnlastIndex;

}publicJdbcTemplate getJTemplate() {returnjTemplate;

}publicvoidsetJTemplate(JdbcTemplate template) {

jTemplate=template;

}//计算结束时候的索引publicvoidsetLastIndex() {

System.out.println("totalRows="+totalRows);///System.out.println("numPerPage="+numPerPage);///if( totalRows

}elseif((totalRows%numPerPage==0)||(totalRows%numPerPage!=0&&currentPage

}elseif(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*/publicList findSeasonRankingList(Long areaId,intrankYear,intrankMonth,

Long categoryId,String characterName) {//SQL语句StringBuffer sql=newStringBuffer("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=newPagination(sql.toString(),1,10,getJdbcTemplate());returnpageInfo.getResultList();//return getJdbcTemplate().queryForList(sql.toString());}