Spring JdbcTemplate 查询分页

原文来自http://tech.e800.com.cn/articles/2009/619/1245373991529_1.html

1大家都有的page类

public class CurrentPage {
 private int pageNumber;
 private int pagesAvailable;
 private List pageItems = new ArrayList();
 public void setPageNumber(int pageNumber) { 
  this.pageNumber = pageNumber;
 }
 public void setPagesAvailable(int pagesAvailable) { 
  this.pagesAvailable = pagesAvailable;
 }
 public void setPageItems(List pageItems) { 
  this.pageItems = pageItems;
 }
 public int getPageNumber() { 
  return pageNumber;
 }
 public int getPagesAvailable() { 
  return pagesAvailable;
 }
 public List getPageItems() { 
  return pageItems;
 }
}

2.分页的助手类

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
public class PaginationHelper<E> {
 public CurrentPage<E> fetchPage(
   final JdbcTemplate jt,
  final String sqlCountRows, final String sqlFetchRows,
  final Object args[], final int pageNo, final int pageSize,
  final ParameterizedRowMapper<E> rowMapper) {
   // determine how many rows are available
   final int rowCount = jt.queryForInt(sqlCountRows, args);
   // calculate the number of pages
   int pageCount = rowCount / pageSize;
   if (rowCount > pageSize * pageCount) {
   pageCount++;
 }
 // create the page object
 final CurrentPage<E> page = new CurrentPage<E>();
 page.setPageNumber(pageNo);
 page.setPagesAvailable(pageCount);
 // fetch a single page of results
 final int startRow = (pageNo - 1) * pageSize;
 jt.query(sqlFetchRows, args, new ResultSetExtractor() {
  public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
   final List pageItems = page.getPageItems();
   int currentRow = 0;
   while (rs.next() && currentRow < startRow + pageSize) {
   if (currentRow >= startRow) {
   pageItems.add(rowMapper.mapRow(rs, currentRow));
   }
   currentRow++;
   }
   return page;
   }
   });
  return page;
 }
}

 

//完了！下面看一看Dao的一个接口:

List<Client> getAllCompanyTest(int pageSize)throws DataAccessException;

//接口的实现:

@Override
public List<Client> getAllCompanyTest(int pageSize) throws DataAccessException {
 PaginationHelper<Client> ph = new PaginationHelper<Client>();
 List<Client> c=new ArrayList<Client>();
 CurrentPage<Client> p=ph.fetchPage(
 jdbcTemplate,
 "SELECT count(*) FROM angle_company WHERE state=?",
 "SELECT acid,corpname,contact,legal,tel,postcode,mail,address,summary,employee_eeid FROM angle_company WHERE state=?",
 new Object[]{JdbcSqlCollection.NORMALRECORD},
 pageSize,
 JdbcSqlCollection.PAGERECORDS,
 new TestClientRowMap()
 );
 c=p.getPageItems();
 return c;
}

最好还有一个ParameterizedRowMapper的实现类,就不贴原码了,下面是简单的伪代码

class TestClientRowMap implements ParameterizedRowMapper<Client>{

@Override
public Client mapRow(ResultSet rs, int arg1) throws SQLException {
Client client=new Client();

...

return client;

}