Connecting Tomcat 7 Web Application to Database Using JNDI

Several weeks ago, I looked at some decompiled Java codes from a fairly small web application that basically records cheque numbers and generates reports. To my surprise, it was written in dreadful codes full of clutter and signs of haste! I almost cried. Just imagine the following code littered all around the application and entangled by a lot of package-level instance variables. Not to mention the SQL-statements-in-Servlets type of design pattern.

Class.forName("com.mysql.jdbc.Driver");

1	Class.forName("com.mysql.jdbc.Driver");

Thus, this blog.

This is how I do things.

Software Applications used

Tomcat 7.0.23
JDK1.6
Eclipse
HSQL 2.2.6

Step 1 – Download and set up HSQL Database

Please see Tomcat 7 and Up: JDBC Realm on how to create a database and start the HSQL server. For this blog, we will use

runServer.bat -database.0 file:tc7db -dbname.0 tc7db

1	runServer.bat -database.0 file:tc7db -dbname.0 tc7db

Create table and insert sample data

create table tc7table(name varchar(200));
insert into tc7table values('a');
insert into tc7table values('b');
insert into tc7table values('karl');

create table tc7table(name varchar(200));

insert into tc7table values('a');

insert into tc7table values('b');

insert into tc7table values('karl');

Test Data

Step 2– Create an Eclipse project and Servlet that uses our database.

The workspace.

Java project in Eclipse

The Servlet code

WARNING: This is just for demonstration purposes. Do not put SQLs in your Servlets.

package com.karlsangabriel.tc7db;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
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 Servlet1 extends HttpServlet {
  @Override
  protected void doGet(HttpServletRequest req,
    HttpServletResponse resp)
      throws ServletException, IOException {
    PrintWriter out = resp.getWriter();

    try {
      Connection co = this.getConnection();
      Statement stmt = co.createStatement();
      ResultSet rs
        = stmt.executeQuery("select name from tc7table");

      while(rs.next()) {
        out.println("");
        out.println(rs.getString(1));
      }
    } catch (Exception e) { e.printStackTrace(); }
 }

 private Connection getConnection() {
   Connection conn = null;
   try {
     Context initContext = new InitialContext();
     Context envContext = (Context)initContext.lookup("java:/comp/env");
     DataSource ds = (DataSource)envContext.lookup("jdbc/TC7DB");
     conn = ds.getConnection();
   } catch(Exception e) { e.printStackTrace();}
 return conn;
 }
}

package com.karlsangabriel.tc7db;

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import javax.naming.Context;

import javax.naming.InitialContext;

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 Servlet1 extends HttpServlet {

@Override

protected void doGet(HttpServletRequest req,

HttpServletResponse resp)

throws ServletException, IOException {

PrintWriter out = resp.getWriter();

try {

Connection co = this.getConnection();

Statement stmt = co.createStatement();

ResultSet rs

= stmt.executeQuery("select name from tc7table");

while(rs.next()) {

out.println("");

out.println(rs.getString(1));

}

} catch (Exception e) { e.printStackTrace(); }

}

private Connection getConnection() {

Connection conn = null;

try {

Context initContext = new InitialContext();

Context envContext = (Context)initContext.lookup("java:/comp/env");

DataSource ds = (DataSource)envContext.lookup("jdbc/TC7DB");

conn = ds.getConnection();

} catch(Exception e) { e.printStackTrace();}

return conn;

}

The web.xml

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
 http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
 version="3.0"
 metadata-complete="true">
 <servlet>
 <servlet-name>Servlet1</servlet-name>
 <servlet-class>com.karlsangabriel.tc7db.Servlet1</servlet-class>
 </servlet>

<servlet-mapping>
 <servlet-name>Servlet1</servlet-name>
 <url-pattern>/*</url-pattern>
 </servlet-mapping>

 <resource-ref>
 <res-ref-name>jdbc/TC7DB</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
 </resource-ref>
</web-app>

<?xml version="1.0" encoding="ISO-8859-1"?>

<web-app xmlns="http://java.sun.com/xml/ns/javaee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/javaee

http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"

version="3.0"

metadata-complete="true">

<servlet-name>Servlet1</servlet-name>

<servlet-class>com.karlsangabriel.tc7db.Servlet1</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>Servlet1</servlet-name>

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

</servlet-mapping>

<resource-ref>

<res-ref-name>jdbc/TC7DB</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>

</web-app>

The web applicaiton directory structure and the required HSQL jar file.

The Web App dir and required HSQL jar file

Step 3 – Configure Tomcat7

a) Add the following between the context tags in <TOMCAT_HOME>/conf/context.xml

<?xml version='1.0' encoding='utf-8'?>

<Context>
...

<Resource name="jdbc/TC7DB" auth="Container" type="javax.sql.DataSource"
 maxActive="10" maxIdle="30" maxWait="10000"
 username="sa" password="" driverClassName="org.hsqldb.jdbcDriver"
 url="jdbc:hsqldb:hsql://localhost:9001/tc7db"/>
</Context>