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.

Solution 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.

Solution 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.

Comments

[Andna]

Still the same error, I used this construct before, but somewhere I found the one i quoted, anyway both of them return the same error.

[drobson]

Sounds like this guy had the same problem and has posted a solution blogs.agilefaqs.com/2009/11/23/…

[Andna]

Still nothing, I can write to this folder, I even created contex.xml in my webapp, still the same error.

[drobson]

have u deleted the TOMCAT_HOME/Webapp/Projekt folder each time u rebuild? I've found if im working with .wars tomcat doesnt always clear the old code.

[Andna]

I tried that, besides even in Eclipse which I use this problem occurs. But I think that there may be something in xml files as suggested in links, because i get this null url and no class, I wonder what could be wrong.

[Sefran2]

@drobson: What is the DataSource type when accessing the datasource from a class java? I use org.apache.tomcat.jdbc.pool.DataSource, or it should be javax.sql.DataSource (the latter is the value of the type attribute of the Resource)?

[drobson]

@Cricket: org.apache.tomcat.jdbc.pool.DataSource is fine as it implements javax.sql.DataSource. When you set the resource type as javax.sql.DataSource your literally saying your going to use an object that implements javax.sql.DataSource.

[Sefran2]

@drobson: Thank you. So I can use one of them. Could I call the lookup in the classes in which I have the queries executions or is there a better way? Now, I call the lookup before any set of queries I have to do, but I don't know if the "Too many connections" error is due to this or if the reason is the context.xml configuration.

[drobson]

@Cricket you want to have one instance of each of your datasources. For example you would only want to have one EmployeeDB Datasource in your entire application. I personally prefer doing this by using Spring to Decouple all my DAOs and then inject the one instance of the datasource too them. Another way is to lookup the datasource in your session, then use a getter in the session to reference the datasource in your DAOs. BUT!!! i would recommend looking into a decoupling pattern!!

[Sefran2]

@drobson: What do you think if I used a singleton pattern for the datasource?

[PhoenixPerson]

This is awesome. Thanks.