七郎's JavaBlog

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