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