Creating a JNDI DBCP Connection Pool

Copy the source code below into a file called plain.jsp in your application's main folder.

Your application directory structure will look something like this:

Source Code (Copy and Paste): <%-- /* * @(#)plain.jsp (Metawerx customer example classes) * * Copyright (c) 1998-2007 Metawerx. All Rights Reserved. * * This software is the confidential and proprietary information of Metawerx * ("Confidential Information"). You shall not disclose such Confidential * Information and shall use it only in accordance with the terms of the * license agreement you entered into with Metawerx. * * METAWERX MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY * OF THE SOFTWARE OR THE SOURCE CODE, EITHER EXPRESS OR IMPLIED, INCLUDING * BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR * A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. METAWERX SHALL NOT BE LIABLE * FOR ANY DAMAGES SUFFERED BY ANY PARTY AS A RESULT OF USING, MODIFYING OR * DISTRIBUTING THIS SOFTWARE AND DOCUMENTATION OR ITS DERIVATIVES * * * Synopsis * ===================================================================================== * This code demonstrates the use of a JNDI DBCP Connection Pool entry * to run and output data from an SQL query using direct JDBC calls. * * Requirements * ===================================================================================== * Using JNDI, the JDBC driver must be available in Tomcat's CLASSPATH. * Making it part of the application's CLASSPATH is not sufficient. * * Complete file structure for Tomcat for this sample * ===================================================================================== JAKARTA-WEBAPPS JAKARTA-WEBAPPS/JNDIApp JAKARTA-WEBAPPS/JNDIApp/plain.jsp JAKARTA-WEBAPPS/JNDIApp/WEB-INF JAKARTA-WEBAPPS/JNDIApp/WEB-INF/web.xml JAKARTA-WEBAPPS/JNDIApp/META-INF JAKARTA-WEBAPPS/JNDIApp/META-INF/context.xml * * Complete, minimal web.xml file for this sample * ===================================================================================== <?xml version="1.0" encoding="UTF-8"?> <web-app 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" version="2.4"> <!-- Note there are no resource-reference tags required here, because they come from context.xml automatically. --> <!-- This is a minimal web.xml - it doesn't even have a description for the application. Feel free to add whatever else you need. --> </web-app> * History * ===================================================================================== * 2006/04/19 9:39 Neale Rudd * Added this example * */ --%> <%@ page pageEncoding="UTF-8" contentType="text/html" %> <%@ page import="javax.naming.*" %> <%@ page import="javax.sql.*" %> <%@ page import="java.sql.*" %> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <html> <head> <title>JNDI DBCP Test</title> </head> <body> <h1>JNDI DBCP Test Page</h1> <br/>Executing the query ... <br/> <% String selectQry = "SELECT * FROM users;"; Context ctx = new InitialContext(); if(ctx != null) { DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mydatabase"); if (ds != null) { Statement s = null; Connection c = null; ResultSet r = null; ResultSetMetaData rm = null; try { c = ds.getConnection(); if(c != null) { // Execute SQL out.println("<P><B>Executing query...["+selectQry+"]</B><P><PRE>"); // Create a statement and execute the query on it s = c.createStatement(); s.execute(selectQry); // Get result set r = s.getResultSet(); rm = r.getMetaData(); // Display data int count = rm.getColumnCount(); // Output each row from the query while (r.next()) { for(int i = 1; i <= rm.getColumnCount(); i++) { // Output each column, and it's value out.println("<BR>[Column " + i + " - " + rm.getColumnName(i) + "] " + r.getString(rm.getColumnName(i))); } out.println("<P>"); } // Clean up s.close(); c.close(); } else { out.println("ERROR: No connection"); } } catch (Exception e) { out.println("Errors occurred: " + e.toString()); throw e; } finally { // Always make sure result sets and statements are closed, // and the connection is returned to the pool if(r != null) { try { r.close(); } catch(Exception e) {} r = null; } if(s != null) { try { s.close(); } catch(Exception e) {} s = null; } if(c != null) { try { c.close(); } catch(Exception e) {} c = null; } } // Done out.println("</PRE><P><HR><B>DONE</B>"); } else { out.println("ERROR: No datasource"); } } else { out.println("ERROR: No context"); } %> </body> </html>