Home TOC |
![]() ![]() ![]() |
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 inlisteners.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 nestedparam
tag inside thequery
tag. Theupdate
tag is used to update a database row. Thetransaction
tag is used to perform an atomic update.In
catalog.jsp
, the value of theAdd
request parameter determines which book information should be retrieved from in the database. This parameter is saved as the attribute namebid
and passed to theparam
tag. Notice that thequery
tag obtains its data source from the context attributebookDS
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 thejavax.servlet.jsp.jstl.sql
package.public interface Result Map[] getRows(); Object[][] getRowsByIndex(); ColumnMetaData[] getMetaData(); int getSize(); boolean isLimitedByMaxRows(); public interface ColumnMetaDataThe
var
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 syntaxresult
.rows
to a call toresult
.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, thevar
attribute set by the tag is of typeMap
. To retrieve information from a row, use theget("
colname")
method to get a column value. The JSTL expression language converts the syntaxmap
.
colname to a call tomap
.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 thebook
map as follows.<c:forEach var="book" begin="0" items="${books.rows}"> <h2><c:out value="${book.title}"/></h2> <fmt:message key="By"/> <em><c:out value="${book.first_name}"/> <c:out value="${book.surname}"/></em> (<c:out value="${book.yr}"/>)<br> <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 theRow
andColumn
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 thebid
andbookRow
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 thejsp: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 |
![]() ![]() ![]() |