SqlServer的一个分页工具类,主要功能有为据传入的参数生成sql语句,或者根据传入的参数直接返回一个ResultSet对象。
/**
*
*/
package com.woxiaoe.ework.util;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.gdb.common.DataSourceFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
/**
* 分页工具类
* @author 小e
*
* 2010-1-1 下午07:46:04
*/
public class PaginationUtil {
private Map<ResultSet,Connection> currentConn = new HashMap<ResultSet, Connection>();
public String getPaginationSql(int start,int limit,String tableName,String fields,String filter,String orderBy){
String sql = "SELECT TOP " + limit +" * FROM "
+ "( "
+ "SELECT ROW_NUMBER() OVER (ORDER BY " + orderBy + ") AS RowNumber," + fields +" FROM " + tableName +
((filter != null)?" where " + filter:"")
+ ") A "
+ " WHERE RowNumber > " + start ;
return sql;
}
public ResultSet query(int start,int limit,String tableName,String fields,String filter,String orderBy){
Connection conn = DataSourceUtils.getConnection(DataSourceFactory.getDataSource());
PreparedStatement pstmt;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(getPaginationSql(start,limit,tableName,fields,filter,orderBy));
rs = pstmt.executeQuery();
currentConn.put(rs, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
public void releaseConn(ResultSet rs){
Connection conn = currentConn.get(rs);
currentConn.remove(rs);
DataSourceUtils.releaseConnection(conn, DataSourceFactory.getDataSource());
}
}