Oracle自带连接池使用(转载收录)

最近在搞数据迁移：从sql server 迁数据到oracle。

其中sql server在本地，oracle在远程数据库服务器。

用jdbc写了段代码连接两个库，转数据。

连oracle时用了一个jdbc连接，速度很慢。

想了下用数据库连接池。

为何不直接使用 Oracle 提供的连接池实现(转载收录)
[url]http://unmi.cc/use-oracle-carried-connection-pool/[/url]
Oracle自带连接池应用
[url]http://shihuan830619.iteye.com/blog/1162300[/url]

上代码，看main

package com.zas.test.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;
import javax.sql.PooledConnection;

import oracle.jdbc.pool.OracleConnectionCacheImpl;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;

/**
 * Oracle自带连接池应用
 * */
public class OraclePooled {

	private static PooledConnection dbpool;

	public OraclePooled(String ConnectionURL, String UserName, String PassWord) {
		try {
			OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
			ocpds.setURL(ConnectionURL);
			ocpds.setUser(UserName);
			ocpds.setPassword(PassWord);
			dbpool = ocpds.getPooledConnection();
		} catch (Exception ex) {
			System.err.println("Error in PooledSQL-construct: ");
			ex.printStackTrace(System.err);
		}
	}

	protected void finalize() { 
		if(dbpool != null) { 
			try { 
				dbpool.close(); 
	        } catch(Exception ex){    
	        	ex.printStackTrace();       
	        } 
	    } 
	}

	public ResultSet Query(String sql, String[] params){ 
		PreparedStatement pstmt = null; 
	    ResultSet rs = null; 
	    Connection connection = null; 

	    try { 
	        connection = dbpool.getConnection(); 
	        pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        for(int i=1; i<params.length+1; i++){
	        	pstmt.setString(i, params[i-1]);
	        }
			rs = pstmt.executeQuery();
			while(rs.next()){
		    	System.out.println(rs.getString(1));
		    	System.out.println(rs.getString(2));
		    }
	    } catch(Exception ex) { 
	        System.err.println("Error in Query - SQLBean: "); 
	        ex.printStackTrace(System.err); 
	    } 

	    return rs; 
	}
	/**
	 * 创建一个 DataSource
	 * 直接使用 OracleConnectionCacheImpl
	 * @throws SQLException
	 */
	public static DataSource createDataSource() throws SQLException
	{
	    OracleConnectionCacheImpl occi = new OracleConnectionCacheImpl();
	    occi.setURL("jdbc:oracle:thin:@10.128.38.2:1521:sr3");
	    occi.setUser("qatest");
	    occi.setPassword("qatest");
	    occi.setMinLimit(2);  //最小连接数
	    occi.setMaxLimit(10); //最大连接数
	    return occi;
	}

	public static void main(String[] args) throws SQLException{
		DataSource dataSource = OraclePooled.createDataSource();
		Connection connection = dataSource.getConnection();
		String sql = "select 1 from dual";
		PreparedStatement pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
	    	System.out.println(rs.getString(1));
	    }
	}
}