databasemanager.java

java 编写的sqlserver快速分页通用类

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;


public class DataBaseManager {

	/** Connection */
	private Connection conn = null;
	/** ResultSet */
	private ResultSet rs = null;
	/** PreparedStatement */
	private PreparedStatement ps = null;
	/** sql */
	private String sql = "";
	/** bindData */
	private StringBuffer bindData = null;
	/** The line feed code at the time of carrying out the log output of the SQL sentence */
	protected final String CR_LF = "\n";

	private boolean canClose = true;

	/**
	 * Constructor
	 *
	 */
	public DataBaseManager(){
	}

	/**
	 * Constructor
	 */
	public DataBaseManager(Connection conn){
		this.conn = conn;
		canClose = false;
	}

	/**
	 * An SQL sentence is registered to prepareStatement.
	 * @param sql Execution SQL
	 */
	public void prepare(String sql) throws SQLException {
		if(ps != null){
			ps.close();
		}
		this.sql = sql;
		ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		bindData = new StringBuffer("");
	}

	/**
	 * An SQL sentence is registered to prepareStatement.
	 * <pre>
	 * prepareStatement is generated in updating mode.
	 * </pre>
	 * 
	 * @param sql Execution SQL
	 */
	public void prepareUpdate(String sql) throws SQLException {
		if(ps != null){
			ps.close();
		}
		this.sql = sql;
		ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
		bindData = new StringBuffer("");
	}

	/**
	 * The registered SQL sentence is published.
	 * 
	 * @param Bind variable 
	 * @return ResultSet
	 * @throws SQLException, DataNotFoundException
	 */
	public ResultSet executeQuery(List list) throws SQLException, DataNotFoundException{
		if(ps == null){
			return null;
		}
		bindList(list);
//		if(rs != null){
//			rs.close(); 
//		}

		rs = executeQuery();
		

		return rs;
	}

	/**
	 * The registered SQL sentence is published.
	 * 
	 * @return ResultSet
	 * @throws SQLException, DataNotFoundException
	 */
	public ResultSet executeQuery() throws SQLException, DataNotFoundException{
		if(ps == null){
			return null;
		}
//		if(rs != null){
//			rs.close(); 
//		}
		
		long starttime = System.currentTimeMillis();
		try{
//			System.out.println(sql);
			
			rs = ps.executeQuery();

			long endtime = System.currentTimeMillis();
			long interval = endtime - starttime;
			
			if (rs != null && !rs.isBeforeFirst()) {
				throw new DataNotFoundException("DATA_NOT_FOUND");
			}
			
		}catch(SQLException sqle){
			bindData = new StringBuffer("");

			throw sqle;
		}

		return rs;
	}

	/**
	 * The registered SQL sentence is published.
	 * 
	 * @return ResultSet
	 * @throws SQLException
	 */
	public ResultSet executeQueryNoNFE() throws SQLException{
		if(ps == null){
			return null;
		}
//		if(rs != null){
//			rs.close();
//		}
		
		long starttime = System.currentTimeMillis();
		try{
			
			rs = ps.executeQuery();

			long endtime = System.currentTimeMillis();
			long interval = endtime - starttime;
					}catch(SQLException sqle){
			bindData = new StringBuffer("");

			throw sqle;
		}

		return rs;
	}

	/**
	 * An updating SQL sentence is published.
	 * 
	 * @return Result count
	 * @throws	SQLException
	 */
	public int executeUpdate() throws SQLException{
		if(ps == null){
			return -1;
		}
		if(rs != null){
			rs.close();
		}
		int ret = 0;
		long starttime = System.currentTimeMillis();
		try{
			
			ret = ps.executeUpdate();

			long endtime = System.currentTimeMillis();
			long interval = endtime - starttime;
			
		}catch(SQLException sqle){
			if (bindData == null){
				bindData = new StringBuffer("");
			}
						throw sqle;
		}
		return ret;
	}

	/**
	 * An updating SQL sentence is published.
	 * 
	 * @param Bind variable 
	 * @return Result count
	 * @throws	SQLException
	 */
	public int executeUpdate(List list) throws SQLException{
		if(ps == null){
			return 0;
		}
		bindList(list);
		return executeUpdate();
	}


	/**
	 * A value is set to the parameter of SQL.
	 * 
	 * @param Bind variable 
	 * @throws	SQLException
	 */
	public void bindList(List list) throws SQLException{
		int idx=1;
		String bind = "";
		this.bindData = new StringBuffer("");
		for(Iterator i=list.iterator();i.hasNext();idx++){
			bind = (String)i.next();
			ps.setString(idx,bind);
			this.bindData.append(":" + bind + " ");
		}
	}

	/**
	 * It connects with a database.
	 *
	 * @param datasource Data source name.
	 * @throws	SQLException
	 * @throws	NamingException
	 */
	public void connect( String dataSourceName ) throws SQLException, NamingException{
		// A data source is acquired from JNDI.
		InitialContext ic = new InitialContext();
		DataSource ds = (DataSource) ic.lookup( dataSourceName );
		// It connection-acquires from a connection pool.
		this.conn = ds.getConnection();
		
//		//for test
//		try
//		{
//		//数据池连接用的代码	
//		/*	Context initCtx = new javax.naming.InitialContext(); 
//            Context envCtx = (Context) initCtx.lookup("java:comp/env"); 
//            DataSource ds = (DataSource)envCtx.lookup("jdbc/DBPool"); ;
//			return ds.getConnection();
//		
//			*/
// 		///:-~
//       //应用JDBC-ODBC桥进行连接
// 		String driver="sun.jdbc.odbc.JdbcOdbcDriver";
//     	String db="jdbc:odbc:progsys";	//progsys为你在ODBC中设置的名称
//     	String username="sa";
//        String 	password="sa";    
// 	    try {
//			Class.forName(driver);
//		} catch (ClassNotFoundException e1) {
//			// TODO Auto-generated catch block
//			e1.printStackTrace();
//		}      
// 		this.conn=DriverManager.getConnection(db,username,password); 		
//		}
//		catch(SQLException e)
//		{
//			throw e;
//		}		
		
	}

	/**
	 * It commit with a database.
	 *
	 * @throws	SQLException
	 */
	public void commit() throws SQLException{
		if( conn!=null ) {
			this.conn.commit();
		}
	}

	/**
	 * It rollback with a database.
	 *
	 * @throws	SQLException
	 */
	public void rollback(){
		if( conn!=null ) {
			try {
				this.conn.rollback();
			} catch (Exception e) {}
		}
	}

	/**
	 * It cuts from a database.
	 *
	 */
	public void disconnect() {
		if ( rs!=null ) {
			try {
				rs.close();
				rs = null;
			} catch (Exception e) {}
		}
		if( ps != null){
			try {
				ps.close();
				ps = null;
			} catch (Exception e) {}
		}
		if( conn!=null ) {
			try {
				conn.close();
				conn = null;
			} catch (Exception e) {}
		}
	}


	/**
	 * Release processing of an object.
	 * <pre>
	 * 	An object is the method automatically performed by the GABEJI collector 
	 * at the time of needlessness (it became impossible to refer to).
	 * </pre>
	 * @throws Exception When a database access error occurs
	 */
	public void finalize() throws Exception{
		if(canClose){
 			disconnect();
		}
	}

	public Connection getConnect(){
		return this.conn;
	}
}