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

 
本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/YUHEN78/archive/2010/09/27/5910317.aspx