JdbcTemplate扩展分页

编程技术  /  houtizong 发布于 3年前   69

1.JdbcTemplateExtend .java
package rms.pub.main.web.test;

import java.util.List;
import java.util.Map;
import javax.sql.*;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class JdbcTemplateExtend extends JdbcTemplate {
 private DataSource dataSource;

 /**
  * 默认构造器，调用此方法初始化，需要调用setDataSource设置数据源
  */
 public JdbcTemplateExtend() {
 }

 /**
  * 初始构造器
  *
  * @param dataSource
  *            数据源
  */
 public JdbcTemplateExtend(DataSource dataSource) {
  this.dataSource = dataSource;
  super.setDataSource(dataSource);
 }

 /**
  * 普通分页查询<br>
  * <b>如果结果结合比较大应该调用setFetchsize() 和setMaxRow两个方法来控制一下，否则会内存溢出</b>
  *
  * @see #setFetchSize(int)
  * @see #setMaxRows(int)
  * @param sql
  *            查询的sql语句
  * @param startRow
  *            起始行
  * @param rowsCount
  *            获取的行数
  * @return
  * @throws DataAccessException
  */
 @SuppressWarnings("unchecked")
 public List<Map> queryForListPage(String sql, int startRow, int rowsCount)
   throws DataAccessException {
  return queryForListPage(sql, startRow, rowsCount,
    getColumnMapRowMapper());
 }

 /**
  * 自定义行包装器查询<br>
  * <b>如果结果结合比较大应该调用setFetchsize() 和setMaxRow两个方法来控制一下，否则会内存溢出</b>
  *
  * @see #setFetchSize(int)
  * @see #setMaxRows(int)
  * @param sql
  *            查询的sql语句
  * @param startRow
  *            起始行
  * @param rowsCount
  *            获取的行数
  * @param rowMapper
  *            行包装器
  * @return
  * @throws DataAccessException
  */
 @SuppressWarnings("unchecked")
 public List<Map> queryForListPage(String sql, int startRow, int rowsCount,
   RowMapper rowMapper) throws DataAccessException {
  return (List) query(sql, new SplitPageResultSetExtractor(rowMapper,
    startRow, rowsCount));
 }

 public DataSource getDataSource() {
  return dataSource;
 }

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
  super.setDataSource(dataSource);
 }
}


2. SplitPageResultSetExtractor .java

package rms.pub.main.web.test;

import java.sql.*;
import java.util.*;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.Assert;

public class SplitPageResultSetExtractor implements ResultSetExtractor {
 private final int start;// 起始行号
 private final int len;// 结果集合的长度
 private final RowMapper rowMapper;// 行包装器

 public SplitPageResultSetExtractor(RowMapper rowMapper, int start, int len) {
  Assert.notNull(rowMapper, "RowMapper is required");
  this.rowMapper = rowMapper;
  this.start = start;
  this.len = len;
 }

 /**
  * 处理结果集合,被接口自动调用，该类外边不应该调用
  */
 public Object extractData(ResultSet rs) throws SQLException,
   DataAccessException {
  List result = new ArrayList();
  int rowNum = 0;
  int end = start + len;
  point: while (rs.next()) {
   ++rowNum;
   if (rowNum < start) {
    continue point;
   } else if (rowNum >= end) {
    break point;
   } else {
    result.add(this.rowMapper.mapRow(rs, rowNum));
   }
  }
  return result;
 }
}