[ Team LiB ] Previous Section Next Section

JSTL SQL Tag Library

The SQL tag library is one of the most powerful tag libraries in JSTL, because it allows you to perform database queries and updates from simple tags. You can use these tags in conjunction with the core tag library to iterate through a resultset and display it.

The <sql:setDataSource> Tag

Before you can perform any database operations, you must declare a data source, which is the repository for data that you want to use (specified by the var attribute and the optional scope attribute). You may optionally declare a scoped variable that will be used to export a reference to the data source. If you don't specify a variable, the data source is stored in a default data source variable. You can specify the scope of the default variable via the scope attribute. For example, if you define a data source with a scope of session and don't store it in a variable, the data source will be the default data source for all SQL tag operations for that session but not for other sessions.

There are two ways to specify the data source: You can use either a preconfigured Java Numbering and Directory Interface (JNDI) data source name specified by the dataSource attribute or a JDBC URL and optional driver name, username, and password. The attributes for these are url, driver, user, and password.

After you create the data source, you can perform other database operations.

Using Data Sources with JNDI

graphics/didyouknow_icon.gif

Using JNDI, you can create a data source beforehand and associate it with a name stored in a directory. Data sources are often preconfigured within the application server, when a Web application is deployed. To use a data source with JNDI, you simply look up the name and obtain a reference to the data source. The details of this are beyond the scope of this hour, but it's worth further investigation.


The <sql:query> Tag

The <sql:query> tag executes a query and stores the resulting values in a variable specified by the var attribute and an optional scope attribute. If there is no default data source set, you must specify a data source with the dataSource attribute. The value of the dataSource attribute must be an EL expression that evaluates to a DataSource object. If you have previously stored a data source in a variable, don't forget to surround the variable name with ${}.

The sql attribute specifies the SQL query you want to perform. Instead of the sql attribute, you can specify the query in the body of the <sql:query> tag. You can also specify a maximum number of rows to receive with the maxRows attribute and the first row to return with the startRow attribute.

The <sql:query> parameter can work with parameters. To pass parameters to the query, use the <sql:param> or <sql:dateParam> tags in the body of the <sql:query> tag. You can specify a parameter for the <sql:param> via the value attribute or in the body of the <sql:param> tag. Similarly, you can specify the date value for the <sql:dateParam> in either the value attribute or the body of the tag. You can also specify the kind of date parameter you want: date, time, or timestamp (timestamp is the default).

The result of the query is an object that implements the Result interface. The two most important methods in the Result interface are getRows (which returns an array of SortedMap objects where each map represents a row and the keys of the map are the column names) and getRowsByIndex (which returns an array or arrays of objects). You can also retrieve an array of column names with getColumnNames, the number of rows with getRowCount, and an indication of whether the query was limited by a maximum row count with isLimitedByMaxRows.

Here is an example.

Listing 18.2 shows a JSP that queries a Person table.

Listing 18.2 Source Code for ShowPeopleJSTL.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>
<sql:setDataSource driver="com.ibm.db2j.jdbc.DB2jDriver"
    url="jdbc:db2j:PeopleDB" var="ds" />

<sql:query sql="select name, age from People" var="results"
    dataSource="${ds}"/>
<html>
<body>
<table border="4">
<tr><th>Name</th><th>Age</th></tr>
<c:forEach var="row" items="${results.rows}">
    <tr><td><c:out value="${row.name}"/></td>
        <td><c:out value="${row.age}"/></td></tr>
</c:forEach>
</table>
</body>
</html>

Compare Listing 18.1 with Listing 18.2. They basically do the same thing—print all rows from the Person table using JDBC and a JSP. The primary difference is that Listing 18.2 shows the use of the JSTL tag libraries. Notice how much simpler and shorter Listing 18.2 is than Listing 18.1.

The <sql:update> Tag

The <sql:update> tag executes an INSERT, UPDATE, or DELETE statement, optionally returning the number of rows updated in a variable specified by the var attribute and optional scope attribute. If there is no default data source, you must specify the data source in the dataSource attribute. The actual SQL statement is specified either by the sql attribute or in the body of the <sql:update> tag. As with the <sql:query> tag, you can use the <sql:param> and <sql:dateParam> tags to substitute parameters in the SQL statement.

The <sql:transaction> Tag

Sometimes you need to execute several statements as part of a single database transaction. The <sql:transaction> tag groups any tags in its body content in a single database transaction. If there is no default data source, you must specify the data source in the dataSource attribute and not within any SQL tags contained within the <sql:transaction> tag.

You can also specify the transaction isolation level with the isolation attribute, which must be one of the following: read_committed, read_uncommitted, repeatable_read, or serializable.

    [ Team LiB ] Previous Section Next Section