Metawerx Java Hosting Small Logo

Accessing a JNDI DataSource with JSTL from JSP

This code demonstrates the use of a JNDI DBCP Connection Pool defined in context.xml, to execute an SQL query and output the data using JSTL in a JSP page.

In this case, the query is select * from products. The results will be displayed as a table with bolded column headings.

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.
  • Modify the query in test.jsp if necessary. The current query is select * from products
  • Install test.jsp, 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 code requires the jstl.jar and standard.jar files, as it uses JSTL (Java Standard Templates Library). Ensure these jars are present in your WEB-INF/lib folder or Tomcat CLASSPATH.

Source Code - test.jsp

<%page pageEncoding="UTF-8" contentType="text/html" %>

<%-- These libraries are required for the <c> and <sql> tags --%>
<%taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> 
<%taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %> 
<%
/*
 * @(#)test.jsp (Metawerx example classes)
 *
 * Copyright (c) 1998-2006 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 JSTL.
 *
 * Requirements
 * =====================================================================================
 * This code requires the jstl.jar and standard.jar files to use JSTL.
 * Using JNDI, the JDBC driver must be available in Tomcat's CLASSPATH.
   Making it part of the application's CLASSPATH is not sufficient.
   This is because [JNDI] datasources are created before the application context is loaded.
 * The JNDI datasource must be defined as jdbc/mydatabase in META-INF/context.xml
 *
 * Complete file structure for Tomcat for this sample
 * =====================================================================================

    JAKARTA-WEBAPPS
    JAKARTA-WEBAPPS/JNDIApp
    JAKARTA-WEBAPPS/JNDIApp/test.jsp
    JAKARTA-WEBAPPS/JNDIApp/WEB-INF
    JAKARTA-WEBAPPS/JNDIApp/WEB-INF/web.xml
    JAKARTA-WEBAPPS/JNDIApp/WEB-INF/lib
    JAKARTA-WEBAPPS/JNDIApp/WEB-INF/lib/jstl.jar
    JAKARTA-WEBAPPS/JNDIApp/WEB-INF/lib/standard.jar
    JAKARTA-WEBAPPS/JNDIApp/META-INF
    JAKARTA-WEBAPPS/JNDIApp/META-INF/context.xml
 
 * History
 * =====================================================================================
 * 2006/12/17 13:41  Neale Rudd
 *                   Ported code to Metawerx Wiki, along with many context.xml examples
 * 2006/04/19  9:22  Neale Rudd
 *                   Added this example
 *
 */
%>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<html>
<head>
    <title>JNDI DBCP Test Page for java</title>
</head>
<body>

    <h1>JNDI DBCP Test Page Java</h1>
    <br/>Executing the query ...
    <br/>

    <%-- Note: Enter a query that is valid for your database here --%>
    <sql:query var="result" dataSource="jdbc/mydatabase">
        SELECT * FROM products
    </sql:query>

    <table border="1">

        <%-- Output column names on a header row --%>
        <tr>
            <c:forEach var="columnName" items="${result.columnNames}">
                <th><c:out value="${columnName}"/></th>
            </c:forEach>
        </tr>

        <%-- Output each row of data --%>
        <c:forEach var="row" items="${result.rowsByIndex}">
            <tr>
                <%-- Output each column of data --%>
                <c:forEach var="col" items="${row}">
                    <td><c:out value="${col}"/></td>
                </c:forEach>
            </tr>
        </c:forEach>
    </table>
    
</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