- Utilizing database connection pooling services provided by the container, i.e. Tomcat uses Commons DBCP and Commons Pool as the implementation (tomcat-dbcp.jar).
- Externalizing database connection and make it independent from the web application itself.
- Sharing database connections across applications deployed in the container.
The following examples are tested in Tomcat 7 and MySQL Database 5.5.
1. Sample MySQL database
First, we need to create a sample database. Let’s execute the following MySQL script:
That creates a database called usersdb and a table called users. Remember to insert some dummy data into this table.
To interact with MySQL database from Java applications, the MySQL Connector/J library must present in the classpath. Here, we need to copy the mysql-connector-java-VERSION-bin.jar file to the $CATALINA_BASE/lib directory. If you have only one Tomcat instance on your computer, then $CATALINA_BASE is the Tomcat’s installation directory, e.g. c:\Program Files\Apache Software Foundation\Tomcat 7.0 on Windows platform. Doing so help Tomcat loads MySQL JDBC driver when it discovers the JNDI DataSource configuration.
Recommended Book: High Performance MySQL: Optimization, Backups, and Replication
2. Configuring context
To declare a JNDI DataSource for the MySQL database above, create a Resource XML element with the following content:
Add this element inside the root element <Context> in a context.xml file. There are two places where the context.xmlfile can reside (create one if not exist):
- Inside /META-INF directory of a web application: the JNDI DataSource is only available to the application itself, thus it cannot be shared among other ones. In addition, this makes the configuration dependent on the application.
- Inside $CATALINA_BASE/conf directory: this is the preferred place because the JNDI DataSource will be available to all web applications and it’s independent of any applications.
Therefore, we declare above Resource element in the context.xml file under the $CATALINA_BASE/conf directory. The following table describes the attributes specified in the above configuration:
Name of the resource.
Specify authentication mechanism for the application code, can be Application or Container.
The fully qualified Java class name expected by the web application when it performs a lookup for this resource.
Maximum number of database connections in pool. Set to -1 for no limit.
Maximum number of idle database connections to retain in pool. Set to -1 for no limit.
Maximum time to wait for a database connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely.
The fully qualified Java class name of the database driver. For MySQL Connector/J, it is com.mysql.jdbc.Driver.
The JDBC connection URL.
MySQL database user name.
MySQL database user password.
For more information about the attributes, visit the reference links mentioned the end of this tutorial.
- If you are using Tomcat inside Eclipse IDE, you need to modify the context.xml file under the Servers project. That is because Eclipse made a copy of Tomcat configuration:
- If two resources with the same name both declared in the context.xml files under the web application’s META-INFdirectory and in the $CATALINA_BASE/conf directory, then the internal version takes precedence.
3. Configuring web.xml
Add the following declaration into the web.xml file:
This is necessary in order to make the JNDI DataSource available to the application under the specified namespacejdbc/UsersDB.
4. Coding test JSP page
Now, create a JSP page (UsersList.jsp) to test the configuration we have made:
Here, we use the JSTL’s SQL tag query to make a SELECT query to the database. Note that the dataSource attribute refers to the JNDI resource name declared in the web.xml file:
Here’s a sample output when loading the JSP page (http://localhost:8080/JNDIDataSourceExample/UsersList.jsp):
5. Coding test Java servlet
We can look up the configured JNDI DataSource using Java code as follows:
After obtaining the connection, we can use it as trivial JDBC code:
Here’s the source code of an example Java servlet:
And the following is a sample output when calling the servlet (http://localhost:8080/JNDIDataSourceExample/listUsers):
Alternatively, we can use the @Resource annotation (javax.annotation.Resource) instead of the lookup code above. For example, declare a field called dataSource in the servlet like this:
Tomcat will look up the specified resource name and inject an actual implementation when it discovers this annotation. Therefore, the servlet code looks like this: