Spring 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;
}
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
