DataBaseConnectionPool 사용법 - (Tomcat 5.5.4)

- 톰켓 5.5.4 에서 dbcp 사용법 입니다. 필요하신분에게 요긴하게 사용되리라 생각합니다.
- 출처 www.theserverside.com

Full Solution for Oracle 8i - 10g and Tomcat 5.5.4
Posted by: Gregg Lagnese on January 18, 2005 in response to Message #153211 1 replies in this thread
Thanks to everybody here I composed a solution that is repeatable in all of my IDE's and test setups. I hope someone finds this useful...

How to use JNDI for Oracle JDBC Connection Pooling with Tomcat 5.5.4

Pre-requisite setup:
Copy ojdbc14.jar (not 'ojdbc14.zip') to the <CATALINA_HOME>/common/lib directory.

Steps to Implement

1) In <CATALINA_HOME>/conf/server.xml between <GlobalNamingResources> and </GlobalNamingResources> add:

    <Resource name="jdbc/<alias>"
              auth="Container"
              type="javax.sql.DataSource"
              driverClassName="oracle.jdbc.driver.OracleDriver"
              factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
              url="jdbc:oracle:thin:@<server>:<port>:<sid>"
              username="<user>"
              password="<password>"
              maxActive="20"
              maxIdle="10"
              maxWait="-1" />
             
example -

  <!-- Global JNDI resources -->
  <GlobalNamingResources>

    <!-- Test entry for demonstration purposes -->
    <Environment name="simpleValue" type="java.lang.Integer" value="30"/>

    <!-- Editable user database that can also be used by
         UserDatabaseRealm to authenticate users -->
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
       description="User database that can be updated and saved"
           factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
          pathname="conf/tomcat-users.xml" />
         
    <Resource name="jdbc/devDb"
              auth="Container"
              type="javax.sql.DataSource"
              driverClassName="oracle.jdbc.driver.OracleDriver"
              factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
              url="jdbc:oracle:thin:@db1.oracle.com:1521:orcl"
              username="scott"
              password="tiger"
              maxActive="20"
              maxIdle="10"
              maxWait="-1" />
   
  </GlobalNamingResources>
             
2) In <CATALINA_HOME>/conf/context.xml between <Context> and </Context> add:

<ResourceLink global="jdbc/<alias>" name="jdbc/<alias>" type="javax.sql.DataSource"/>

example -

<!-- The contents of this file will be loaded for each web application -->
<Context>

    <!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>META-INF/context.xml</WatchedResource>

<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<ResourceLink global="jdbc/devDb" name="jdbc/devDb" type="javax.sql.DataSource"/>
</Context>

3) In the <CONTEXT>/WEB-INF/web.xml between <web-app> and </web-app> add:

    <resource-ref>
        <description>Oracle Development Datasource</description>
        <res-ref-name>jdbc/<alias></res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
   
example -

    <resource-ref>
        <description>Oracle Development Datasource</description>
        <res-ref-name>jdbc/devDb</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
   
4) Restart Tomcat

5) In a Java file named Connector.java add the following code

package com.microdeveloper.db.jndi;

import javax.naming.*;
import javax.sql.*;
import java.sql.*;

public class ConnectionPool {
    String message = "Not Connected";

    public void init() {
        Connection conn = null;
        ResultSet rst = null;
        Statement stmt = null;
        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            DataSource ds = (DataSource) envContext.lookup("jdbc/devDb");

            if (envContext == null)
                throw new Exception("Error: No Context");
            if (ds == null)
                throw new Exception("Error: No DataSource");
            if (ds != null) {
                conn = ds.getConnection();

                if (conn != null) {
                    message = "Got Connection " + conn.toString() + ", ";
                    stmt = conn.createStatement();
                    rst = stmt.executeQuery("SELECT 'Success obtaining connection' FROM DUAL");
                    if (rst.next()) {
                        message = rst.getString(1);
                    }
                    rst.close();
                    rst = null;
                    stmt.close();
                    stmt = null;
                    conn.close(); // Return to connection pool
                    conn = null; // Make sure we don't close it twice

                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Always make sure result sets and statements are closed,
            // and the connection is returned to the pool
            if (rst != null) {
                try {
                    rst.close();
                } catch (SQLException e) {
                    ;
                }
                rst = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    ;
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    ;
                }
                conn = null;
            }
        }

    }

    public String getMessage() {
        return message;
    }

}

6) Create a JSP file and add the following code:

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<html>
<head><title>JSP Page</title></head>
<body>

  <%
    com.microdeveloper.db.jndi.ConnectionPool ocp = new com.microdeveloper.db.jndi.ConnectionPool();
    ocp.init();
  %>

  <h2>Results</h2>
    Message: <%= ocp.getMessage() %>
</body>
</html>

7) Compile, deploy, and run. You should see...

Results
Message: Success obtaining connection

8) If not, check the following ...

  Verify database connectivity
a) TNSPing the database
b) Connect using the username and password in step 1
c) Verify the server, sid, and port

Driver errors (Cannot create JDBC driver of class '' for connect URL 'null'):
Place the ojdbc14.jar file in the <CATALINA_HOME>\common\lib directory
Do NOT place the JAR in your <CONTEXT>/WEB-INF/lib directory
If used with an IDE that auto-deploys, exclude the JAR from the deployment

Ensure that the resource link (step 2) is in either the <CONTEXT>/META-INF>context.xml file
- or - the <CATALINA_HOME>/META-INF>context.xml (for global deployment)

  Error messages that 'jdbc' is an unknown context:
  Verify step (3) is complete