Home TOC Index

SQL Tags

The JSTL SQL tags are designed for quick prototyping and simple applications. For production applications, database operations are normally encapsulated in JavaBeans components.

Table 16-7 SQL Tags 

Area	Function	Tags	TLD	Prefix
Database		setDataSource	/jstl-sql	sql
	SQL	query   dateParam  paramtransaction update  dateParam   param

The setDataSource tag is provided to allow you to set data source information for the database. You can provide a JNDI name or DriverManager parameters to set the data source information. All the Duke's Bookstore pages that have more than one SQL tag use the following statement to set the data source:

<sql:setDataSource dataSource="jdbc/BookDB" />
 

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.

In catalog.jsp, the value of the Add request parameter determines which book information should be retrieved from 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 context attribute bookDS set in the context listener.

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

The update tag is used to update a database row. The transaction tag is used to perform a series of SQL statements atomically.

The JSP page receipt.jsp page uses both tags to update the database inventory for each purchase. Since a shopping cart can contain more than one book, the transaction tag is used to wrap multiple queries and updates. First the page establishes that there is sufficient inventory, then the updates are performed.

<c:set var="sufficientInventory" value="true" />
<sql:transaction>
  <c:forEach var="item" items="${sessionScope.cart.items}">
    <c:set var="book" value="${item.item}" />
    <c:set var="bookId" value="${book.bookId}" />

    <sql:query var="books" 
      sql="select * from PUBLIC.books where id = ?" >
      <sql:param value="${bookId}" />
    </sql:query>
    <jsp:useBean id="inventory"
      class="database.BookInventory" />
    <c:forEach var="bookRow" begin="0"
      items="${books.rowsByIndex}">
      <jsp:useBean id="bookRow"  type="java.lang.Object[]" />
      <jsp:setProperty name="inventory" property="quantity"
        value="<%=(Integer)bookRow[7]%>" />

      <c:if test="${item.quantity > inventory.quantity}">
        <c:set var="sufficientInventory" value="false" />
        <h3><font color="red" size="+2">
        <fmt:message key="OrderError"/> 
        There is insufficient inventory for 
        <i><c:out value="${bookRow[3]}"/></i>.</font></h3>
      </c:if>
    </c:forEach>
  </c:forEach>

  <c:if test="${sufficientInventory == 'true'}" />
    <c:forEach var="item" items="${sessionScope.cart.items}">
      <c:set var="book" value="${item.item}" />
      <c:set var="bookId" value="${book.bookId}" />

      <sql:query var="books" 
        sql="select * from PUBLIC.books where id = ?" >
        <sql:param value="${bookId}" />
      </sql:query>

      <c:forEach var="bookRow" begin="0"
        items="${books.rows}">            
        <sql:update var="books" sql="update PUBLIC.books set
          inventory = inventory - ? where id = ?" >
          <sql:param value="${item.quantity}" />
          <sql:param value="${bookId}" />
        </sql:update>
      </c:forEach>
    </c:forEach>
    <h3><fmt:message key="ThankYou"/>
      <c:out value="${param.cardname}" />.</h3><br>  
  </c:if>
</sql:transaction>
 

query Tag Result Interface

The Result interface is used to retrieve information from objects returned from a query tag.

public interface Result
  public String[] getColumnNames();
  public int getRowCount()
  public Map[] getRows();
  public Object[][] getRowsByIndex();
  public boolean isLimitedByMaxRows();
 

For complete information about this interface, see the API documentation for the javax.servlet.jsp.jstl.sql package.

The var attribute set by a query tag is of type Result. The getRows method returns an array of maps that can be supplied to the items attribute of a forEach tag. The JSTL expression language converts the syntax ${result.rows} to a call to result.getRows. The expression ${books.rows} in the following example returns an array of maps.

When you provide a array of maps to the forEach tag, the var attribute set by the tag is of type Map. To retrieve information from a row, use the get("colname") method to get a column value. The JSTL expression language converts the syntax ${map.colname} to a call to map.get("colname"). For example, the expression ${book.title} returns the value of the title entry of a book map.

The Duke's Bookstore page bookdetails.jsp retrieves the column values from the book map as follows.

<c:forEach var="book" begin="0" items="${books.rows}">
  <h2><c:out value="${book.title}"/></h2>
  &nbsp;<fmt:message key="By"/> <em><c:out
  value="${book.firstname}"/> <c:out
  value="${book.surname}"/></em>&nbsp;&nbsp;
  (<c:out value="${book.year}"/>)<br> &nbsp; <br>
  <h4><fmt:message key="Critics"/></h4>
  <blockquote><c:out value="${book.description}"/>
  </blockquote>
  <h4><fmt:message key="ItemPrice"/>: 
  <fmt:formatNumber value="${book.price}" type="currency"/> 
  </h4>
</c:forEach>
 

The following excerpt from catalog.jsp uses the Row interface 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 creates a bean that describes the book and scripting language expressions are used to set the book properties from book row column values. Finally the book is added to the shopping cart.

You might want to compare this version of catalog.jsp to the versions in JavaServer Pages Technology and Custom Tags in JSP Pages that use a book database JavaBeans component.

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

Home TOC Index

This tutorial contains information on the 1.0 version of the Java Web Services Developer Pack.

All of the material in The Java Web Services Tutorial is copyright-protected and may not be published in other works without express written permission from Sun Microsystems.