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&¤tPage
}elseif(totalRows%numPerPage!=0&¤tPage==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());}