Spring JdbcTemplate 实现自定义分页

下面是我用到Spring jdbctemplate 方式实现自定义分页的详细步骤：

步骤一创建SplitPageResultSetExtractor 类：   

package com.utils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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 startIndex;// 起始行号

 private final int pageSize;// 每页记录数

 private final RowMapper rowMapper;// 行包装器

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

 /**
  * 处理结果集合,被接口自动调用，该类外边不应该调用
  */
 public Object extractData(ResultSet rs) throws SQLException,
   DataAccessException {
  List result = new ArrayList();
  rs.first();
  rs.relative(startIndex-1);
  int count=0;
   while (rs.next()) {  
             count++;  
             result.add(this.rowMapper.mapRow(rs, startIndex+count));  
             System.out.println(rs.getBoolean(1));
             if (count == pageSize) {  
                 break;  
             }  
         }  
  return result;
 }
 
 /**
  * 没有使用该方法 如果数据量较大 就无法查询出数据
  */
 public Object extractDatas(ResultSet rs) throws SQLException,
   DataAccessException {
  List result = new ArrayList();
  int rowNum = 0;
  int end = startIndex + pageSize; 
  point: while (rs.next()) {
   ++rowNum;
   if (rowNum < startIndex) {
    continue point;
   } else if (rowNum >= end) {
    break point;
   } else {
    result.add(this.rowMapper.mapRow(rs, rowNum));
   }
  }
  return result;
 }
}

 

 

步骤二创建JdbcTemplateExtend 类：

package com.utils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;
import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;

public class JdbcTemplateExtend extends JdbcTemplate {
 @Override
 public List queryForList(String arg0, Object[] arg1)
   throws DataAccessException {
  return super.queryForList(arg0, arg1);
 }

 private DataSource dataSource;

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

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

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

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

 /**
  * 普通分页查询<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> queryForListPagination(String sql, Object[] arg1,
   int startRow, int rowsCount) throws DataAccessException {
  return queryPagination(sql, arg1, startRow, rowsCount,
    getColumnMapRowMapper());
 }
 
 /**
  * 普通分页查询<br>
  * <b>如果结果结合比较大应该调用setFetchsize() 和setMaxRow两个方法来控制一下，否则会内存溢出</b>
  *
  * @see #setFetchSize(int)
  * @see #setMaxRows(int)
  * @param types[]
  *           传递参数的类型值 java.sql.Types.VARCHAR
  * @param sql
  *            查询的sql语句
  * @param startRow
  *            起始行
  * @param rowsCount
  *            获取的行数
  * @return
  * @throws DataAccessException
  */
 @SuppressWarnings("unchecked")
 public List<Map> queryForListPagination(String sql, Object[] arg1,int [] types,
   int startRow, int rowsCount) throws DataAccessException {
  return queryPagination(sql, arg1,types, startRow, rowsCount,
    getColumnMapRowMapper());
 }

 private List<Map> queryPagination(String sql, final Object[] arg1,int [] types,
   int startRow, int rowsCount, RowMapper columnMapRowMapper) {
  PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(
    sql, types);
  factory.setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
  PreparedStatementCreator psc = factory
    .newPreparedStatementCreator(arg1);
  return (List) query(psc, new SplitPageResultSetExtractor(
    columnMapRowMapper, startRow, rowsCount));
 }

 private List<Map> queryPagination(String sql, final Object[] arg1,
   int startRow, int rowsCount, RowMapper columnMapRowMapper) {
  int[] types = new int[arg1.length];
  for (int i = 0; i < arg1.length; i++) {
   types[i] = Types.VARCHAR;
  }
  PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(
    sql, types);
  factory.setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
  // factory.setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
  PreparedStatementCreator psc = factory
    .newPreparedStatementCreator(arg1);
  return (List) query(psc, new SplitPageResultSetExtractor(
    columnMapRowMapper, startRow, rowsCount));
 }

 public DataSource getDataSource() {
  return dataSource;
 }

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

 

 

步骤三Dao层中调用：

public List getInto(String plateauClientname ,int noteCount,int start,int pagesize)
  {
   JdbcTemplateExtend jdbcextend = new JdbcTemplateExtend();
   jdbcextend.setDataSource(jdbcTemplate.getDataSource());//设置数据连接源
   String sql = "select * from UserPhoneDC where plateauClientname = '  "+plateauClientname+"' and notecount          = "+noteCount;
   List list = jdbcextend.querySP(sql, start, pagesize);
       //参数解释：
        sql: sql操作语句。
        start: 起始记录行
        pagesize：每页显示记录数
      return list;
  }