对于分页主要思想无非两种一是从数据库取出所有记录后进行分页另一种思路是在取出数据的同时进行分页然后在页面显示昨晚查了不少资料对目前流行的分页方式总结了下
本人目前有个项目要用到Spring的JdbcTemplate顺带搜了点关于其分页的实现方法
用Spring的JdbcTemplate实现分页功能
最近使用了spring中的JdbcTemplate实现数据库的查询和插入操作发现spring的JdbcTemplate 不象HibernateTemplate那么好已经实现了分页功能所以要自己实现使用getJdbcTemplate()queryForList(string sql)得到的结果集是所有的
如果你的查询有条记录或者更多速度肯定慢了当然你可以通过resultset中的游标控制查询的起始和结束我这里用的是Oracle数据库使用伪列ROWNUM来实现分页我的分页代码如下
package comdeityrankingutil;import javautilList;
import orgsprireJdbcTemplate;
import orgspriresupportJdbcDaoSupport;
/** * 分页函数 ** @author allenpan */public class Pagination extends JdbcDaoSupport{
public static final int NUMBERS_PER_PAGE = ;
//一页显示的记录数
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;
/**
* 每页显示条记录的构造函数使用该函数必须先给Pagination设置currentPagejTemplate初值
* @param sql oracle语句
*/
public Pagination(String sql){
if(jTemplate == null){
throw new IllegalArgumentException(comdeityrankingutilPaginationjTemplate is nullplease initial it first );
}else if(sqlequals()){
throw new IllegalArgumentException(comdeityrankingutilPaginationsql is emptyplease initial it first );
}
new Pagination(sqlcurrentPageNUMBERS_PER_PAGEjTemplate);
}
/**分页构造函数
* @param sql 根据传入的sql语句得到一些基本分页信息
* @param currentPage 当前页
* @param numPerPage 每页记录数
* @param jTemplate JdbcTemplate实例
*/
public Pagination(String sqlint currentPageint numPerPageJdbcTemplate jTemplate){
if(jTemplate == null){
throw new IllegalArgumentException(comdeityrankingutilPaginationjTemplate is nullplease initial it first );
}else if(sql == null || sqlequals()){
throw new IllegalArgumentException(comdeityrankingutilPaginationsql is emptyplease initial it first );
}
//设置每页显示记录数
setNumPerPage(numPerPage);
//设置要显示的页数
setCurrentPage(currentPage);
//计算总记录数
StringBuffer totalSQL = new StringBuffer( SELECT count(*) FROM ( );
totalSQLappend(sql);
totalSQLappend( ) totalTable );
//给JdbcTemplate赋值
setJdbcTemplate(jTemplate);
//总记录数
setTotalRows(getJdbcTemplate()queryForInt(totalSQLtoString()));
//计算总页数
setTotalPages();
//计算起始行数
setStartIndex();
//计算结束行数
setLastIndex();
Systemoutprintln(lastIndex=+lastIndex);//////////////////
//构造oracle数据库的分页语句
StringBuffer paginationSQL = new StringBuffer( SELECT * FROM ( );
paginationSQLappend( SELECT temp* ROWNUM num FROM ( );
paginationSQLappend(sql);
paginationSQLappend() temp where ROWNUM <= + lastIndex);
paginationSQLappend( ) WHEREnum > + startIndex);
//装入结果集
setResultList(getJdbcTemplate()queryForList(paginationSQLtoString()));
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Autogenerated method stub}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
thiscurrentPage = currentPage;
}
public int getNumPerPage() {
return numPerPage;
}
public void setNumPerPage(int numPerPage) {
thisnumPerPage = numPerPage;
}
public List getResultList() {
return resultList;}
public void setResultList(List resultList) {
thisresultList = resultList;
}
public int getTotalPages() {
return totalPages;
}
//计算总页数
public void setTotalPages() {
if(totalRows % numPerPage == ){
thistotalPages = totalRows / numPerPage;
}else{
thistotalPages= (totalRows / numPerPage) + ;
}
}
public int getTotalRows() {
return totalRows;
}
public void setTotalRows(int totalRows) {
thistotalRows = totalRows;
}
public int getStartIndex() {
return startIndex;
}
public void setStartIndex() {
thisstartIndex = (currentPage ) * numPerPage;
}
public int getLastIndex() {
return lastIndex;
}
public JdbcTemplate getJTemplate() {
return jTemplate;
}
public void setJTemplate(JdbcTemplate template) {
jTemplate = template;
}
//计算结束时候的索引
public void setLastIndex() {
Systemoutprintln(totalRows=+totalRows);///////////
Systemoutprintln(numPerPage=+numPerPage);///////////
if( totalRows < numPerPage){
thislastIndex = totalRows;
}else if((totalRows % numPerPage == ) || (totalRows % numPerPage != && currentPage < totalPages)){
thislastIndex = currentPage * numPerPage;
}else if(totalRows % numPerPage != && currentPage == totalPages){//最后一页
thislastIndex = totalRows ;
}
}}
在我的业务逻辑代码中
/**
* find season ranking list from DC
* @param areaId 选手区域id
* @param rankDate 赛季
* @param category 类别
* @param characterName 角色名
* @return List
*/
public List findSeasonRankingList(Long areaId int rankYearint rankMonth
Long categoryIdString characterName) {
//SQL语句
StringBuffer sql = new StringBuffer( SELECT CUSERID useridDPOSNAME posnameCGAMEID gameidCAMOUNT amountCRANK rank FROM );
//表sqlappend( (SELECT BUSERID USERID);
sqlappend( BPOSID POSID);
sqlappend( ADISTRICT_CODE DISTRICTCODE);
sqlappend( AGAMEID GAMEID);
sqlappend( AMOUNT AMOUNT);
sqlappend( RANK RANK );
sqlappend( FROM TB_FS_RANK A );
sqlappend( LEFT JOIN TB_CHARACTER_INFO B );
sqlappend( ON ADISTRICT_CODE = BDISTRICT_CODE );
sqlappend( AND AGAMEID = BGAMEID );
//附加条件
if(areaId != null && areaIdintValue() != ){
sqlappend( and ADISTRICT_CODE = + areaIdintValue());
}
if( rankYear > && rankMonth > ){
//hqlappend( and sasiddt >= to_date( + rankYear + + rankMonth + :: + YYYYMMDD HH:MI:SS);
//hqlappend( and sasiddt <= to_date( + rankYear + + rankMonth + + TimeToolfindMaxDateInMonth(rankYearrankMonth) + :: + YYYYMMDD HH:MI:SS);
sqlappend( and ADT = fn_time_convert(to_date( + rankYear + + rankMonth + + YYYYMM)) );
}
if(categoryId != null && categoryIdintValue() != ){
sqlappend( and ACID = + categoryIdintValue());
}
if(characterName != null && !characterNametrim()equals()){
sqlappend( and AGAMEID = + characterNametrim()+ );
}
sqlappend( ORDER BY RANK ASC) C );
sqlappend( LEFT JOIN TB_FS_POSITION D );
sqlappend( ON CPOSID = DPOSID );
sqlappend( ORDER BY CRANK );
Systemoutprintln(hql=+sqltoString());////////////////
//使用自己的分页程序控制结果集
Pagination pageInfo = new Pagination(sqltoString()getJdbcTemplate());
return pageInfogetResultList();
//return getJdbcTemplate()queryForList(sqltoString());
}
非常简单但有使用的分页条件判断语句
if ($page <= ) {
$page_str = [首页][上一页];
} else {
$page_str = [<a href=$PHP_SELF?module=$module&page=>首页</a>][<a href=$PHP_SELF?module=$module&page=($page)>上一页</a>];
}
if ($page >= $page_total) {
$page_str = [下一页][尾页];
} else {
$page_str = [<a href=$PHP_SELF?module=$module&page=($page+)>下一页</a>][<a href=$PHP_SELF?module=$module&page=$page_total>尾页</a>];
}
