Creating a Connection Resource in Tomcat

MySQL, like most other relational database vendors, supplies a JDBC driver for connecting to MySQL databases from Java. You need to download this driver (a JAR file) and place it in your Tomcat installation so that you can use it from your applications. This may strike you as odd because other JAR files you use are simply packaged with your applications in /WEB-INF/lib. You must never do this with a JDBC driver for two reasons:

- Most important, doing so can cause a memory leak. JDBC drivers automatically register themselves with the java.sql.DriverManager, which is part of the Java SE core libraries. If your application includes a JDBC driver in /WEB-INF/lib, the DriverManager holds on to the driver classes forever, even if your application is undeployed. The application server then cannot fully undeploy your application, resulting in a memory leak.

- It’s a best practice to have the application server manage your JDBC DataSources. Application servers have built-in systems for managing connection pools, improving the performance of database connections in your applications. For the application server to be able to manage these connections, you must load the JDBC driver in the application server class loader instead of the web application class loader.

Installing the MySQL JDBC driver in Tomcat is extremely easy. On the MySQL download website locate the Connector/J product. This is the JDBC driver. It is platform-independent, so all you need to do is download the ZIP or TAR archive (whichever is easiest for you to use on your computer). Extract the JAR file from the archive, and copy it to C:\Program Files\Apache Software Foundation\Tomcat 8.0\lib (or the equivalent directory for your Tomcat installation).
That’s all there is to it. The next time Tomcat starts, the MySQL JDBC driver becomes available to all web applications.

Creating a Connection Resource in Tomcat

Creating a pooled connection DataSource in Tomcat is quite straightforward and takes only a few minutes. Instead of creating a brand new resource every time and having dozens of resources by the end, it is also acceptable to simply create one resource and change it every time you need to.

1. To create a connection resource, open C:\Program Files\Apache Software Foundation\Tomcat 8.0\conf\context.xml (or the equivalent in your Tomcat installation) in your favorite text editor.

2. Add the following <Resource> element between the beginning and end <Context> elements:
     <Resource name="jdbc/DataSourceName" type="javax.sql.DataSource"
          maxActive="20" maxIdle="5" maxWait="10000"
          username="mysqluser" password="mysqlpassword"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/databaseName" />

3. For each example you must replace jdbc/DataSourceName with the appropriate data source name (which should always start with jdbc/) and databaseName with the correct database name. mysqluser and mysqlpassword should be replaced with the user and password that you previously created to access MySQL.

This <Resource> definition causes Tomcat to expose the connection pool DataSource as a JNDI resource so that it can be looked up via JNDI by any application.