Spring JdbcTemplate 查询分页

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

 

1.大家都有的page类

public class CurrentPage<E> { private int pageNumber; private int pagesAvailable; private List<E> pageItems = new ArrayList<E>(); public void setPageNumber(int pageNumber) { this.pageNumber = pageNumber; } public void setPagesAvailable(int pagesAvailable) { this.pagesAvailable = pagesAvailable; } public void setPageItems(List<E> pageItems) { this.pageItems = pageItems; } public int getPageNumber() { return pageNumber; } public int getPagesAvailable() { return pagesAvailable; } public List<E> 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;

}

 

引用原地址:

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