Tomcat and JDBC connection pooling

Question

I am trying to set up connection pooling to mysql databe with tomcat. My simple app is called Projekt, in my Projekt.xml in Apache/conf/Catalina/localhost I have

<Context docBase="Projekt.war" path="/Projekt">
  <Resource name="jdbc/mysqldb"
      auth="Container"
  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
      type="javax.sql.DataSource"
      driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost:3306/Music"
      username="andrzej"
      password="qazxsw"
      maxActive="20"
      maxIdle="30"
      maxWait="5"
  />
</Context> 

web.xml of my app

<servlet>
    <servlet-name>HelloServlet</servlet-name>
    <servlet-class>org.jtp.HelloServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>HelloServlet</servlet-name>
    <url-pattern>/Hai</url-pattern>
</servlet-mapping>

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

and in my Apache/lib folder I have

mysql-connector-java-5.1.18-bin.jar

but when I execute this code:

Context initContext  = new InitialContext();
dataSource = (DataSource)initContext.lookup("java:comp/env/jdbc/mysqldb");
System.out.println(dataSource.getConnection().createStatement().
            execute("select * from Users"));

I get exception

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

I am puzzled now, in some places I read that it may be caused by not placing driver in tomcat/lib, but I have it and it works, because when I tested the driver with manual connections it worked.

For my setup I was trying to follow http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html

EDIT: Finally got it working, it seemed that I had some left context tags in one of the files so when parsing he overriden other attributes, so it is all my fault at the end.

Answer 1

Looks like you are missing Context envCtx = (Context) initCtx.lookup("java:comp/env");
JNDI lookup should be done like this:

// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");

// Look up our data source
DataSource ds = (DataSource) envCtx.lookup("jdbc/EmployeeDB");

// Allocate and use a connection from the pool
Connection conn = ds.getConnection(); 

documentation from http://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html.

Answer 2

To achieve JDBC connection pooling with Tomcat, there is an alternative to the XML configuration files. I've never been able to get those Context and resource-ref tags to work. Furthermore those tags are overkill if you don't really need the JNDI features.

The alternative is using Java code to configure Tomcat's JDBC connection pooling. See example code in the Plain Ol' Java section of Tomcat 7 documentation page, The Tomcat JDBC Connection Pool.

Basically, you:

1. Instantiate a PoolProperties object to store your settings (URL to database, db user name, db password, and such).
2. Instantiate a Tomcat DataSource object while passing the PoolProperties object from step # 1 above.

Easy peasy. Like this…

PoolProperties p = new PoolProperties();
p.setUrl( jdbc:postgresql://localhost:5432/" + "my_database_name" );
p.setDriverClassName( "org.postgresql.Driver" );
p.setUsername( "someUserName" );
p.setPassword( "somePassword" );
…
DataSource datasource = new org.apache.tomcat.jdbc.pool.DataSource( p );
datasource.setPoolProperties(p);

To use the data source…

Connection conn = null;
try {
    conn = datasource.getConnection();
    …

The DataSource instance can be stored:

• As a member field on your Servlet.
• As a Singleton as mentioned in another answer's comments.
• As an “attribute” on the ServletContext using built-in mapping of String to Object, as described on an Answer of mine and as discussed on the Question How to get and set a global object in Java servlet context.