基于Spring JdbcTemplate 的分页处理

1.JdbcPaginationHelper.java

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;import org.tshark.core.orm.Page;public class JdbcPaginationHelper<T> {private static final int DEFAULT_PAGE_SIZE = 30;public Page<T> fetchPage(final JdbcTemplate jdbcTemplate, final String countSql, final String dataSql, final Object args[],final int pageNo, final ParameterizedRowMapper<T> rowMapper) {return fetchPage(jdbcTemplate, countSql, dataSql, args, pageNo, DEFAULT_PAGE_SIZE, rowMapper);}public Page<T> fetchPage(final JdbcTemplate jdbcTemplate, final String countSql, final String dataSql, final Object args[],final int pageNo, final int pageSize, final ParameterizedRowMapper<T> rowMapper) {// 总记录数据final int rowCount = jdbcTemplate.queryForInt(countSql, args);// 计算总页数int pageCount = rowCount / pageSize;if (rowCount > pageSize * pageCount) {pageCount++;}final Page<T> page = new Page<T>();page.setPageNo(pageNo);page.setTotalCount(pageCount);page.setPageSize(pageSize);// 根据pageNo取一页数据final int startRow = (pageNo - 1) * pageSize;jdbcTemplate.query(dataSql, args, new ResultSetExtractor<Page<T>>() {public Page<T> extractData(ResultSet rs) throws SQLException, DataAccessException {final List<T> pageItems = page.getResult();int currentRow = 0;while (rs.next() && currentRow < startRow + pageSize) {if (currentRow >= startRow) {pageItems.add(rowMapper.mapRow(rs, currentRow));}currentRow++;}return page;}});return page;}}

2.Page.java

import java.util.List;import org.apache.commons.lang.StringUtils;import com.google.common.collect.Lists;/** * @param <T> Page中记录的类型. */public class Page<T> {//-- 公共变量 --//public static final String ASC = "asc";public static final String DESC = "desc";//-- 分页参数 --//protected int pageNo = 1;protected int pageSize = 1;protected String orderBy = null;protected String order = null;protected boolean autoCount = true;protected List<T> result = Lists.newArrayList();protected long totalCount = -1;public Page() {}public Page(int pageSize) {this.pageSize = pageSize;}/** * 获得当前页的页号,序号从1开始,默认为1. */public int getPageNo() {return pageNo;}/** * 设置当前页的页号,序号从1开始,低于1时自动调整为1. */public void setPageNo(final int pageNo) {this.pageNo = pageNo;if (pageNo < 1) {this.pageNo = 1;}}public Page<T> pageNo(final int thePageNo) {setPageNo(thePageNo);return this;}/** * 获得每页的记录数量,默认为1. */public int getPageSize() {return pageSize;}/** * 设置每页的记录数量,低于1时自动调整为1. */public void setPageSize(final int pageSize) {this.pageSize = pageSize;if (pageSize < 1) {this.pageSize = 1;}}public Page<T> pageSize(final int thePageSize) {setPageSize(thePageSize);return this;}/** * 根据pageNo和pageSize计算当前页第一条记录在总结果集中的位置,序号从1开始. */public int getFirst() {return ((pageNo - 1) * pageSize) + 1;}/** * 获得排序字段,无默认值.多个排序字段时用','分隔. */public String getOrderBy() {return orderBy;}/** * 设置排序字段,多个排序字段时用','分隔. */public void setOrderBy(final String orderBy) {this.orderBy = orderBy;}public Page<T> orderBy(final String theOrderBy) {setOrderBy(theOrderBy);return this;}/** * 获得排序方向. */public String getOrder() {return order;}/** * 设置排序方式向. * * @param order 可选值为desc或asc,多个排序字段时用','分隔. */public void setOrder(final String order) {//检查order字符串的合法值String[] orders = StringUtils.split(StringUtils.lowerCase(order), ',');if(orders != null){for (String orderStr : orders) {if (!StringUtils.equals(DESC, orderStr) && !StringUtils.equals(ASC, orderStr)) {throw new IllegalArgumentException("排序方向" + orderStr + "不是合法值");}}}this.order = StringUtils.lowerCase(order);}public Page<T> order(final String theOrder) {setOrder(theOrder);return this;}/** * 是否已设置排序字段,无默认值. */public boolean isOrderBySetted() {return (StringUtils.isNotBlank(orderBy) && StringUtils.isNotBlank(order));}/** * 查询对象时是否自动另外执行count查询获取总记录数, 默认为false. */public boolean isAutoCount() {return autoCount;}/** * 查询对象时是否自动另外执行count查询获取总记录数. */public void setAutoCount(final boolean autoCount) {this.autoCount = autoCount;}public Page<T> autoCount(final boolean theAutoCount) {setAutoCount(theAutoCount);return this;}//-- 访问查询结果函数 --///** * 取得页内的记录列表. */public List<T> getResult() {return result;}/** * 设置页内的记录列表. */public void setResult(final List<T> result) {this.result = result;}/** * 取得总记录数, 默认值为-1. */public long getTotalCount() {return totalCount;}/** * 设置总记录数. */public void setTotalCount(final long totalCount) {this.totalCount = totalCount;}/** * 根据pageSize与totalCount计算总页数, 默认值为-1. */public long getTotalPages() {if (totalCount < 0) {return -1;}long count = totalCount / pageSize;if (totalCount % pageSize > 0) {count++;}return count;}/** * 是否还有下一页. */public boolean isHasNext() {return (pageNo + 1 <= getTotalPages());}/** * 取得下页的页号, 序号从1开始. * 当前页为尾页时仍返回尾页序号. */public int getNextPage() {if (isHasNext()) {return pageNo + 1;} else {return pageNo;}}/** * 是否还有上一页. */public boolean isHasPre() {return (pageNo - 1 >= 1);}/** * 取得上页的页号, 序号从1开始. * 当前页为首页时返回首页序号. */public int getPrePage() {if (isHasPre()) {return pageNo - 1;} else {return pageNo;}}}

3.TransactionJdbcService.java

import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.springframework.jdbc.core.PreparedStatementSetter;import org.springframework.jdbc.core.simple.ParameterizedRowMapper;import org.springframework.stereotype.Service;import org.tshark.core.orm.Page;import org.tshark.core.service.AbstractJdbcService;import org.tshark.core.service.JdbcPaginationHelper;import org.tshark.framework.model.TransactionModel;@Servicepublic class TransactionJdbcService extends AbstractJdbcService {private static String QUERY_TRANS_COUNT_SQL = "select count(*) from sys_transaction  ";private static String QUERY_TRANS_DATA_SQL = "select * from sys_transaction ";private static String INSERT_TRANS_SQL = "insert into `sys_transaction` (`amount`,`month`,`region`) values(?,?,?) ";@Transientprotected JdbcTemplate jdbcTemplate;@Resourcepublic void setDataSource(DataSource dataSource) {jdbcTemplate = new JdbcTemplate(dataSource);}public void insertTransaction(final TransactionModel transaction) {jdbcTemplate.update(INSERT_TRANS_SQL, new PreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps) throws SQLException {ps.setDouble(1, transaction.getAmount());ps.setInt(2, transaction.getMonth());ps.setString(3, transaction.getRegion());}});}public Page<TransactionModel> findTransactionList(int pageNo, int month) {Object[] args = null;if (month != 0) {QUERY_TRANS_COUNT_SQL += "where month=?";QUERY_TRANS_DATA_SQL += "where month=?";args = new Object[] { month };}JdbcPaginationHelper<TransactionModel> JdbcPaginationHelper = new JdbcPaginationHelper<TransactionModel>();return JdbcPaginationHelper.fetchPage(jdbcTemplate, QUERY_TRANS_COUNT_SQL, QUERY_TRANS_DATA_SQL, args, pageNo,new TransactionRowMap());}class TransactionRowMap implements ParameterizedRowMapper<TransactionModel> {@Overridepublic TransactionModel mapRow(ResultSet rs, int rowNum) throws SQLException {TransactionModel transaction = new TransactionModel();transaction.setId(rs.getLong("id"));transaction.setAmount(rs.getDouble("amount"));transaction.setMonth(rs.getInt("month"));transaction.setRegion(rs.getString("region"));return transaction;}}}