Where do I have to place the JDBC driver for Tomcat's connection pool?

Question

So I've figured out my error, now I'm just looking for some insight as to what is going on exactly. I am using Apache Tomcat Version 7.0.32. I am using this tutorial to set up pooling for JDBC. In my META-INF folder I made a context.xml file and put this in there.

<?xml version="1.0" encoding="UTF-8"?>

<Context>
    <Resource type="javax.sql.DataSource" name="jdbc/gmustudent"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" 
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/official"
        username="root" password="root"
        maxActive="100" maxIdle="20" minIdle="15" initialSize="15" maxWait="10000" />
</Context>

I got this error when I wrote this

WARNING: Unexpected exception resolving reference
java.sql.SQLException: com.mysql.jdbc.Driver
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:254)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:699)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:631)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:485)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:143)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:116)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:103)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:539)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:237)
    at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:143)
    at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:304)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:843)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:154)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:831)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:168)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1061)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:671)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:270)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5173)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:680)
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:247)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:246)
    ... 29 more
Oct 31, 2012 11:23:25 AM org.apache.catalina.core.NamingContextListener addResource
WARNING: Failed to register in JMX: javax.naming.NamingException: com.mysql.jdbc.Driver
Oct 31, 2012 11:23:25 AM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["http-bio-8086"]
Oct 31, 2012 11:23:25 AM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["ajp-bio-8009"]
Oct 31, 2012 11:23:25 AM org.apache.catalina.startup.Catalina start
INFO: Server startup in 794 ms

This error only comes up when I have this statement in my context.xml file. When I remove it no error.

factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" 

The reason I want to make sure that I have it is because in the apache tomcat tutorial it says

factory is required, and the value should be org.apache.tomcat.jdbc.pool.DataSourceFactory

So then I did a little bit of research on SO and found a post saying that you need to add this jar to your lib folder if you have an older version of tomcat. So I added the jar and now it works but I would like to have some information as to what on earth is going on because I am using the newest tomcat version. So why when I specify a factory do I get an error. And what is this jar that I'm adding in and why is there very little documentation about it? Any information about what is going on here would be greatly appreciated.

Fumarole

Answer 1

The JDBC driver has to be visible to the same classloader as the data source factory itself. The data source factory library is placed in Tomcat's own /lib folder and thus loaded by Tomcat's "common" classloader.

Your problem sounds much like that you dropped the JDBC driver in webapp's /WEB-INF/lib. The webapp's /WEB-INF/lib is invisible to the "common" classloader. So technically, you have to place the JDBC driver in Tomcat's own /lib folder (or, at least, in a configurable path as specified by common.loader setting in /conf/catalina.properties) in order to make it visible to the data source factory.

Or, as you attempted, copying the data source factory into /WEB-INF/lib will also fix it. The webapp's /WEB-INF/lib has namely higher precedence in classloading than Tomcat's /lib folder. So if the data source factory is found in /WEB-INF/lib, it will be loaded from there. As the JDBC driver is also there, it will be seen. This is however not the right solution to your concrete problem, this is more a workaround, so you shouldn't do this.

There isn't exactly documentation which is specifically targeted to this issue. The Tomcat Class Loader HOW-TO will however help in understanding the class loading hierarchy in Tomcat.

See also:

• The infamous java.sql.SQLException: No suitable driver found
• How should I connect to JDBC database / datasource in a servlet based application?
• Is it safe to use a static java.sql.Connection instance in a multithreaded system?

Bash

THANK YOU THANK YOU THANK YOU! You are honestly the best! I understand everything you are saying and why I was getting an error. The only thing that I do not understand is where "Tomcat's own /lib" folder is? I am currently using eclipse and I have two folders in my workspace. gmustudent and Servers. When I expand Servers I see a folder Tomcat v7.0 Server at localhost-config and in that there are a couple of XML files. I tried putting it in that folder but that was a huuuuge error! So I apologize for the second question but what do you mean by "Tomcat's own /lib folder? – Fumarole

In Tomcat's installation folder on the local disk file system. E.g. C:\path\to\apache-tomcat-7.0.30. This is not to be managed from inside Eclipse. The /conf folder mentioned in my answer is also in there. – Bash

Wow that makes so much sense! I wish I could talk to you about this for hours! I have a couple quick questions of things I want to clear up about this if you don't mind. 1) You say the datasource factory is in that lib folder. Which jar is it in? 2) This may be a similair answer to 1 but where is this located factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" exactly? Is this pointing to a jar in that folder or somewhere else? 3) Should I keep my jdbc driver in that lib folder from now on or is it good practice to put it in WEB-INF/lib unless you need it elsewhere like this scenario? – Fumarole

It's the tomcat-jdbc.jar file. The factory value is the fully qualified class name which is to be passed to Class#forName() in Tomcat's internal code so that it could be loaded and instantiated. Yes, the JDBC driver JAR file should be kept in Tomcat's /lib (or at least in a path as specified by common.loader property). – Bash

So basically when I start my server factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" is loaded into ClassforName somewhere behind the scenes in Tomcats internal code and then it loads that jar (tomcat-jdbc.jar) which allows me to utilize connection pooling? Do I got it!? lol – Fumarole

Not exactly that. When Tomcat starts, it loads context.xml file(s), it finds resource of type javax.sql.DataSource with a factory attribute. It does a DataSourceFactory factory = (DataSourceFactory) Class.forName(factoryClassName).newInstance(); and then DataSource dataSource = factory.createDataSource(properties); with all other properties found in the XML to finally get the concrete DataSource instance which has a getConnection() method. The JAR file isn't exactly loaded, it's just "already" in the classpath, so that the factory class can be found. – Bash

See also tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/… for Tomcat's javadoc on DataSourceFactory. By the way, JDBC drivers work roughly the same way. – Bash

Thank you so much! I really appreciate your insight! I did not understand this at all yesterday and now I fully get what is happening! Thank you! – Fumarole

You're welcome :) It's called the abstract factory pattern. – Bash

Hello again! I have another question about this if you would be so kind. I have now moved my site to MochaHosting and off of my local host and it has been an absolute nightmare! I don't know why things are not working correctly. I configured my context.xml file to map to the new database but I keep getting this error "WARNING: Failed to register in JMX: javax.naming.NamingException: Access denied for user 'ccarrin2_ccarrin'@'localhost' (using password: YES)" I know the url username and password in context.xml are correct so I don't know what to do? Should I maybe move this to server.xml? – Fumarole

It's really the username/password combo which isn't accepted for the given DB as specified in the JDBC URL. Take care with case sensitivity and whitespace. Also make sure that the DB name in JDBC URL is correct. JDBC driver and all is fine. It would otherwise not come to that point where you are. Contact the hosting support if in vain. – Bash

Your the best! Couldn't imagine going to anyone else for help thank you! – Fumarole

Answer 2

I had the same problem, your solution didn't work for me. I had to add the 'mysql-connector-java-5.1.30-bin.jar' file to my lib folder for it to work, since i was using that driver :) Cheers.

Kidderminster