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 --
