import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.sql.PooledConnection;

import oracle.jdbc.pool.OracleConnectionPoolDataSource;

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

	private 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; 
	}
	
	public static void main(String[] args){
		OraclePooled op = new OraclePooled("jdbc:oracle:thin:@192.168.1.10:1521:oraid", "shihuan", "zznode");
		String sql = "SELECT T.MODULE_NAME, T.PARAM_NAME, T.SHOW_NAME, T.REL_SYMBOLS, T.GREEN_VALUE, T.YELLOW_VALUE, T.RED_VALUE, T.UNIT, T.SCPOE FROM TB_THRESHOLD_SETTING T WHERE T.PARAM_NAME = ?";
		String[] params = new String[]{"CSZJFX"};
		op.Query(sql, params);
		op.finalize();
	}
}