jdbcTemplate分页彻底解决，使用游标滚动

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> * * @see #setFetchSize(int) * @see #setMaxRows(int) * @param sql * 查询的sql语句 * @param startRow * 起始行 * @param rowsCount * 获取的行数 * @return * @throws DataAccessException */ @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> * * @see #setFetchSize(int) * @see #setMaxRows(int) * @param sql * 查询的sql语句 * @param startRow * 起始行 * @param rowsCount * 获取的行数 * @param rowMapper * 行包装器 * @return * @throws DataAccessException */ @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); } }