OracleDataSource 를 이용한 Connection Pooling

Java Stored Procedure 에서도 외부 DB 연결시 사용 가능하나, 사전에 아래와 같이 권한이 있어야 함.

exec dbms_java.grant_permission('schemaName', 'SYS:java.net.SocketPermission', 'host:port', 'connect,resolve');

package test;
import java.sql.*;

import javax.sql.PooledConnection;

import oracle.jdbc.pool.OracleConnectionCacheManager;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;
import oracle.jdbc.pool.OracleDataSource;

public class OracleDatasourceTest {
 
 private static OracleDataSource ds;
 
 static {
  try{
     ds = new OracleDataSource();

     ds.setDriverType("thin");
     ds.setServerName("localhost");
     ds.setPortNumber(1521);
     ds.setDatabaseName("orcl"); // sid
     ds.setUser("scott");
     ds.setPassword("tiger");
     ds.setConnectionCachingEnabled(true);
     ds.setConnectionCacheName("MyCache");
       
     java.util.Properties sProperties = new java.util.Properties();
     sProperties.setProperty("MinLimit", "1");
     sProperties.setProperty("MaxLimit", "3");
     sProperties.setProperty("ValidateConnection", "true"); // Validates connection before sending to client
         
     ds.setConnectionCacheProperties(sProperties);
     OracleConnectionCacheManager occm = null;
     occm = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
     occm.createCache("MyCache", ds, sProperties);
  }catch(Exception e){}
 }// end of static block
 
 public static OracleDataSource getDataSource(){
  return ds;
 }
 
 public static void main(String[] args)throws Exception {
  long start = System.currentTimeMillis();
  for(int i=0;i<50;i++){
   OracleDataSource ds = getDataSource();
   Connection conn = ds.getConnection();
   Statement stmt = conn.createStatement();
   String sql = "select empno, ename from emp where empno=7369";
   ResultSet rs = stmt.executeQuery(sql);
   int empno = 0;
   String ename = null;
   while(rs.next()){
    empno = rs.getInt("EMPNO");
    ename = rs.getString("ENAME");
   }
   rs.close();
   stmt.close();
   conn.close();
  }
  long end = System.currentTimeMillis();
  System.out.println("경과시간:"+(end-start));
 }
}