jugLviv

Meta


Share on:


Configuring JNDI DataSource for Database Connection Pooling in Tomcat

juglvivjuglviv

Configuring JNDI DataSource for Database Connection Pooling in Tomcat

This tutorial shows you how to create a JNDI resource that represents a JDBC DataSource in Tomcat, and then how to configure a Java web application in order to access the JNDI DataSource. The benefits of using a JNDI DataSource are:

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:
1
2
3
4
5
6
7
8
9
10
11
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 FilesApache Software FoundationTomcat 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:
1
2
3
4
5
6
7
8
9
10
11
12
<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.xmlfile can reside (create one if not exist):
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:
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.
For more information about the attributes, visit the reference links mentioned the end of this tutorial.
NOTES:

3. Configuring web.xml

Add the following declaration into the web.xml file:
1
2
3
4
5
6
<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 namespacejdbc/UsersDB.
 

4. Coding test JSP page

Now, create a JSP page (UsersList.jsp) to test the configuration we have made:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<%@ 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:
1
2
3
<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):
JNDI DataSource Example Output 1

5. Coding test Java servlet

We can look up the configured JNDI DataSource using Java code as follows:
1
2
3
4
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:
1
2
3
4
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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):
JNDI DataSource Example Output 2
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:
1
2
@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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@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);
        }
    }
 
}
 Орига
juglviv
Author