How to configure JNDI DataSource for Database Connection Pooling in Tomcat
- Details
- Written by Nam Ha Minh
- Last Updated on 05 August 2019   |   Print Email
- 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.
1. Sample MySQL database
First, we need to create a sample database. Let’s execute the following MySQL script:create database usersdb; use usersdb; CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(45) NOT NULL, `password` varchar(45) NOT NULL, `email` varchar(45) NOT NULL, PRIMARY KEY (`user_id`) );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.
2. Configure context
To declare a JNDI DataSource for the MySQL database above, create a Resource XML element with the following content:<Resource name="jdbc/UsersDB" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/usersDB" username="root" password="secret" />Add this element inside the root element <Context> in a context.xml file. There are two places where the context.xml file 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.
Attribute name | Description |
name | Name of the resource. |
auth | Specify authentication mechanism for the application code, can be Application or Container. |
type | The fully qualified Java class name expected by the web application when it performs a lookup for this resource. |
maxActive | Maximum number of database connections in pool. Set to -1 for no limit. |
maxIdle | Maximum number of idle database connections to retain in pool. Set to -1 for no limit. |
maxWait | 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. |
driverClassName | The fully qualified Java class name of the database driver. For MySQL Connector/J, it is com.mysql.jdbc.Driver. |
url | The JDBC connection URL. |
username | MySQL database user name. |
password | MySQL database user password. |
- 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-INF directory and in the $CATALINA_BASE/conf directory, then the internal version takes precedence.
3. Configure web.xml
Add the following declaration into the web.xml file:<resource-ref> <description>DB Connection</description> <res-ref-name>jdbc/UsersDB</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>This is necessary in order to make the JNDI DataSource available to the application under the specified namespace jdbc/UsersDB.
4. Code a test JSP page
Now, create a JSP page (UsersList.jsp) to test the configuration we have made:<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <sql:query var="listUsers" dataSource="jdbc/UsersDB"> select username, email from users; </sql:query> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Users List</title> </head> <body> <div align="center"> <table border="1" cellpadding="5"> <caption><h2>List of users</h2></caption> <tr> <th>Name</th> <th>Email</th> </tr> <c:forEach var="user" items="${listUsers.rows}"> <tr> <td><c:out value="${user.username}" /></td> <td><c:out value="${user.email}" /></td> </tr> </c:forEach> </table> </div> </body> </html>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:
<sql:query var="listUsers" dataSource="jdbc/UsersDB"> select username, email from users; </sql:query>Here’s a sample output when loading the JSP page (http://localhost:8080/JNDIDataSourceExample/UsersList.jsp):
5. Code a test Java servlet
We can look up the configured JNDI DataSource using Java code as follows:Context initContext = new InitialContext(); Context envContext = (Context) initContext.lookup("java:comp/env"); DataSource ds = (DataSource) envContext.lookup("jdbc/UsersDB"); Connection conn = ds.getConnection();After obtaining the connection, we can use it as trivial JDBC code:
Statement statement = conn.createStatement(); String sql = "select username, email from users"; ResultSet rs = statement.executeQuery(sql); // iterates over the result set...Here’s the source code of an example Java servlet:
package net.codejava.jdbc; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.DataSource; /** * This servlet class demonstrates how to access a JNDI DataSource that * represents a JDBC connection. * @author www.codejava.net */ @WebServlet("/listUsers") public class UsersListServlet extends HttpServlet { private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter writer = response.getWriter(); try { Context initContext = new InitialContext(); Context envContext = (Context) initContext.lookup("java:comp/env"); DataSource ds = (DataSource) envContext.lookup("jdbc/UsersDB"); Connection conn = ds.getConnection(); Statement statement = conn.createStatement(); String sql = "select username, email from users"; ResultSet rs = statement.executeQuery(sql); int count = 1; while (rs.next()) { writer.println(String.format("User #%d: %-15s %s", count++, rs.getString("username"), rs.getString("email"))); } } catch (NamingException ex) { System.err.println(ex); } catch (SQLException ex) { System.err.println(ex); } } }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:
@Resource(name = "jdbc/UsersDB") private DataSource dataSource;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:
@WebServlet("/listUsers") public class UsersListServlet extends HttpServlet { private static final long serialVersionUID = 1L; @Resource(name = "jdbc/UsersDB") private DataSource dataSource; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter writer = response.getWriter(); try { Connection conn = dataSource.getConnection(); Statement statement = conn.createStatement(); String sql = "select username, email from users"; ResultSet rs = statement.executeQuery(sql); int count = 1; while (rs.next()) { writer.println(String.format("User #%d: %-15s %s", count++, rs.getString("username"), rs.getString("email"))); } } catch (SQLException ex) { System.err.println(ex); } } }
References:
Other Tomcat Tutorials:
- How to deploy a Java web application on Tomcat
- How to Embed Tomcat Server into Java Web Applications
- How to Use Virtual Hosts in Tomcat
- How to set web application context path as server root in Tomcat
- How to configure session timeout in Tomcat
- How to add Tomcat server in Eclipse IDE
- How to change Tomcat port number
- How to change server location and deploy path for Tomcat in Eclipse
Comments