The JavaTM Web Services Tutorial
Home
TOC
PREV TOP NEXT

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 17-5 SQL Tags 
Area
Function
Tags
TLD
Prefix
Database

<driver>
/jstl-sql
sql
SQL
<query>
<param> <transaction>
<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 context attribute bookDS set in the context listener.

<c:set var="bid" value="${param.Add}"/>	
<sql:query var="books" dataSource="${application.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 complete information about these interfaces, see the API documentation for the javax.servlet.jsp.jstl.sql package.

public interface Result	
   Map[] getRows();	
   Object[][] getRowsByIndex();	
   ColumnMetaData[] getMetaData();	
   int getSize();	
   boolean isLimitedByMaxRows();	
	
public interface ColumnMetaData
 

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. Thus, 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.

Thus, 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.first_name}"/> <c:out	
   value="${book.surname}"/></em>&nbsp;&nbsp;	
   (<c:out value="${book.yr}"/>)<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 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 in JavaServer Pages Technology and Custom Tags in JSP Pages that use a book database JavaBeans component.

<sql:query var="books" dataSource="${application.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
PREV TOP NEXT