Tomcat 5.5.4 에서 dbcp 사용법
- 톰켓 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>commonlib 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