Monday, 31 May 2010

Tomcat 6.0 / UCP Data Source Setup

To setup tomcat to use UCP as it's data source implementation here are the steps. I used 11.2 JDBC driver and UCP jar files to connect to a 11.2.0.1 RDBMS.

1. Copy ojdbc6.jar and ucp.jar into $TOMCAT_HOME/lib directory. You can download those JAR files from here.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

2. Add your Data Source setup in $TOMCAT_HOME/conf/server.xml as follows.


<!-- Define the default virtual host
Note: XML Schema validation will not work with Xerces 2.2.
-->

<Host name="localhost" appBase="webapps"
unpackWARs="true" autoDeploy="true"
xmlValidation="false" xmlNamespaceAware="false">

<Context docBase="demods" path="/demods" reloadable="true">
<Resource name="jdbc/UCPPool"
auth="Container"
factory="oracle.ucp.jdbc.PoolDataSourceImpl"
type="oracle.ucp.jdbc.PoolDataSource"
description="Pas testing UCP Pool in Tomcat"
connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
minPoolSize="2"
maxPoolSize="5"
inactiveConnectionTimeout="20"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=beast.au.oracle.com)(PORT=1523))(CONNECT_DATA=
(SERVICE_NAME=linux11gr2)))"

connectionPoolName="UCPPool"
validateConnectionOnBorrow="true"
sqlForValidateConnection="select 1 from DUAL" />
</Context>
</Host>

3. In your web projects you deploy to tomcat add the following to web.xml

<resource-ref>
<res-ref-name>jdbc/UCPPool</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

4. Your code to lookup the data source would be as follows.

private DataSource getDataSource (String dataSourceLocation) throws NamingException
{
// Get a context for the JNDI look up
Context ctx = new InitialContext();
Context envContext = (Context) ctx.lookup("java:/comp/env");

// Look up a data source
javax.sql.DataSource ds
= (javax.sql.DataSource) envContext.lookup (dataSourceLocation);


return ds;
}

private Connection getConnection (DataSource ds) throws SQLException
{
Connection conn = null;
// Get a connection object
conn = ds.getConnection();

return conn;
}

Note: The dataSouceLocation would be "jdbc/UCPPool"

5. Finally verify using jconsole that indeed your using UCP. In the example below we are verifying that the pool named "UCPPool" is indeed created.

6 comments:

Anonymous said...

You set the setSQLForValidateConnection but those were the old days. The Oracle UCP Developer's Guide says:

The setSQLForValidateConnection property is not
recommended when using an Oracle JDBC driver. UCP for JDBC
performs an internal ping when using an Oracle JDBC driver. The
mechanism is faster than executing an SQL statement and is
overridden if this property is set. Instead, set the
setValidateConnectionOnBorrow property to true and do not
include the setSQLForValidateConnection property.

Pas Apicella said...

Thanks for pointing that out..

Anonymous said...

With this configuration I am getting this error in tomcat 6

Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource

But is working in Jetty:(

Thanks.

Emac

Anonymous said...

About my last comment, the UPC works some times, and some times throws the exception (Tomcat and jetty)

oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource

Any Idea?

Emac

Pas Apicella said...

Would need the full stack trace to see why it couldn't create a Pool. It may indicate why not.

Pas Apicella said...

Also would be curious to know what your data source config looked like on jetty and what you did to set it up on that. I have never used Jetty but would be worth knowing.

1. Where does ucp.jar and ojdbc5.jar go? I assume some LIB dir
2. Where to define data source and what does it look like?
3. What is the JNDI look up code to find the DS?
4. Can all web apps use the same DS?

Thanks..