Recipe 23.7 Using A SQL JSTL Tag with a DataSource Configuration

Problem

You want to interact with a relational database by configuring the javax.sql.DataSource in the deployment descriptor.

Solution

Add a context-param element to web.xml, creating a parameter named javax.servlet.jsp.jstl.sql.dataSource that connects with a particular database.

Discussion

The JSTL SQL tag library allows a JSP to interact with a database using custom tags. Java Database Connectivity (JDBC) and the classes in the javax.sql package allow this technology to work. The first step in this recipe is to configure the DataSource that the tags will use to connect with a database.

A DataSource is a factory for java.sql.Connection objects, which represent a socket connection with a particular database server such as MySQL or Oracle.

Example 23-7 creates a context-param element in web.xml. For the JSTL's SQL tags to automatically receive their Connections from this setting, the param name must be javax.servlet.jsp.jstl.sql.dataSource. The param value comprises comma-separated phrases:

[JDBC URL],[Driver name],[user],[password]

Developers commonly derive the JDBC URL and driver name from database vendor documentation (and often from mailing lists, because debugging backend database connections with JDBC can be tricky!). The code I show here contains an example of a JDBC URL for Oracle8i Personal Edition.

Example 23-7. An example web.xml configuration for a javax.sql.Datasource

<!-- top of web.xml file -->

<context-param>

    <param-name>javax.servlet.jsp.jstl.sql.dataSource</param-name>

        <param-value>jdbc:oracle:thin:@192.168.0.2:1521:ORCL,
      oracle.jdbc.driver.OracleDriver,scott,tiger</param-value>

</context-param>

<!-- rest of web.xml file -->

The JSTL software uses these values to generate a DataSource for its SQL tags. The advantage of using an external setting for the DataSource is that to switch databases, you can change the value of the context-param to the configuration representing the new database without touching the JSP code. The JSP deals transparently with the SQL tags and DataSource object.

Now on to the JSP. Remember that the SQL tags (the ones using the "sql" prefix) use the DataSource that we just set with the context-param element in web.xml.

Web applications always have a web.xml file in the WEB-INF directory. See Chapter 1 if you need a further explanation.

The taglib directives at the top of Example 23-8 are required if you want to use the JSTL 1.0 core and SQL libraries.

Example 23-8. A JSP uses JSTL sql tags to display database information

<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>

<html>
<head><title>Database Query</title></head>
<body>
<h2>Querying a database from a JSTL tag</h2>

<sql:query var="athletes">
SELECT * FROM athlete
</sql:query>

<table border="1">

<c:forEach var="row" items="${athletes.rows}">

<tr>
<th>user_id</th>
<th>name</th>
<th>birthdate</th>
<th>passwrd</th>
<th>gender</th></tr>

<tr>
<td><c:out value="${row.user_id}"/></td>
<td><c:out value="${row.name}"/></td>
<td><c:out value="${row.birthdate}"/></td>
<td><c:out value="${row.passwrd}"/></td>
<td><c:out value="${row.gender}"/></td>
</tr>

</c:forEach>
</table>

</body>
</html>

The sql:query tag uses its nested content to send the SQL SELECT statement "select * from athlete" to a database. The database connection derives from the DataSource you have already configured. The statement is designed to "select all rows from the table named `athlete.'" The sql:query tag saves the result set in a javax.servlet.jsp.jstl.sql.Result object, in a variable named athletes.

Result objects are converted from java.sql.ResultSet objects. Result objects have methods (such as getRows( )) that are designed to interact with the JSTL SQL tags.

The code:

${athletes.rows}

is an EL phrase that calls the Result object's getRows( ) method. This method returns a java.util.SortedMap[] type or an array of SortedMaps. Example 23-8 uses the c:forEach tag to iterate over this array and create an HTML table row out of each of the returned database rows.

You can use this form of code to display the column names of a result set (`athletes' is the variable storing the result set):

<c:forEach var="col" items= "${athletes.columnNames}"> <c:out value="${col}"/> </c:forEach>

The next recipe shows how a JSP accomplishes this same task without a context-param configuring the DataSource.

Try to stick with the strategy of setting the DataSource in web.xml, because it represents a better software design than cluttering up a JSP with a DataSource configuration.

Figure 23-5 shows the JSP displaying the database row information in a web browser.

Figure 23-5. Displaying database information in a JSP