现在进行的项目由于数据库的遗留原因(设计的不堪入目)不能用hibernate.所以用的Spring JdbcTemplate,今天作派谴员工的分页,发现一个不错的JdbcTemplate分页写法,较现在搜索到的写法都值得说一说!看源码吧!很简单
1.大家都有的page类
[java] view plaincopy
- 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.分页的助手类
[java] view plaincopy
- 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;
接口的实现:
[java] view plaincopy
- @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/