SQL Tag Library

query Tag Result Interface

SQL Tag Library

The JSTL SQL tags for accessing databases listed inTable 7-7 are designedfor quick prototyping and simple applications. For production applications, database operations are normally encapsulatedin JavaBeans components.

Table 7-7 SQL Tags

query    dateParam    paramtransactionupdate    dateParam    param

ThesetDataSource tag allows you to set data source information for the database.You can provide a JNDI name orDriverManager parameters to set the datasource information. All of the Duke’s Bookstore pages that have more than oneSQL tag use the following statement to set the data source:

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

Thequery tag performs an SQL query that returns a result set. Forparameterized SQL queries, you use a nestedparam tag inside thequery tag.

Intut-install/javaeetutorial5/examples/web/bookstore4/web/books/bookcatalog.jsp, the value of theAdd request parameter determines which book informationshould be retrieved from the database. This parameter is saved as the attributenamebid and is passed to theparam tag.

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

Theupdate tag is used to update a database row. Thetransactiontag is used to perform a series of SQL statements atomically.

The JSP pagetut-install/javaeetutorial5/examples/web/bookstore4/web/books/bookreceipt.jsp uses both tags to update the database inventoryfor each purchase. Because a shopping cart can contain more than one book,thetransaction tag is used to wrap multiple queries and updates. First, thepage 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            />        <c:forEach var="bookRow" begin="0"            items="${books.rowsByIndex}">            <jsp:useBean  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

TheResult interface is used to retrieve information from objects returned from aquery 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 theJSTLpackages.

Thevar attribute set by aquery tag is of typeResult. ThegetRows method returns an array of maps that can be supplied to theitems attribute of aforEach tag. The JSTL expression language converts the syntax${result.rows}to a call toresult.getRows. The expression${books.rows} in the following example returns an arrayof maps.

When you provide an array of maps to theforEach tag, thevarattribute set by the tag is of typeMap. To retrieve information froma row, use theget("colname") method to get a column value. TheJSP expression language converts the syntax${map.colname} to a call tomap.get("colname"). Forexample, the expression${book.title} returns the value of the title entry ofa book map.

The Duke’s Bookstore pagetut-install/javaeetutorial5/examples/web/bookstore4/web/books/bookdetails.jsp retrieves the column values from thebookmap 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 fromtut-install/javaeetutorial5/examples/web/bookstore4/web/books/bookcatalog.jsp uses theRow interface to retrieve valuesfrom the columns of a book row using scripting language expressions. First, the bookrow that matches a request parameter (bid) is retrieved from the database. Becausethebid andbookRow objects are later used by tags that use scriptinglanguage expressions to set attribute values and by a scriptlet that adds abook to the shopping cart, both objects are declared as scripting variables usingthejsp:useBean tag. The page creates a bean that describes the book, andscripting language expressions are used to set the book properties from book rowcolumn values. Then the book is added to the shopping cart.

You might want to compare this version ofbookcatalog.jsp to the versions inChapter 5, JavaServer Pages Technology andChapter 8, 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  type="java.lang.String" />    <jsp:useBean type="java.lang.Object[]" />    <jsp:useBean        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>

Copyright © 2010, Oracle and/or its affiliates. All rights reserved.Legal Notices