Servlet JNDI DataSource in Tomcat

By Nithya Vasudevan

Environment Used

  • JDK 6 (Java SE 6) (To install JDK – Windows , Ubuntu)
  • Eclipse Indigo IDE for Java EE Developers (3.7.1) (To install Eclipse, refer this page)
  • Apache Tomcat 6.x (To install Tomcat refer this page)
  • MySQL 5.5 (To install MySQL refer this page)
  • MySQL Connector/J 5.1 JAR file
  • Java EE 5 API (Servlet 2.5)
  • Java Database Connectivity (JDBC) API (java.sql.*, javax.sql.*)
  • [Optional] For monitoring and analyzing HTTP headers between the browser and web servers, you can use one of these add-ons of Firefox
    • Live HTTP Headers
    • HttpFox

Setting up development environment

If you are new to developing Servlet with Tomcat and Eclipse, you can read this page before proceeding with this example.

Project Description

  • This example explains how to configure datasource in Apache Tomcat and look it up in Servlet.
  • This application uses one table ‘Employee‘ and displays employee details to the user.
  • We use Eclipse IDE for Java EE Developers and Apache Tomcat to run the Servlet.

What is javax.sql.DataSource?

  • Datasource is a Java object which represents a factory of connections to the physical data source. DataSource is preferred over DriverManager facility.
  • The DataSource interface is implemented by a driver vendor and will typically be registered with a naming service based on the Java Naming and Directory (JNDI) API.
  • DataSource is usually configured and managed by the application server instead of your application. DataSource object is retrieved through a lookup operation using JNDI API and then used to create a Connection object.
  • Advantage of DataSource:
    • A DataSource object has properties that can be modified when necessary. If the data source is moved to a different server, the property for the server can be changed and any code accessing that data source does not need to be changed.

Create Database and table in MySQL

This example uses one table ‘Employee‘ and the description of the table is shown below.

Field Type Key Extra
emp_id int Primary Key auto_increment
emp_name varchar(255)
salary double
dept_name varchar(255)
  • Open command prompt (Windows) or Terminal(Linux) and type
    mysql –u [your-username] –p
    and press enter and type the password.
  • If you are using Windows, you can also use MySQL command line client which will be available in All programs menu.
  • For creating a new database, refer this page. In this example, the database name is ‘exampledb‘.
  • After creating the database type the command “use <database_name>;”
  • For creating a new table, refer this page. In this example, the table name is ‘employee’
  • Insert some records in the table. Refer this page for inserting rows using MySQL.

Create Dynamic Web Project

  • Open Eclipse IDE
  • For writing Servlet and JSP, we need to create a new Dynamic Web project. Create this project and name it as “ServletDatasource“.

Download MySQL connector

  • The connector can be downloaded from: http://dev.mysql.com/downloads/connector/j/.
  • This tutorial uses 5.1 version. Unzip the connector to a safe location on your computer which contains MySQL Connector J JAR.
  • Copy the MySQL Connector J JAR and paste it inside project’s ‘lib’ folder.

Configure Context

  • Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to your Context in context.xml.
  • This xml file contains context information and can be placed in one of the location as explained below,
    • In the /conf/context.xml file
    • In the /conf/[enginename]/[hostname]/context.xml.
    • META-INF/context.xml inside the application.

We will place the context.xml file in appliction’s META-INF folder.
Create a new XML file in META-INF folder and copy the following content.

<?xml version="1.0" encoding="UTF-8"?>
 <!-- The contents of this file will be loaded for each web application -->
  <Context crossContext="true">

	<!-- Default set of monitored resources -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
	
   <Resource name="jdbc/testDB" auth="Container" 
		type="javax.sql.DataSource"
		maxActive="100" maxIdle="30" maxWait="10000"
		username="<YOUR_DATABASE_USERNAME>" 
		password="<YOUR_DATABASE_PASSWORD>" 
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/exampledb"/>
</Context>

Fill the username and password for your database and enter your database name in the url string.

Create Servlet

Here, we lookup the JDBC resource and store it in DataSource object. Using this object we create Connection object. We are creating SQL Statement and executing the query and printing the result.

package com.theopentutorials.servlets;

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.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

public class EmployeeServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	public EmployeeServlet() {}

	public void doGet(HttpServletRequest request, 
		HttpServletResponse response) 
		throws ServletException, IOException {
		
		Context envContext = null;
		try {
			envContext = new InitialContext();
			Context initContext  = (Context)envContext.lookup("java:/comp/env");
			DataSource ds = (DataSource)initContext.lookup("jdbc/testDB");
			//DataSource ds = (DataSource)envContext.lookup("java:/comp/env/jdbc/testDB");
			Connection con = ds.getConnection();
						
			Statement stmt = con.createStatement();
			String query = "select * from employee";
			ResultSet rs = stmt.executeQuery(query);
			
			PrintWriter out = response.getWriter();
			response.setContentType("text/html");
			out.print("<center><h1>Employee Details</h1></center>");
			out.print("<html><body><table border=\"1\" cellspacing=10 cellpadding=5>");
			out.print("<th>Employee ID</th>");
			out.print("<th>Employee Name</th>");
			out.print("<th>Salary</th>");
			out.print("<th>Department</th>");
			
			while(rs.next())
			{
				out.print("<tr>");
				out.print("<td>" + rs.getInt("emp_id") + "</td>");
				out.print("<td>" + rs.getString("emp_name") + "</td>");
				out.print("<td>" + rs.getDouble("salary") + "</td>");
				out.print("<td>" + rs.getString("dept_name") + "</td>");
				out.print("</tr>");				
			}
			out.print("</table></body></html>");		
		}  catch (SQLException e) {
			e.printStackTrace();
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
}
  • There is a global JNDI namespace, and it uses names that are vendor dependent, possibly not portable. Every EJB, JDBC pool, JMS Topic or Queue, you create in the server, has its name in the global JNDI.
  • “java:comp/env” is a utility context and every resource (for example, JDBC, EJB, etc) has its own java:comp/env context populated with resources such as datasources, ejbs, etc.
  • To lookup JDBC resources in that global namespace, we use “jdbc/” as defined in name attribute of element in context.xml. In our case, it is “jdbc/testDB“.

Instead of doing the lookup twice (one for global JNDI namespace and another for JDBC resource) as shown above, we can do a single lookup to the JDBC resource in the namespace as shown below. 

Context envContext = new InitialContext();
DataSource ds = (DataSource)envContext.lookup("java:/comp/env/jdbc/testDB");

Configure web.xml

A resource reference is an element in a deployment descriptor that identifies the component’s coded name for the resource. More specifically, the coded name references a connection factory for the resource. In the example in the following section, the resource reference name is “jdbc/testDB“.
Now create a WEB-INF/web.xml for this application.

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns="http://java.sun.com/xml/ns/javaee" 
	xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" 		
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javae		
	/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
    <resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/testDB</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    </resource-ref>
  <servlet>
    <servlet-name>EmployeeServlet</servlet-name>
    <servlet-class>com.theopentutorials.servlets.EmployeeServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>EmployeeServlet</servlet-name>
    <url-pattern>/employee.do</url-pattern>
  </servlet-mapping>
</web-app>

Folder Structure

The complete folder structure of this example is shown below.

Output

Use Ctrl + F11 in Eclipse to run the Servlet. The URL for requesting the Servlet is
http://localhost:8080/ServletDatasource/employee.do

Program control flow

  • Web client (browser) sends requests to Servlet through request URL. Since the request is sent directly to Servlet it is a HTTP GET Request
  • Servlet in doGet() method, looks up (using JNDI) JDBC resource (jdbc/testDB) in global JNDI namespace (java:/comp/env) and stores it in DataSource object. Using this object it creates Connection and executes SQL statement and responds with actual HTML content

The content of HTTP request and response headers can be monitored using browser add-ons such as Live HTTP Headers, HttpFox, etc

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Servlet DataSource Resource Injection in TomcatServlet
Get all parameters in HTML form using getParameterMap()Servlet