Spring JdbcTemplate 查询分页



Spring JdbcTemplate 查询分页

现在进行的项目由于数据库的遗留原因(设计的不堪入目)不能用hibernate.所以用的Spring JdbcTemplate,今天作派谴员工的分页,发现一个不错的JdbcTemplate分页写法,较现在搜索到的写法都值得说一说!看源码吧!很简单

 

1.大家都有的page类

 

  1. public class CurrentPage<E> {
  2.     private int pageNumber;
  3.     private int pagesAvailable;
  4.     private List<E> pageItems = new ArrayList<E>();
  5.     public void setPageNumber(int pageNumber) {
  6.         this.pageNumber = pageNumber;
  7.     }
  8.     public void setPagesAvailable(int pagesAvailable) {
  9.         this.pagesAvailable = pagesAvailable;
  10.     }
  11.     public void setPageItems(List<E> pageItems) {
  12.         this.pageItems = pageItems;
  13.     }
  14.     public int getPageNumber() {
  15.         return pageNumber;
  16.     }
  17.     public int getPagesAvailable() {
  18.         return pagesAvailable;
  19.     }
  20.     public List<E> getPageItems() {
  21.         return pageItems;
  22.     }
  23. }

 

 

2.分页的助手类

 

  1. import java.sql.ResultSet;
  2. import java.sql.SQLException;
  3. import java.util.List;
  4. import org.springframework.dao.DataAccessException;
  5. import org.springframework.jdbc.core.JdbcTemplate;
  6. import org.springframework.jdbc.core.ResultSetExtractor;
  7. import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
  8. public class PaginationHelper<E> {
  9.     public CurrentPage<E> fetchPage(final JdbcTemplate jt,
  10.             final String sqlCountRows, final String sqlFetchRows,
  11.             final Object args[], final int pageNo, final int pageSize,
  12.             final ParameterizedRowMapper<E> rowMapper) {
  13.         // determine how many rows are available
  14.         final int rowCount = jt.queryForInt(sqlCountRows, args);
  15.         // calculate the number of pages
  16.         int pageCount = rowCount / pageSize;
  17.         if (rowCount > pageSize * pageCount) {
  18.             pageCount++;
  19.         }
  20.         // create the page object
  21.         final CurrentPage<E> page = new CurrentPage<E>();
  22.         page.setPageNumber(pageNo);
  23.         page.setPagesAvailable(pageCount);
  24.         // fetch a single page of results
  25.         final int startRow = (pageNo - 1) * pageSize;
  26.         jt.query(sqlFetchRows, args, new ResultSetExtractor() {
  27.             public Object extractData(ResultSet rs) throws SQLException,
  28.                     DataAccessException {
  29.                 final List pageItems = page.getPageItems();
  30.                 int currentRow = 0;
  31.                 while (rs.next() && currentRow < startRow + pageSize) {
  32.                     if (currentRow >= startRow) {
  33.                         pageItems.add(rowMapper.mapRow(rs, currentRow));
  34.                     }
  35.                     currentRow++;
  36.                 }
  37.                 return page;
  38.             }
  39.         });
  40.         return page;
  41.     }
  42. }

 

 

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

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


接口的实现:

 

  1. @Override
  2. public List<Client> getAllCompanyTest(int pageSize) throws DataAccessException {
  3.     PaginationHelper<Client> ph = new PaginationHelper<Client>();
  4.     List<Client> c=new ArrayList<Client>();
  5.        CurrentPage<Client> p=ph.fetchPage(
  6.                jdbcTemplate,
  7.                ”SELECT count(*) FROM angle_company WHERE state=?”,
  8.                ”SELECT acid,corpname,contact,legal,tel,postcode,mail,address,summary,employee_eeid FROM angle_company WHERE state=?”,
  9.                new Object[]{JdbcSqlCollection.NORMALRECORD},
  10.                pageSize,
  11.                JdbcSqlCollection.PAGERECORDS,
  12.                new TestClientRowMap()
  13.        );
  14.        c=p.getPageItems();
  15.     return c;
  16. }

 

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

class TestClientRowMap implements ParameterizedRowMapper<Client>{

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

return client;

}

 

引用原地址:

http://www.codefutures.com/tutorials/spring-pagination/