最近使用了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());
}
