AVAJAVA Web Tutorials

(Continued from page 2)

3.

Following the Eclipse/Tomcat project structure we followed in an earlier lesson, if we add our JDBCW driver (mysql-connector-java-5.0.4-bin.jar) to our web application's classpathW (in the lib directory in this example), we can now write our JavaSW code to access our data source. Our project structure is shown below.

However, note that, depending on how you are running things, you may need to add your JDBC driver to your Tomcat's common/lib/ directory so that TomcatSW knows about the JDBC driver.

You can now access your databaseW through your web application. The testJndiDataSource method of the TestJndiServlet shown below does this. Notice that the "jdbc/TestDB" resource ref maps to "java:comp/env/jdbc/TestDB". Obtaining the DataSource object from the InitialContext can either be performed in one lookup or two. The two lookup technique is shown below, commented out.

After obtaining the data source, the method obtains a connection to 'testdatabase' (jdbc/TestDB). Following this, the method creates a statement and then executes the SELECT statement. The results are returned in a result set, which is iterated over via its next() method. The id, first name, and last name are obtained for each row in the database table, and the results are stored in a StringBuffer, which is converted to a String.

testJndiDataSource() method from TestJndiServlet in the web application:

	public String testJndiDataSource() {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		try {
			InitialContext ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/TestDB");

			// This works too
			// Context envCtx = (Context) ctx.lookup("java:comp/env");
			// DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");

			conn = ds.getConnection();

			st = conn.createStatement();
			rs = st.executeQuery("SELECT * FROM employees");

			while (rs.next()) {
				String id = rs.getString("id");
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				sb.append("ID: " + id + ", First Name: " + firstName
						+ ", Last Name: " + lastName + "<br/>");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
			try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); }
			try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
		return sb.toString();
	}

TestJndiServlet calls its testJndiDataSource() method and outputs the results.

The full TestJndiServlet.java and project web.xmlW files are provided below. The server.xml context entry can be copied from Section 1 up above.

TestJndiServlet.java

package test;

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

public class TestJndiServlet extends javax.servlet.http.HttpServlet implements
		javax.servlet.Servlet {

	private static final long serialVersionUID = 1L;

	public TestJndiServlet() {
		super();
	}

	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		performTask(request, response);
	}

	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		performTask(request, response);
	}

	private void performTask(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out.println("TestJndiServlet says hi");
		out.println("<br/>");
		out.println(testJndiDataSource());
	}

	public String testJndiDataSource() {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		try {
			InitialContext ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/TestDB");

			// This works too
			// Context envCtx = (Context) ctx.lookup("java:comp/env");
			// DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");

			conn = ds.getConnection();

			st = conn.createStatement();
			rs = st.executeQuery("SELECT * FROM employees");

			while (rs.next()) {
				String id = rs.getString("id");
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				sb.append("ID: " + id + ", First Name: " + firstName
						+ ", Last Name: " + lastName + "<br/>");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
			try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); }
			try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
		return sb.toString();
	}

}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="tomcat-demo" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
	<servlet>
		<servlet-name>TestJndiServlet</servlet-name>
		<servlet-class>test.TestJndiServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>TestJndiServlet</servlet-name>
		<url-pattern>/testJndi</url-pattern>
	</servlet-mapping>
	<resource-ref>
		<description>Test DB</description>
		<res-ref-name>jdbc/TestDB</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>
</web-app>