OracleConnectionPool

Oracle에서 제공하는 Connection Pooling 예제
테스트환경: Oracle 10g, JDK6.0, ojdbc14.jar


Connection Pool로부터 Connection을 얻어내는 예

package test;

import java.sql.Connection;
import javax.sql.PooledConnection;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;

public class OraclePoolTest {

 public static void main(String[] args) throws Exception{

        try {
              OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
              ocpds.setURL("jdbc:oracle:thin:@localhost:1521:orcl");
              ocpds.setUser("scott");
              ocpds.setPassword("tiger");
              // Create a pooled connection
              PooledConnection pc  = ocpds.getPooledConnection();
              // Get a Logical connection
              Connection con = pc.getConnection();
              //con을 사용하여 필요한 작업을 한다

              //con을 사용한 후에는.....
              con.close();
              pc.close();

        } catch (ClassNotFoundException e) {
              e.printStackTrace();
        }
 }
}

 얻어낸 Connection을 사용하고 닫는 절차

 package test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.PooledConnection;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;

public class OraclePoolTest {

 public static void main(String[] args) throws Exception{

        try {
              OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
              ocpds.setURL("jdbc:oracle:thin:@localhost:1521:orcl");
              ocpds.setUser("scott");
              ocpds.setPassword("tiger");
              // Create a pooled connection
              PooledConnection pc  = ocpds.getPooledConnection();
              // Get a Logical connection
              Connection con = pc.getConnection();
              Statement stmt = con.createStatement();
              String sql = "select ename from emp where empno=7369";
              ResultSet rs = stmt.executeQuery(sql);
              if(rs.next()){
               String ename = rs.getString("ENAME");
               System.out.println("ENAME: "+ename);
              }
              con.close();
              pc.close();

        } catch (Exception e) {
              e.printStackTrace();
        }
 }
}

 Stored Procedure를 실행하는 예

/* 다음과 같이 Stored Procedure를 생성한 후에 이 클래스를 테스트한다.
 create or replace PROCEDURE proc3
 (dept_no IN emp.deptno%TYPE,
 empcsr OUT SYS_REFCURSOR
 )
 is
 begin
 --CURSOR 타입변수에 데이터를 저장하는 방법이 특이하므로 주의요망
 open empcsr for select * from emp where deptno=dept_no;
 end proc3;
 /
 */

package test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import javax.sql.PooledConnection;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;

public class OraclePoolTest {

 public static void main(String[] args) throws Exception{

        try {
              OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
              ocpds.setURL("jdbc:oracle:thin:@localhost:1521:orcl");
              ocpds.setUser("scott");
              ocpds.setPassword("tiger");
              // Create a pooled connection
              PooledConnection pc  = ocpds.getPooledConnection();
              // Get a Logical connection
              Connection con = pc.getConnection();
              CallableStatement cstmt = con.prepareCall("{call proc3(?,?)}");
             
              cstmt.setInt(1, 30);
              cstmt.registerOutParameter(2, OracleTypes.CURSOR);
              cstmt.executeQuery();
             
              ResultSet rs = (ResultSet)cstmt.getObject(2);
              while(rs.next()){
               int empno = rs.getInt("EMPNO");
               String ename = rs.getString("ENAME");
               int sal = rs.getInt("SAL");
               System.out.println("사번:"+empno+ " 이름:"+ename+ " 급여:"+sal);
              }
              rs.close();
              con.close();
              cstmt.close();
              pc.close();

        } catch (Exception e) {
              e.printStackTrace();
        }
 }
}