dboperate.java

数据库连接池源码

package llm.pool.relation;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

public class DBOperate implements DBOperateI {
	
	private static Logger log = Logger.getLogger(DBOperate.class.getName());
	private int queryTotalRow;
	private DBConnectPara dbcPara;
	private DBConnect dbConnect;
	private String message;

	public DBOperate() {
		this.queryTotalRow = 0;
		this.message       = "";
	}

	public void initialize(String jndiName) throws LlmDBException {
		if( null == jndiName )
			throw new LlmDBException( "数据源连接参数不能为空！" );
		jndiName  = jndiName.trim();		
		dbConnect = DBConnectManager.getDBConnect( jndiName );
		dbcPara    = dbConnect.getDbConnectPara();
	}

	public void init( String jndiName ) throws LlmDBException {
		initialize( jndiName );
	}
	
	public DataSource getDataSource() {
		return dbConnect.getDataSource();
	}
	
	public DBConnectPara getDBConnectPara(){
		return dbcPara;
	}

	public Connection getDBConnection() throws SQLException, LlmDBException {
		return dbConnect.getConnection();
	}

	public void freeConnection() {
		dbConnect.freeConnection();
	}

	public ArrayList exeQuery( String query ) throws SQLException, LlmDBException {
		ArrayList<String[]> resultArrayList = new ArrayList<String[]>();
	    int totalColumn = 0, i = 0;
	    Connection connection = null;
	    PreparedStatement pstmt = null;
	    ResultSet resultSet = null;
	    try {
	    	connection = getDBConnection();
	    	pstmt = connection.prepareStatement( changeCharset( query, dbcPara.getAppCharset(),
	    		  dbcPara.getDbReadCharset() ) );
	    	resultSet = pstmt.executeQuery();
	    	ResultSetMetaData rsmd = resultSet.getMetaData();
	    	totalColumn = rsmd.getColumnCount();
	    	String tempArray[];
	    	
	    	while ( resultSet.next() ) {
	    		tempArray = new String[totalColumn];
	    		for ( i = 0; i < totalColumn; i ++ ) {
	    			tempArray[i] = changeCharset( resultSet.getString(i+1), dbcPara.getDbReadCharset(), dbcPara.getAppCharset()  );
	    		}
	    		resultArrayList.add( tempArray );
	    	}
	    	
	    	queryTotalRow = resultArrayList.size();
	    }
	    finally {
	    	closeResultSet( resultSet );
	    	closePreparedStatement( pstmt );
	    	freeConnection();
//	    	closeConnection( connection );
	    }
	    return resultArrayList;
	}
	
	public int exeQueryInt( String query ) throws SQLException, LlmDBException {
	    int resultInt = 0;
	    Connection connection = null;
	    PreparedStatement pstmt = null;
	    ResultSet resultSet = null;
	    try {
	    	connection = getDBConnection();
	    	pstmt = connection.prepareStatement( changeCharset( query, dbcPara.getAppCharset(),
	    		  dbcPara.getDbReadCharset() ) );
	    	resultSet = pstmt.executeQuery();	    	
	    	while ( resultSet.next() ) {
	    		resultInt = resultSet.getInt( 1 );
	    	}	    	
	    	queryTotalRow = 1;
	    }
	    finally {
	    	closeResultSet( resultSet );
	    	closePreparedStatement( pstmt );
	    	freeConnection();
//	    	closeConnection( connection );
	    }
	    return resultInt;	
	}

	public ArrayList exeQuery( String query, int beginRow, int endRow ) throws SQLException, LlmDBException {
		int totalColumn = 0, i = 0;
	    ArrayList<String[]> resultArrayList = new ArrayList<String[]>();
	    Connection connection = null;
	    Statement stmt = null;
	    ResultSet resultSet = null;
	    ResultSetMetaData rsmd = null;

	    try {
	    	connection = getDBConnection();
	    	stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
	                                 ResultSet.CONCUR_READ_ONLY);
	    	resultSet = stmt.executeQuery( changeCharset(query, dbcPara.getAppCharset(),
	    		  dbcPara.getDbReadCharset()));
	    	rsmd = resultSet.getMetaData();
	    	totalColumn = rsmd.getColumnCount();
	    	
	    	if (beginRow != 0)
	    		resultSet.absolute(beginRow);
	    	
	    	String tempArray[];
	    	
	    	while ( resultSet.next() && beginRow <= endRow ) {
	    		tempArray = new String[totalColumn];
	    		for ( i = 0; i < totalColumn; i++ ) {
	    			tempArray[i] = changeCharset( resultSet.getString( i + 1 ), dbcPara.getDbReadCharset(), dbcPara.getAppCharset() );
	    		}
	    		resultArrayList.add(tempArray);
	    		beginRow++;
	    	}
	    	if ( resultSet.last() )
	    		queryTotalRow = resultSet.getRow();
	    }
	    finally {
	    	
	    	closeResultSet( resultSet );
	    	closeStatement( stmt );
	    	freeConnection();
//	    	closeConnection( connection );
	    	
	    }
	    return resultArrayList;
	}

	public boolean exeUpdate(String query) {
	    Statement stmt = null;
	    Connection connection = null;
	    boolean prevCommit = true;
	    try {
	    	connection = getDBConnection();
	    	prevCommit = connection.getAutoCommit();
	    	TransManager.beginTrans( connection, dbcPara.getDbType() );
	    	stmt = connection.createStatement();
	    	stmt.executeUpdate( changeCharset( query, dbcPara.getAppCharset(), dbcPara.getDbWriteCharset() ) );
	    	TransManager.commitTrans(connection, dbcPara.getDbType() );
	    }
	    catch ( Exception e ) {
	    	message = "更新数据库失败。";
	    	log.error( message + e.getMessage(), e );	
	    	if (connection != null)
	    		try {
	    			TransManager.rollbackTrans( connection, dbcPara.getDbType() );
	    		} catch (Exception e2) {
	    			message = "数据库记录更新回滚时出错。";	    	
	    	    	log.error( message + e2.getMessage(), e );	
	    		}
	    	return false;
	    }
	    finally {
	    	closeStatement( stmt );
	    	if (connection != null)
	    		try {
	    			connection.setAutoCommit( prevCommit );
	    			freeConnection();
//	    			closeConnection( connection );
	    		}
	    		catch (Exception e) {
	    			message = "数据库连接关闭时出错。";	    	
	    	    	log.error( message + e.getMessage(), e );	
	    		}
	    }
	    return true;
	}

	public boolean exeUpdate( ArrayList arrayListSql ) {
		boolean status = true;
	    Connection connection = null;
	    Statement stmt = null;
	    boolean prevCommit = true;
	    try {
	    	connection = getDBConnection();
	    	prevCommit = connection.getAutoCommit();
	    	TransManager.beginTrans( connection, dbcPara.getDbType() );
	    	stmt = connection.createStatement();
	    	Iterator iterator = arrayListSql.iterator();
	    	while( iterator.hasNext() )
	    		stmt.executeUpdate( changeCharset( (String)iterator.next(),
	    				dbcPara.getAppCharset(),
	    				dbcPara.getDbWriteCharset() ) );
	    	TransManager.commitTrans(connection, dbcPara.getDbType() );
	    }
	    catch (Exception e) {
	    	status = false;	    	
	    	message = "数据库记录更新时应用程序发生意外。";	    	
	    	log.error( message + e.getMessage(), e );	 	
	    	
	    	if (connection != null)
	    		try {
	    			TransManager.rollbackTrans( connection, dbcPara.getDbType() );
	    		} catch (Exception e2) {
	    			message = "数据库记录更新回滚时出错。";	    	
	    	    	log.error( message + e2.getMessage(), e );	
	    		}
	    }
	    finally {
	    	closeStatement( stmt );	    	
	    	if (connection != null)
	    		try {
	    			connection.setAutoCommit( prevCommit );
	    			freeConnection();
//	    			closeConnection( connection );
	    		}
	    		catch (Exception e) {
	    			status = false;
	    			message = "数据库连接关闭时出错。";	    	
	    	    	log.error( message + e.getMessage(), e );	
	    		}
	    }
	    return status;
	}

	public boolean exeUpdate(String[] SqlArray) {
		ArrayList<String> arrayListSql = new ArrayList<String>();
	    for (int i = 0; i < SqlArray.length; i++) {
	    	arrayListSql.add( SqlArray[i] );
	    }
	    return exeUpdate( arrayListSql );
	}

	public String exeProc(String strSql) throws SQLException, LlmDBException {
		String ret = "";
		Connection connection = null;
	    try {
	    	connection = getDBConnection();
	    	CallableStatement cstmt = connection.prepareCall( strSql );
	    	cstmt.registerOutParameter( 1, 4 );
	    	cstmt.registerOutParameter( 2, 12 );
	    	cstmt.execute();
	    	ret = cstmt.getString(2);
	    	cstmt.close();
	    }
	    finally {
	    	freeConnection();
	    }
	    return ret;
	}

	public int getQueryTotalRow() {
		return queryTotalRow;
	}

	public ArrayList getResultArrayList() {
		return null;
	}

	public String getMessage() {
		return message;
	}
	
	/**
	 * 字符转换函数
	 * @param string
	 * @param charsetFrom
	 * @param charsetTo
	 * @return
	 */
	private final String changeCharset( String string, String charsetFrom, String charsetTo ) {
		
		if ( string == null ) return "";
		if ( dbcPara.isChangeCharset() )
			try {
				return new String( string.getBytes( charsetFrom ), charsetTo );
			}
			catch (Exception e) {
				log.error("字符转换出错", e);				
			}
		return string;
	}
	
	/**
	 * 关闭结果集
	 * @param resultSet ResultSet
	 */
	private void closeResultSet(ResultSet resultSet) {
		if (resultSet != null)
			try {
				resultSet.close();
			} catch (Exception e) {
				log.error("关闭结果集出错", e);
			}
	}
	
	/**
	 * 关闭声明 Statement
	 * @param resultSet ResultSet
	 */
	private void closeStatement(Statement stmt) {
		if ( stmt != null )
			try {
				stmt.close();
			} catch ( Exception e ) {
				log.error("关闭声明出错", e);
			}
	}
	
	/**
	 * 关闭声明 PreparedStatement
	 * @param resultSet ResultSet
	 */
	private void closePreparedStatement(PreparedStatement pstmt) {
		if (pstmt != null)
			try {
				pstmt.close();
			} catch (Exception e) {
				log.error("关闭声明出错", e);
			}
	}

	/**
	 * 关闭数据库连接 Connection
	 * @param connection Connection
	 */
//	private void closeConnection( Connection connection ) {
//		if ( connection != null )
//			try {
//				connection.close();
//			} catch (Exception e) {
//				log.error("关闭声明出错", e);
//			}
//	}

	/**
	 * @param args
	 * @throws LlmDBException 
	 * @throws LlmDBException 
	 * @throws SQLException 
	 * @throws SQLException 
	 * @throws InterruptedException 
	 */
	public static void main(String[] args) throws LlmDBException, SQLException {
		DBOperate dbo = new DBOperate();
		dbo.init( "SYSTEM" );
	    String query;
//	    long sss = System.currentTimeMillis();
	    System.out.println( "start" );
	    ArrayList<String> sqlList = new ArrayList<String>();	    
	    query = "INSERT INTO aaa( a,b,c) VALUES('A','B','C')";
	    sqlList.add(query);
	    if( dbo.exeUpdate( query ) )
	    	System.out.println("INSERT ok");
	    else
	    	System.out.println("INSERT failed");
	    query = "SELECT a,b,c FROM aaa";
	    ArrayList al1 = dbo.exeQuery( query );
	    System.out.println("总行数：" + dbo.getQueryTotalRow());
	    String[] temp = null;
	    int ll = 0;
	    for (int i = 0; i < al1.size(); i++ ) {
	    	temp = (String[])al1.get(i);
	    	
	    	ll = temp.length;
	    	for (int j = 0; j < ll; j ++ ) {
	    		System.out.print( temp[j]  + ", " );	
	    	}
	    	System.out.println("");
	    }
//	    System.out.println( System.currentTimeMillis() - sss );

	}

}