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.
| 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 “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.
- In the
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
