[ Team LiB ] Previous Section Next Section

Recipe 23.7 Using A SQL JSTL Tag with a DataSource Configuration


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


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


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





<!-- 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" %>

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

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

<table border="1">

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


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



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:


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=

    <c:out value="${col}"/>


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

See Also

Chapter 21 on working with databases; the Jakarta Project's Taglibs site: http://jakarta.apache.org/taglibs/index.html; the Sun Microsystems JSTL information page: http://java.sun.com/products/jsp/jstl/; Recipe 23.3 on using the core tags; Recipe 23.5 on using the XML Transform tags; Recipe 23.6 on using the formatting tags; Recipe 23.8 on using a SQL JSTL tag without a DataSource configuration; Recipe 23.9-Recipe 23.14 on using the EL to access scoped variables, cookies, and JavaBean properties.

    [ Team LiB ] Previous Section Next Section