Configuring JNDI Data Source for Database Connection Pooling in Tomcat?

In this post, we will learn how to create a JNDI(Java Naming and Directory Interface) resource that represents a JDBC Data Source in Tomcat, and then how to configure a Java web application in order to access the JNDI Data Source. The benefits of using a JNDI Data Source are:

It uses database connection pooling services provided by the container, i.e. Tomcat uses Commons DBCP (tomcat-dbcp.jar) and Commons Pool as the implementation.
It externalizes database connections and makes it independent from the web application.
It shares database connections across applications deployed in the Web container.

The following examples are tested in Tomcat 9 and MySQL Database 8

Project Structure using Tomcat Database Connection Pool:

NOTE: This web Application requires three jars jstl-1.2.jar,mysql-connector-java-8.0.28.jar &servlet-api-2.5.jar jars in WEB-INF/lib directory

1. Sample MySQL database

First, we need to create a sample database. Let’s execute the following MySQL script(Bookstore.sql):

CREATE DATABASE  IF NOT EXISTS jdbcpooldb;
CREATE TABLE IF NOT EXISTS `jdbcpooldb`.`book_table` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `author` varchar(45) NOT NULL,
  `price` float NOT NULL,
  PRIMARY KEY (`book_id`),
  UNIQUE KEY `book_id_UNIQUE` (`book_id`),
  UNIQUE KEY `title_UNIQUE` (`title`)
);

INSERT INTO `jdbcpooldb`.`book_table`
(`title`,
`author`,
`price`)
VALUES
('Core Java','Kathy Sierra',900.0);

INSERT INTO `jdbcpooldb`.`book_table`
(`title`,
`author`,
`price`)
VALUES
('Spring in Action',' Craig Walls',950.0);

CREATE DATABASE IF NOT EXISTS jdbcpooldb;

CREATE TABLE IF NOT EXISTS `jdbcpooldb`.`book_table` (

`book_id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(128) NOT NULL,

`author` varchar(45) NOT NULL,

`price` float NOT NULL,

PRIMARY KEY (`book_id`),

UNIQUE KEY `book_id_UNIQUE` (`book_id`),

UNIQUE KEY `title_UNIQUE` (`title`)

);

INSERT INTO `jdbcpooldb`.`book_table`

(`title`,

`author`,

`price`)

VALUES

('Core Java','Kathy Sierra',900.0);

INSERT INTO `jdbcpooldb`.`book_table`

(`title`,

`author`,

`price`)

VALUES

('Spring in Action',' Craig Walls',950.0);

This DB script will create a database schema is called ‘jdbcpooldb’ and a table called book_table. Here we are inserting some dummy data into this table using INSERT queries.

To interact with the MySQL database from the Java Web application, the MySQL Connector/J library must be present in the classpath. Here, we need to copy the mysql-connector-java-8.0.28.jar (or choose the correct version for your application) jar file to the application/lib directory.

2. Configure context

To declare a JNDI Data Source for the MySQL database, create a Resource XML element with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/bookstoreDB"
          auth="Container"
          type="javax.sql.DataSource"
          validationQuery="SELECT 1"
          validationInterval="30000"
          maxActive="100"
          minIdle="10"
          maxWait="10000"
          initialSize="10"
          jmxEnabled="true"
          username="root"
          password="root"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/jdbcpooldb"/>
</Context>

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

<Resource name="jdbc/bookstoreDB"

auth="Container"

type="javax.sql.DataSource"

validationQuery="SELECT 1"

validationInterval="30000"

maxActive="100"

minIdle="10"

maxWait="10000"

initialSize="10"

jmxEnabled="true"

username="root"

password="root"

driverClassName="com.mysql.jdbc.Driver"

url="jdbc:mysql://localhost:3306/jdbcpooldb"/>

</Context>

Add above element inside the root element <Context> in a context.xml file. There are a couple of places where the context.xml XML file can be resided (create one if not exist):

Inside /META-INFdirectory of a web application: the JNDI Data Source is only available to the application itself, thus it cannot be shared among other ones. In addition, this makes the configuration depend on the application.
Inside {tomcat installation directory}/conf directory: this is the preferred place because the JNDI Data Source will be available to all web applications and it’s independent of any applications.

The Below table explains the attributes specified in the above configuration:

Attribute name	Description
name	This represents the name of the resource.
auth	It specifies the authentication mechanism for the application code, the value can be Application or Container.
type	The fully qualified Java class name is expected by the web application when it performs a lookup for this specified resource.
validationQuery	This parameter basically used to validate connections from the connection pool before returning them to the caller.
validationInterval	It specifies to avoid excess validation, only run validation at most at this frequency – time in milliseconds.
maxActive	It specifies the maximum number of database connections in the pool. We can set it to -1 for no limit imposed.
minIdle	It Specifies the minimum number of established connections that should be kept in the pool at all times.
maxWait	It specifies the maximum time to wait for a database connection to become available in ms(milliseconds), in this example 10 seconds. An Exception will be thrown if this timeout is crossed. You can set it to -1 to wait indefinitely.
initialSize	This attribute is used to set the initial number of connections that are created when the connection pool is started. The default value is 10
jmxEnabled	It specifies to register the pool with JMX or not. The default value is true.
username	It specifies MySQL database user name.
password	It specifies MySQL database user password.
driverClassName	Here we specify the fully qualified class name of the database driver. For MySQL Connector/J, it is com.mysql.jdbc.Driver.
url	It specifies the JDBC connection URL.

For more information about the attributes, visit the reference link: Here

NOTES:

If you are using Tomcat inside Eclipse IDE then you will have to modify the XML file under the Servers project.

That is because Eclipse IDE made a copy of the Tomcat configuration.
If two resources with the same name are both declared in the XML files under the web application’s META-INF directory and in the {tomcat installation directory}/conf directory, then the internal version(META-INF\context.xml) takes precedence.

3. Below are the Java resources used like service, DAO, and Controller classes

Service Interface & Implementation class:

package com.infotech.bookstore.service;

import java.sql.SQLException;
import java.util.List;

import com.infotech.bookstore.model.Book;

public interface BookService {
	public abstract List<Book> getAllBooksInfo() throws SQLException;
}

package com.infotech.bookstore.service;

import java.sql.SQLException;

import java.util.List;

import com.infotech.bookstore.model.Book;

public interface BookService {

public abstract List<Book> getAllBooksInfo() throws SQLException;

}

package com.infotech.bookstore.service.impl;

import java.sql.SQLException;
import java.util.List;

import com.infotech.bookstore.dao.impl.BookDAOImpl;
import com.infotech.bookstore.model.Book;
import com.infotech.bookstore.service.BookService;
/**
 * This is a Service class which provides service to the controller
 */
public class BookServiceImpl implements BookService {

	@Override
	public List<Book> getAllBooksInfo() throws SQLException {
		return new BookDAOImpl().listAllBooks();
	}
}

package com.infotech.bookstore.service.impl;

import java.sql.SQLException;

import java.util.List;

import com.infotech.bookstore.dao.impl.BookDAOImpl;

import com.infotech.bookstore.model.Book;

import com.infotech.bookstore.service.BookService;

/**

* This is a Service class which provides service to the controller

public class BookServiceImpl implements BookService {

@Override

public List<Book> getAllBooksInfo() throws SQLException {

return new BookDAOImpl().listAllBooks();

}

DAO Interface & Implementation class:

package com.infotech.bookstore.dao;

import java.sql.SQLException;
import java.util.List;

import com.infotech.bookstore.model.Book;

public interface BookDAO {

	public abstract List<Book> listAllBooks() throws SQLException;
}

package com.infotech.bookstore.dao;

import java.sql.SQLException;

import java.util.List;

import com.infotech.bookstore.model.Book;

public interface BookDAO {

public abstract List<Book> listAllBooks() throws SQLException;

}

package com.infotech.bookstore.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.infotech.bookstore.dao.BookDAO;
import com.infotech.bookstore.model.Book;
import com.infotech.bookstore.util.DBUtil;

/**
 * This DAO class provides database operation for the table book
 * in the database.
 */
public class BookDAOImpl implements BookDAO {
	
	@Override
	public List<Book> listAllBooks() throws SQLException {
		List<Book> listBook = new ArrayList<>();
		
		String sql = "SELECT * FROM book_table";
		
		try(Connection connection = DBUtil.getDataSource().getConnection();
			Statement statement =connection .createStatement();
			ResultSet resultSet = statement.executeQuery(sql)) {
			
			while (resultSet.next()) {
				int id = resultSet.getInt("book_id");
				String title = resultSet.getString("title");
				String author = resultSet.getString("author");
				float price = resultSet.getFloat("price");
				
				Book book = new Book(id, title, author, price);
				listBook.add(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listBook;
	}
	
}

package com.infotech.bookstore.dao.impl;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import com.infotech.bookstore.dao.BookDAO;

import com.infotech.bookstore.model.Book;

import com.infotech.bookstore.util.DBUtil;

/**

* This DAO class provides database operation for the table book

* in the database.

public class BookDAOImpl implements BookDAO {

@Override

public List<Book> listAllBooks() throws SQLException {

List<Book> listBook = new ArrayList<>();

String sql = "SELECT * FROM book_table";

try(Connection connection = DBUtil.getDataSource().getConnection();

Statement statement =connection .createStatement();

ResultSet resultSet = statement.executeQuery(sql)) {

while (resultSet.next()) {

int id = resultSet.getInt("book_id");

String title = resultSet.getString("title");

String author = resultSet.getString("author");

float price = resultSet.getFloat("price");

Book book = new Book(id, title, author, price);

listBook.add(book);

}

} catch (Exception e) {

e.printStackTrace();

}

return listBook;

}

Model class:

package com.infotech.bookstore.model;

/**
 * This is a model class to hold entity data
 */
public class Book {
	
	protected int id;
	protected String title;
	protected String author;
	protected float price;
	
	public Book(int id, String title, String author, float price) {
		super();
		this.id = id;
		this.title = title;
		this.author = author;
		this.price = price;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getAuthor() {
		return author;
	}

	public void setAuthor(String author) {
		this.author = author;
	}

	public float getPrice() {
		return price;
	}

	public void setPrice(float price) {
		this.price = price;
	}
}

package com.infotech.bookstore.model;

/**

* This is a model class to hold entity data

public class Book {

protected int id;

protected String title;

protected String author;

protected float price;

public Book(int id, String title, String author, float price) {

super();

this.id = id;

this.title = title;

this.author = author;

this.price = price;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getAuthor() {

return author;

}

public void setAuthor(String author) {

this.author = author;

}

public float getPrice() {

return price;

}

public void setPrice(float price) {

this.price = price;

}

Utility class that provides database connection using DataSource:

package com.infotech.bookstore.util;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBUtil {
	private static DataSource dataSource;
	private static final String  JNDI_LOOKUP_SERVICE = "java:/comp/env/jdbc/bookstoreDB";
	static{
		try {
			Context context = new InitialContext();
			Object lookup = context.lookup(JNDI_LOOKUP_SERVICE);
			if(lookup != null){
				dataSource =(DataSource)lookup;
			}else{
				new RuntimeException("JNDI look up issue.");
			}
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	public static DataSource getDataSource(){
		return dataSource;
	}
}

package com.infotech.bookstore.util;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;

public class DBUtil {

private static DataSource dataSource;

private static final String JNDI_LOOKUP_SERVICE = "java:/comp/env/jdbc/bookstoreDB";

static{

try {

Context context = new InitialContext();

Object lookup = context.lookup(JNDI_LOOKUP_SERVICE);

if(lookup != null){

dataSource =(DataSource)lookup;

}else{

new RuntimeException("JNDI look up issue.");

}

} catch (NamingException e) {

e.printStackTrace();

}

public static DataSource getDataSource(){

return dataSource;

}

Controller Class:

package com.infotech.bookstore.controller;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.infotech.bookstore.model.Book;
import com.infotech.bookstore.service.impl.BookServiceImpl;

/**
 * This servlet acts as a controller and handling all
 * requests from the user.
 */
public class BookControllerServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		try {
			listBook(request, response);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void listBook(HttpServletRequest request, HttpServletResponse response)
			throws SQLException, IOException, ServletException {
		List<Book> listBook = new BookServiceImpl().getAllBooksInfo();
		request.setAttribute("listBook", listBook);
		RequestDispatcher dispatcher = request.getRequestDispatcher("bookList.jsp");
		dispatcher.forward(request, response);
	}
}

package com.infotech.bookstore.controller;

import java.io.IOException;

import java.sql.SQLException;

import java.util.List;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.infotech.bookstore.model.Book;

import com.infotech.bookstore.service.impl.BookServiceImpl;

/**

* This servlet acts as a controller and handling all

* requests from the user.

public class BookControllerServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

protected void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

try {

listBook(request, response);

} catch (SQLException e) {

e.printStackTrace();

}

private void listBook(HttpServletRequest request, HttpServletResponse response)

throws SQLException, IOException, ServletException {

List<Book> listBook = new BookServiceImpl().getAllBooksInfo();

request.setAttribute("listBook", listBook);

RequestDispatcher dispatcher = request.getRequestDispatcher("bookList.jsp");

dispatcher.forward(request, response);

}

4. Configure web.xml

Add the following declaration into the web.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
 
  <servlet>
		<servlet-name>ControllerServlet</servlet-name>
		<servlet-class>com.infotech.bookstore.controller.BookControllerServlet</servlet-class>
	</servlet>

	<servlet-mapping>
		<servlet-name>ControllerServlet</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>

	<error-page>
		<exception-type>java.lang.Exception</exception-type>
		<location>/error.jsp</location>
	</error-page>
</web-app>

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

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">

<servlet-name>ControllerServlet</servlet-name>

<servlet-class>com.infotech.bookstore.controller.BookControllerServlet</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>ControllerServlet</servlet-name>

<url-pattern>/</url-pattern>

</servlet-mapping>

<error-page>

<exception-type>java.lang.Exception</exception-type>

<location>/error.jsp</location>

</error-page>

</web-app>

This is necessary in order to make the JNDI Data Source available to the application under the specified namespace jdbc/bookstoreDB

5. Code a test JSP page

Now, create a JSP pages (bookList.jsp & error.jsp) to test the application.

bookList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
	<title>Books Store Application</title>
</head>
<body>
    <div align="center">
        <table border="1" cellpadding="5">
            <caption><h2>List of Books</h2></caption>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Author</th>
                <th>Price</th>
            </tr>
            <c:forEach var="book" items="${listBook}">
                <tr>
                    <td><c:out value="${book.id}" /></td>
                    <td><c:out value="${book.title}" /></td>
                    <td><c:out value="${book.author}" /></td>
                    <td><c:out value="${book.price}" /></td>
                </tr>
            </c:forEach>
        </table>
    </div>	
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<html>

<head>

<title>Books Store Application</title>

</head>

<body>

<caption><h2>List of Books</h2></caption>

<tr>

<th>Title</th>

<th>Author</th>

<th>Price</th>

</tr>

<c:forEach var="book" items="${listBook}">

<tr>

</tr>

</c:forEach>

</table>

</div>

</body>

</html>

error.jsp for error handling:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" isErrorPage="true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
	"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Error</title>
</head>
<body>
	<center>
		<h1>Error</h1>
		<h2><%=exception.getMessage() %><br/> </h2>
	</center>	
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8" isErrorPage="true" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Error</title>

</head>

<body>

<h1>Error</h1>

</center>

</body>

</html>

Here is the sample output when you run this application:

Download Source Code From Github

You May Alos Like:

What is Apache Tomcat?
What is SSL (Secure Sockets Layer)?
How to Change the Default Port of the Tomcat Server?

That’s All about Configuring JNDI Data Source for Database Connection Pooling in Tomcat?

1. Sample MySQL database

4. Configure web.xml

5. Code a test JSP page

Leave a Reply Cancel reply