Home | Download | PDF | API | FAQ | Search | Feedback | History

> ## SQL Tag Library

> The JSTL SQL tags for accessing databases listed in Table 6-7 are designed for quick prototyping and simple applications. For production applications, database operations are normally encapsulated in JavaBeans components.

> Table 6-7 SQL Tags
> Area | Function | Tags | Prefix
> --- | --- | --- | ---
> Database |  | `setDataSource` | `sql`
> SQL | `query` `dateParam param transaction` `update dateParam` `param`

> The `setDataSource` tag allows 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 of 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 performs an SQL query that returns a result set. For parameterized SQL queries, you use a nested `param` tag inside the `query` tag.

> In `bookcatalog.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 is passed to the `param` tag.

>     <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 `bookreceipt.jsp` page uses both tags to update the database inventory for each purchase. Because 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="${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>${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"/>
>           ${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 JSTL`` packages.

> 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 an 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 JSP 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>${book.title}</h2>
>       &nbsp;<fmt:message key="By"/> <em>${book.firstname}
>       ${book.surname}</em>&nbsp;&nbsp;
>       (${book.year})<br> &nbsp; <br>
>       <h4><fmt:message key="Critics"/></h4>
>       <blockquote>${book.description}</blockquote>
>       <h4><fmt:message key="ItemPrice"/>:
>       <fmt:formatNumber value="${book.price}" type="currency"/>
>       </h4>
>     </c:forEach>

> The following excerpt from `bookcatalog.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. Because the `bid` and `bookRow` objects are later used by tags that use scripting language expressions to set attribute values and by 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. Then the book is added to the shopping cart.

> You might want to compare this version of `bookcatalog.jsp` to the versions in Chapter 4and Chapter 7 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.Book"
>         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="${bookRow[4])}" />
>         <jsp:setProperty name="addedBook" property="year"
>           value="${bookRow[6]}" />
>         <jsp:setProperty name="addedBook"
>           property="description"
>           value="${bookRow[7]}" />
>         <jsp:setProperty name="addedBook" property="inventory"
>           value="${bookRow[8]}" />
>       </jsp:useBean>
>       <% cart.add(bid, addedBook); %>
>       ...
>     </c:forEach>