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.

Community · Accepted Answer · 2017-05-23 11:45:34Z

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:

Instantiate a PoolProperties object to store your settings (URL to database, db user name, db password, and such).
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.

oers · Accepted Answer · 2012-02-09 14:52:33Z

13

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.

edited Feb 9, 2012 at 14:52

oers

18.8k13 gold badges68 silver badges76 bronze badges

answered Feb 9, 2012 at 14:49

drobson

9557 silver badges13 bronze badges

9 Comments

Andna Over a year ago

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 Over a year ago

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

Andna Over a year ago

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

drobson Over a year ago

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 Over a year ago

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.

|

Collectives™ on Stack Overflow

Tomcat and JDBC connection pooling

2 Answers 2

Comments

9 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Linked

Related