Servlet DataSource Resource Injection in Tomcat

20 August 2012 By Nithya Vasudevan 8,784 views 6 Comments
9 Flares 0 9 Flares ×

Project Description

  • In the previous Servlet example, we configured datasource in Apache Tomcat and used JNDI look up.
  • This Servlet example explains how to configure datasource in Apache Tomcat and inject it in Servlet using @Resource annotation.
  • For simplicity, all the JDBC codes and HTML responses are coded in the Servlet.
  • Refer this example which explains how to use Datasource in Servlet using MVC, Post-Redirect-Get (PRG), DAO, TO, Singleton patterns and creating user-defined application exception used by DAO methods

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

Prerequisites

Create Database and table in MySQL

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

FieldTypeKeyExtra
emp_idintPrimary Keyauto_increment
emp_namevarchar(255)
salarydouble
dept_namevarchar(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 “ServletDSResInjection“.

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 place this 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

Create a new Servlet “EmployeeServlet.java” in the package “com.theopentutorials.servlets” and copy the following code.

Here, instead of doing JNDI lookup for the JDBC datasource, we use resource injection using @Resource annotaion and store it in DataSource object.

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.annotation.Resource;
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;

	@Resource(name = "jdbc/testDB")
	DataSource ds;

	public EmployeeServlet() {
		super();
	}

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		try {
			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>");
			out.print("<table border=\"1\" cellspacing=10 cellpadding=5>");
			out.print("<tr><th>Employee ID</th>");
			out.print("<th>Employee Name</th>");
			out.print("<th>Salary</th>");
			out.print("<th>Department</th></tr>");

			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();
		}
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	}
}
  • When the application is deployed in the tomcat container, it looks up the JDBC resource and injects into the Servlet.

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>

Output

Deploy the project in Server and start/restart the server.

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

Folder Structure

The complete folder structure of this example is shown below.

Program control flow

  • When the application is deployed in the container, it looks up JDBC resource (jdbc/testDB) in global JNDI namespace and injects into Servlet’s DataSource object (ds).
  • 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, creates Connection from injected Datasource object 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

Tags: , , , , , , , , , ,

  • Swaroop

    Hi,

    Thank you for the wonderful tutorial.
    The previous example as mentioned above runs fine.
    But where as the above example is giving me the following error.
    Am using Oracle Database.I believe there is some problem while injecting the resource.Do we need to re-initialize the context again.Please suggest.

    org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class ‘oracle.jdbc.driver.OracleDriver’
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1136)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
    at com.tutorial.dsinjection.EmployeeServlet2.doGet(EmployeeServlet2.java:30)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
    at java.lang.Thread.run(Thread.java:662)

    • http://theopentutorials.com Praveen Macherla

      Include oracle jdbc jar file in web-inf/lib folder.

      • Swaroop

        Hi,

        Thank you for the response.
        I have included the ojdbc jar in WEB-INF/lib.
        The only change is I have renamed the file as EmployeeServlet2(in web.xml also).
        The first example is running fine with ojdbc jar included.
        Please suggest.

  • Swaroop

    Hi,

    The example worked by copying the ojdbc jars in TOMCAT/WEB-INF/lib.

  • Frank

    HI,
    I would suggest to close the Connection in a finally Block

  • Pingback: Java | ravisankar1