TOMCAT DBCP 설정하기

1. 필수JAR 다운로드

 commons-dbcp.jar
 http://commons.apache.org/dbcp/downloads.html

 commons-collections.jar
 http://commons.apache.org/downloads/download_collections.cgi

 commons-pool.jar
 http://commons.apache.org/pool/download_pool.cgi

 Oracle - classes14.jar (ojdbc14_g.jar ==> 10g)

 

C:\apache-tomcat-설치경로\common\lib
위 4가지를 복사한다.

 

---

2. tomcat의 server.xml 수정

(C:\workspace\project_name\Servers\Tomcat v5.5 Server at localhost-config\server.xml)

       <Context docBase="project_name" path="/" reloadable="true"

                                                                 source="org.eclipse.jst.j2ee.server:SERVER_NAME">
            <Resource name="jdbc/MY_ORACLE" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@127.0.0.1:1521:MY_SID"
              username="USER_ID" password="USER_PW" maxActive="20" maxIdle="10"
              maxWait="-1"/>
      </Context>

 

---

3. web.xml 수정

(C:\workspace\project_name\context_name\WebContent\WEB-INF\web.xml)

    <resource-ref>
        <description>Forum DB Connection</description>
        <res-ref-name>jdbc/MY_ORACLE</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

 

 

---

4. Connection Factoty Class를 만든다
(예제 - MYDBConnection.java)

package com.db;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
*  MYDBConnection.java
* @author admin@edu4edu.net
* @since 2009-12-16
* @version 2009-12-16 Initial Release 1.0<BR>
* Connection Pool로 부터 Connection을 가져온다.
*/
public class MYDBConnection {

private static DataSource ds = null;
private static String source = "jdbc/MY_ORACLE";
private static int poolcount;
private String caller = "unknown";
private Connection conn = null; 

public MYDBConnection() {}
public MYDBConnection(String caller) {this.caller = caller;}
public MYDBConnection(Object obj)    {this.caller = obj.getClass().getName();}

static {
    try {
        // Create the initial naming context.
        Context initContext = new InitialContext();                             
        Context envContext  = (Context)initContext.lookup("java:/comp/env");    
        ds = (DataSource)envContext.lookup(source);                    
        poolcount = 100; 
        System.out.println("Pool Count = "+ source +"[Pool Count : "+ poolcount +"]" );
    } catch (Exception e) {
        System.out.println("exception: "+ e.getMessage());
    }
}  // end of static block


public Connection getConnection() throws Exception {
    try {
        conn = ds.getConnection();              
        System.out.println("[Pool Count : "+(--poolcount)+":"+caller+"]");
    } catch (Exception e) {
        e.printStackTrace();
    }

    if ( conn == null ) {
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:MY_DB_SID";
        conn = DriverManager.getConnection(url, "USER_ID", "USER_PW");
    }
    printInvoker();
    return conn;
}

public boolean closeConnection() throws Exception {
    if (conn != null) {
        try {
            if ( !conn.isClosed() ) conn.close();
            System.out.println("[Pool Count :"+(++poolcount)+":"+caller+"]");
        } catch (Exception e) {
            System.out.println("Pool exception: " + e.getMessage()+ ": "+caller);
        }
        conn = null; 
        return true;           
    } else {
        return true; 
    }
}

public void printInvoker() {
    try {
        Exception e = new Exception();
        CharArrayWriter chw = new CharArrayWriter();
        PrintWriter pw = new PrintWriter(chw);
        e.printStackTrace(pw);
        pw.flush();
        char buf[] = chw.toCharArray();
        BufferedReader br = new BufferedReader(new CharArrayReader(buf));
        br.readLine();
        br.readLine();
        br.readLine();
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }
    return;
}

}

 

---

5. test.jsp로 테스트 한다.

<%@ page contentType="text/html;charset=euc-kr" %>
<%@ page import="java.io.*, java.util.*,java.sql.*" %>
<%@ page import="com.db.MYDBConnection" %>
<%
    MYDBConnection mycon        = null;
    Connection con                   = null;
    PreparedStatement pstmt     = null;
    ResultSet rs = null;
 
    StringBuffer strSql = new StringBuffer(); 
    strSql.append("\n").append(" SELECT");
    strSql.append("\n").append("    to_char(sysdate, 'yyyy-mm-dd') AS dt");
    strSql.append("\n").append(" FROM dual");

try{
      mycon = new MYDBConnection(); 
      con = mycon.getConnection();
      pstmt   = con.prepareStatement( strSql.toString() );
      int index = 1;
      //pstmt.setString( index++,   "dt");
      rs = pstmt.executeQuery();
   
      if ( rs.next() ) {
        out.print( "<HR>" );
        out.print( (String)rs.getString(1));
        out.print( "<HR>" );
      }
 }catch( SQLException sql_e ){   sql_e.printStackTrace();
 }catch( Exception e ){ e.printStackTrace();
 }finally {
        if(rs != null)         try {  rs.close();   } catch (SQLException sqlEx) {}
        if(pstmt != null)   try {  pstmt.close();} catch (SQLException sqlEx) {}
        if(con != null)       try {  con.close();} catch (SQLException sqlEx) {}
        if(mycon != null)  try {  mycon.closeConnection();} catch (SQLException sqlEx) {}
 }

%>

<참고>

http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html

http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

http://blog.paran.com/miso/8758335

http://blog.naver.com/jambeer?Redirect=Log&logNo=90000104051

 

 

- write by 꼬마갱이 09.12.24 -