Metawerx Java Hosting Small Logo

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 != nulls.close()catch (Exception e) {}
        try if(c != nullc.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 --

More Information

navigation
metawerx specific
search
Share
tools
help

referring pages

Share