Home TOC

SQL Tags

The JSTL SQL tags are designed for quick prototyping and simple applications.

Table 5 SQL Tags 

Area	Function	Tags	TLD	Prefix
Database		<driver>	/jstl-sql	sql
	SQL	<transaction> <query> <update> <param>

The driver tag is provided to allow you to set driver information for the database. However, an application's business logic (for example, via a life cycle event listener or controller servlet) should normally be used to create a data source and make it available to the rest of the application. This is the approach followed by the Duke's Bookstore, which creates a data source in listeners.ContextListener:

public void contextInitialized(ServletContextEvent event) {	
   context = event.getServletContext();	
   try  { 	
      InitialContext ic = new InitialContext();	
      Context envCtx = (Context) ic.lookup("java:comp/env");	
      DataSource ds = (DataSource)	
         envCtx.lookup("jdbc/BookDB"); 	
      context.setAttribute("bookDS", ds);
 

The query tag is used to perform an SQL query that returns a result set. For parameterized SQL queries, you use a nested param tag inside the query tag. The update tag is used to update a database row. The transaction tag is used to perform an atomic update.

In catalog.jsp, the value of the Add request parameter determines which book information should be retrieved from in the database. This parameter is saved as the attribute name bid and passed to the param tag. Notice that the query tag obtains its data source from the attribute bookDS set in the context listener.

<c:set var="bid" value="$param:Add"/>	
<sql:query var="books" dataSource="$bookDS">	
   select * from PUBLIC.books where id = ?	
   <sql:param value="$bid" />	
</sql:query>
 

query Tag Result-Related Interfaces

The following interfaces are used to retrieve information from objects returned from a query tag. For each interface we list the methods discussed in this section. For complete information about these interfaces, see the API documentation for the javax.servlet.jsp.jstl.sql package.

public interface Result	
   public Row[] getRows()	
public interface ResultMetaData	
public interface Row	
   public Column[] getColumns()	
public interface ColumnMetaData	
public interface Column	
   public String toString()	
   public Object getValue()
 

The var attribute set by a query is of type Result. The getRows method returns a collection of rows that can be provided to an iterator tag. The SPEL expression language converts the syntax result.rows to a call to result.getRows. The expression $books.rows in the following example returns a collection of rows.

When you provide a collection of rows to an iterator, the var attribute set by the iterator is of type Row. To retrieve information from a row, use the getColumns method of Row to get its columns, and the getValue method of Column to get the value. The SPEL expression language converts the syntax row.columns[i] to a call to row.getColumns()[i] which returns a Column. When you pass a column to c:expr tag, it outputs a String representation of the value of the column.

Thus, the Duke's Bookstore page bookdetails.jsp retrieves the columns from the book row as follows.

<c:forEach var="book" begin="0" items="$books.rows">	
   <h2><c:expr value="$book.columns[3]"/></h2>	
   &nbsp;<fmt:message key="By"/> <em><c:expr	
   value="$book.columns[2]"/> <c:expr	
   value="$book.columns[1]"/></em>&nbsp;&nbsp;	
   (<c:expr value="$book.columns[5]"/>)<br> &nbsp; <br>	
   <h4><fmt:message key="Critics"/></h4>	
   <blockquote><c:expr value="$book.columns[6]"/>	
   </blockquote>	
   <h4><fmt:message key="ItemPrice"/>: 	
   <fmt:formatNumber value="$book.columns[4]"	
      type="currency"/> 	
   </h4>	
</c:forEach>
 

The following excerpt from catalog.jsp uses the Row and Column interfaces to retrieve values from the columns of a book row using scripting language expressions. First the book row that matches a request parameter (bid) is retrieved from the database. Since the bid and bookRow objects are later used by tags that use scripting language expressions to set attribute values and a scriptlet that adds a book to the shopping cart, both objects are declared as scripting variables using the jsp:useBean tag. The page then creates a bean that describes the book and scripting language expressions are used to set the book properties from column values. Finally the book is added to the shopping cart.

You might want to compare this version of catalog.jsp to the versions that use a book database JavaBeans component.

<sql:query var="books" dataSource="$bookDS">	
   select * from PUBLIC.books where id = ?	
   <sql:param value="$bid" />	
</sql:query>	
<c:forEach var="bookRow" begin="0" items="$books.rows"> 	
   <jsp:useBean id="bid"  type="java.lang.String" />	
   <jsp:useBean id="bookRow" 	
      type="javax.servlet.jsp.jstl.sql.Row" />	
   <jsp:useBean id="addedBook" class="database.BookDetails"	
      scope="page" />	
      <jsp:setProperty name="addedBook" property="bookId"	
         value="<%=bookRow.get(0).toString()%>" />	
      <jsp:setProperty name="addedBook" property="surname"	
         value="<%=bookRow.get(1).toString()%>" />	
      <jsp:setProperty name="addedBook" property="firstName"	
         value="<%=bookRow.get(2).toString()%>" />	
      <jsp:setProperty name="addedBook" property="title"	
         value="<%=bookRow.get(3).toString()%>" />	
      <jsp:setProperty name="addedBook" property="price"	
         value="<%=((Double)bookRow.get(4).getValue()).	
         floatValue()%>" />	
      <jsp:setProperty name="addedBook" property="year"	
         value="<%=(Integer)bookRow.get(5).getValue()%>" />	
      <jsp:setProperty name="addedBook" 	
         property="description" 	
         value="<%=bookRow.get(6).toString()%>" />	
      <jsp:setProperty name="addedBook" property="inventory"	
         value="<%=(Integer)bookRow.get(7).getValue()%>" />	
   </jsp:useBean>	
   <% cart.add(bid, addedBook); %>	
   ...	
</c:forEach>
 

Home TOC