Spring JdbcTemplate 查询分页

2022-09-16 14:42:23

1.大家都有的page类

[java] view plain copy

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 plain copy

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 plain copy

@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;

}

Spring JdbcTemplate 查询分页,布布扣,bubuko.com

Spring JdbcTemplate 查询分页