Accessing a JNDI DataSource from Java
This is example Java code demonstrating how to access a JNDI DataSource,
to execute an SQL query and output the results.
The JNDI DataSource has been set up as a DBCP Connection Pool in context.xml.
The Java code in this example has been implemented as a Java Scriptlet in a JSP page for simplicity, so there is no manual compilation required.
In this case, the query is select * from products. The results will be displayed as sets of rows and columns, as follows:
[Column 1 - columnname] value
[Column 2 - columnname] value
[Column 3 - columnname] value
[Column 1 - columnname] value
[Column 2 - columnname] value
[Column 3 - columnname] value
How to use this script
- If necessary, add tables or data to your database so that you have something useful to display.
- Set your database connection details in the META-INF/context.xml file (driver name, JDBC URL, username, password). The JNDI datasource must be defined as jdbc/mydatabase in META-INF/context.xml. See context.xml or the end of this page for examples for many database types.
- Install this file, META-INF/context.xml and WEB-INF/web.xml in a folder under your webapps folder called database-test eg: /private-cgi-bin/tomcat/database-test or <jakarta>/webapps/database-test
- Access http://yoursite/database-test/test.jsp using a browser
Notes
- Because we are using JNDI, the JDBC driver must be available in Tomcat's CLASSPATH (or whichever J2EE container you are using). Making it part of the application's CLASSPATH (ie: WEB-INF/lib) is not sufficient. This is because JNDI datasources are created before the application context is loaded. Therefore, place the driver JAR file into <tomcat install dir>/common/lib.
- This file does not need to be changed, unless you want to alter the query that is executed.
JDBC hints:
- Always close connections so they are returned to the pool, and made available for other threads.
Source Code - test.jsp
<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
/*
* @(#)test.jsp (JDBC Customer Samples)
*
* Copyright (c) 1997-2006 Metawerx Pty Ltd. All Rights Reserved. www.metawerx.net
*
* 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 script takes the connection from a JNDI data source, which is defined
* in context.xml. It then selects all records from a table, then displays
* each row as a set of lines, in the following format:
*
* [Column 1 - columnname] value
* [Column 2 - columnname] value
* [Column 3 - columnname] value
*
* [Column 1 - columnname] value
* [Column 2 - columnname] value
* [Column 3 - columnname] value
*
* History
* =====================================================================================
* 2006/12/17 13:41 Neale Rudd
* Ported code to Metawerx Wiki, along with many context.xml examples
* 2006/04/19 11:20 Neale Rudd
* Added this example
*
*/
String selectQry = "select * from products;"; // change this line, eg: select * from products where price < 10
// Initialise and output top of page
out.println("<HTML><BODY BGCOLOR=\"#ddddff\">");
out.println("<HTML><TITLE>Metawerx JDBC JNDI DBCP Driver Test JSP v1.3</TITLE><BODY><FONT FACE=\"Verdana, Helvetica\" size=4>");
out.println("Metawerx JDBC JNDI DBCP Driver Test JSP v1.3");
// Get DataSource from JNDI (defined in context.xml file)
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mydatabase");
// Create connection/statement variables outside of try block
Connection c = null;
Statement s = null;
try {
// Get Connection and Statement from DataSource
c = ds.getConnection();
s = c.createStatement();
// Execute SQL
out.println("<P><B>Executing query...["+selectQry+"]</B><P><PRE>");
try {
// Create a statement and execute the query on it
String name;
s.execute(selectQry);
// Get result set
ResultSet r = s.getResultSet();
ResultSetMetaData rm = r.getMetaData(); // call before r.next() see note 4 above in JDBC hints
// Display data
int count = rm.getColumnCount();
while (r.next()) {
for(int i = 1; i <= rm.getColumnCount(); i++)
out.println("<BR />[Column " + i + " - " + rm.getColumnName(i) + "] " + r.getString(rm.getColumnName(i)));
out.println("<P />");
}
// Clean up
s.close();
c.close();
} catch (SQLException se) {
out.println("Errors occurred: " + se.toString());
} catch (Exception e) {
out.println("Errors occurred: " + e.toString());
}
} finally {
// Ensure connection is closed and returned to the pool, even if errors occur.
// This is *very* important if using a connection pool, because after all the
// connections are used, the application will hang on getConnection(), waiting
// for a connection to become available.
// Any errors from the following closes are just ignored. The main thing is
// that we have definitely closed the connection.
try { if(s != null) s.close(); } catch (Exception e) {}
try { if(c != null) c.close(); } catch (Exception e) {}
}
// Done
out.println("</PRE><P /><HR /><B>DONE</B></FONT></FONT></BODY></HTML>");
%>
|
Source Code - WEB-INF/web.xml
<?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>
Source Code - META-INF/context.xml
See one of the following, depending on your database:
Or change the following to suit:
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/mydatabase"
auth="Container"
type="javax.sql.DataSource"
username="YOUR_USERNAME"
password="YOUR_PASSWORD"
driverClassName="JDBC_DRIVER_CLASS"
url="JDBC_URL"
validationQuery="select 1"
maxActive="10"
maxIdle="2"/>
</Context>
Troubleshooting
- -- send Metawerx an email if the above code crashes on your local Tomcat for free help (even if you are not a metawerx customer) --
- -- We will either help you directly, or post your question on this page --
- -- We (or someone else viewing this page) will add details of how to fix the problem --
