Core JSTL[c] Mastering the JSP Standard Tag Library

2017-07-07 02:10:07

This chapter has already introduced simple database queries with <sql:query>. This section examines the use of that action in more detail, including accessing query results, limiting query size , and scrolling through large queries.

Using <sql:query>

The <sql:query> action lets you perform a database query. That action has the following syntax: ^[10]

^[10] Items in brackets are optional. See "<sql:query>" on page 533 for a more complete description of <sql:query> syntax.

<sql:query sql var [scope] [dataSource] [startRow] [maxRows]/>

The sql attribute specifies the SQL query. That query can optionally be specified in the body of the <sql:query> action, with this syntax:

<sql:query var [scope] [dataSource] [startRow] [maxRows]> SQL Query </sql:query>

The <sql:query> action stores the query result in a scoped variable whose name is specified with the mandatory var attribute.

The <sql:query> action also has four optional attributes: scope , dataSource , startRow , and maxRows .

The scope attribute specifies the scope for the var scoped attribute; the default is page scope.

The dataSource attribute specifies a data source. You can specify that attribute as a string or a scoped variable that references a string or an instance of javax.sql.DataSource . See "How JSTL Locates Data Sources" on page 363 for examples of setting the dataSource attribute.

The startRows attribute lets you specify a zero-based starting row for a query. The default value specifies the first row in a query. The last row is specified as n-1, where n is the number of rows in the query.

The maxRows attribute specifies the maximum number of rows in a query. That attribute lets you limit the size of your queries and thereby guard against so-called runaway queries and also lets you scroll through large queries. The latter is illustrated in "Scrolling Through Large Queries" on page 385. If you don't specify the maxRows attribute, database queries will not be limited.

If you want to limit the size of all queries for an application, you don't have to specify the maxRows attribute for all of your queries; instead, you can set the SQL_MAX_ROWS configuration setting, for example:

public class InitializationServlet extends HttpServlet { public void init() throws ServletException { // Limit the default size of all queries in your application Config.set(getServletContext(), Config.SQL_MAX_ROWS, 25 ); } }

The preceding code fragment sets the SQL_MAX_ROWS configuration setting in a servlet that's presumably loaded at startup. It's more common, however, to set the SQL_MAX_ROWS configuration setting in the deployment descriptor, like this:

<web-app> ... <context-param> <param-name> javax.servlet.jsp.jstl.sql.maxRows </param-name> <param-value> 25 </param-value> </context-param> ... </web-app>

It's more convenient to set the SQL_MAX_ROWS configuration setting in the deployment descriptor, because you don't have to write any code. If you set the SQL_MAX_ROWS setting in the deployment descriptor, as in the preceding code fragment, you must use that configuration setting's name, which is javax.servlet.jsp.jstl.sql.maxRows . See "Configuration Settings" on page 359 for more information about configuration settings that support the JSTL SQL actions, and see "Configuration Settings" on page 230 for more information about configuration settings in general.

You can also use <sql:query> with SQL parameters, with the following syntaxes:

<sql:query sql var [scope] [dataSource] [startRow] [maxRows]> <sql:param> and <sql:dateParam> actions </sql:query> <sql:query var [scope] [dataSource] [startRow] [maxRows]> SQL Query optional <sql:param> and <sql:dateParam> actions </sql:query>

See "Prepared Statements and SQL Parameters" on page 389 for more information about SQL parameters and the <sql:param> and <sql:dateParam> actions.

Now that we have a good grasp of how to use <sql:query>, let's take a look at an example, shown in Figure 9-3, that queries a database, accesses the query result, and displays the result in an HTML table.

Figure 9-3. Querying a Database

The JSP page shown in Figure 9-3 index.jsp is listed in Listing 9.7.

The JSP page listed in Listing 9.7 performs a query that selects all customers from the Customers tablesee "A Simple Database" on page 360 for more information about that table. That query uses a data source specified in the deployment descriptor ( WEB-INF/web.xml) with the javax.servlet.jsp.jstl.sql.dataSource context initialization parameter, as discussed in "Specify Your Data Source in the Deployment Descriptor" on page 366. All of the examples in the rest of this chapter create data sources in the same way.

The result of the query is stored in page scope in a scoped variable named customers , and the HTML table is created by accessing three of that query's properties: rowCount , columnNames , and rowsByIndex . Those properties are discussed in the next section.

Listing 9.7 index.jsp (Querying)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Accessing Database Queries</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <sql:query var='customers'> SELECT * FROM CUSTOMERS </sql:query> <%-- Access the rowCount property of the query --%> <p>There are <c:out value='${ customers.rowCount }'/> rows in the customer query. Here they are:</p> <%-- Create a table with column names and row data --%> <p><table border='1'> <tr> <c:forEach var='columnName' items='${ customers.columnNames }'> <th><c:out value='${columnName}'/></th> </c:forEach> </tr> <c:forEach var='row' items='${ customers.rowsByIndex }'> <tr> <c:forEach var='rowData' items='${row}'> <td><c:out value='${rowData}'/></td> </c:forEach> </tr> </c:forEach> </table> </body> </html>

Accessing Query Properties

Every time you execute a database query with <sql:query>, a query result is created and stored in a variable in one of the four scopes (page, request, session, or application). Queries have five properties that let you access their data; those properties are listed in Table 9.5.

It's easy to access a query's results with the properties listed in Table 9.5; for example, assume that you executed a database query, like this:

<sql:query var='customers'> SELECT * FROM CUSTOMERS </sql:query>

You can iterate over the customers query column names like this:

<c:forEach var='columnName' items='${ customers.columnNames }'> <c:out value='${columnName}'/><br> </c:forEach>

The preceding code fragment prints each column name from the customer's query. It's more common, however, to generate an HTML table with a query's propertieshere's how you would generate such a table for the customers query with the columnNames and rows properties:

Table 9.5. Query Properties

Property	Description
`columnNames`	An array of strings representing column names.
`rows`	An array of sorted maps. Each of those maps represents a row of data from the query.
`rowsByIndex`	A two-dimensional array of objects representing rows of data from the query.
`rowCount`	A count of the rows in the query.
`limitedByMaxRows`	A `boolean` value that indicates whether the number of rows in the query was limited.

The preceding code fragment iterates over the customers.rows property and places the corresponding data from each row in the table. The customers.rows property is an array of maps, so you access row data with column names, which are case insensitive, as you can probably tell from the preceding code fragment. Alternatively, you can use the customers.rowsByIndex property to generate the exact same table generated by the preceding code fragment, like this:

The customers.rowsByIndex property is an array of arrays, so you access row data by iterating over each array, as shown in the preceding code fragment.

If you use the rows property to access a query's row data, you only have to iterate once, but you must know the names of the query's columns . If you use the rowsByIndex property to access row data, you have to iterate twice, but you do not need to know the names of the query's columns.

In general, you will probably prefer the rowsByIndex property when you need to manipulate all of a query's row data in the order in which that data is stored. You will probably prefer the rows property when you need to manipulate a subset of a query's row data or when you need to reorder that data. For example, it's easy to change the order in which row data is displayed in the preceding code fragment that uses the customers.rows property, perhaps by displaying the Name column before the Cust_ID column. That would be much more difficult to do in the code fragment that uses the customers.rowsByIndex property.

The rowCount and limitedByMaxRows properties are simple scalar properties that you can access like this: ^[11]

^[11] A scalar property contains a single value.

There are <c:out value='${ customers.rowCount }'/> rows in this query. <c:choose> <c:when test='${ customers.limitedByMaxRows }'> This query was limited. </c:when> <c:otherwise> This query was not limited. </c:otherwise> </c:choose>

Scrolling Through Large Queries

As discussed in "Using <sql:query>" on page 378, the <sql:query> action has two attributes startRow and maxRows that let you limit the rows contained in a query. You can also scroll through large queries by using those attributes together.

The startRow attribute is zero-based; for example, if you specify for that attribute, the starting row will be the first row in the query. The maxRows attribute specifies the maximum number of rows returned by the query; for example, if you specify 3 for that attribute, a maximum of three rows will be returned. ^[12] Here are two examples of how the startRow and maxRows attributes work together: If a query potentially has 20 rows and you specify for startRow and 3 for maxRows , the query result will contain three rows, starting with the first row in the query. If you specify 3 for the startRow attribute and 3 for the maxRows attribute, the query result will contain rows 4 , 5 , and 6 .

^[12] If you specify -1 for the maxRows attribute, all of the query rows will be included in the query result.

Figure 9-4 shows a simple Web application that lets you scroll through the query shown in Figure 9-3 on page 381. The top picture in Figure 9-4 shows a JSP page that lets you select a scroll increment, which is subsequently passed to another JSP page as a request parameter. The second JSP page, shown in the middle and bottom pictures in Figure 9-4, lets you scroll forward and backward through the query.

Figure 9-4. Scrolling through Large Database Queries.

The top picture shows a JSP page that lets you select a scrolling increment. The middle picture shows the first three customers in the query, and the bottom picture shows the two last customers in the query.

The JSP page shown in the top picture in Figure 9-4 is listed in Listing 9.8.

The preceding JSP page uses the <c:forEach> action to create options for an HTML select element. Those options represent a scroll increment. The action for the form in Listing 9.8 is scroll_query.jsp , which is passed the scroll increment as a request parameter. That JSP page is listed in Listing 9.9.

The preceding JSP page starts with a <c:choose> action that has three code segments. The first time that JSP page is executed, there is no scroll request parameter, so the code in the body of the first <c:when> action is invoked. That code executes a query that selects all customers from the databasesolely to ascertain how many customers are currently in the databaseand subsequently creates three session attributes: scrollStart , which is set to 0, scrollInc , which is set to the value of the scrollIncrement request parameter, and scrollMax , which is set to the number of rows in the query.

Next, the preceding JSP page executes another customer query bracketed by the scrollStart and scrollInc session attributes. The result of that second query is used to create an HTML table.

After the table has been created, the preceding JSP page creates two HTML anchor elements that both reference the JSP page in which they reside. Those anchors pass a request parameter named scroll that indicates scrolling direction. Finally, the JSP page prints the values of the scrollStart , scrollInc , and scrollMax session attributes.

When the anchor elements are activated, the preceding JSP page is reloaded, and passed the scroll request parameter. The <c:choose> action at the top of the page executes code that increments or decrements the scrollStart session attribute, depending on whether the scroll request parameter is "forward" or "backward." Subsequently, the JSP page executes a query limited by the values of the scrollStart and scrollInc session attributes and redisplays the results in a table, along with the anchors and the values of the scrollStart , scrollInc , and scrollMax session attributes.

Listing 9.8 index.jsp (Scrolling)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Scrolling Through Database Queries</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <form action='scroll_query.jsp'> By clicking the submit button, you will be transferred to a JSP page that scrolls through a database query with 20 rows. <p>Please select a Scroll Increment: <select name='scrollIncrement'> <c:forEach var='item' items='1,2,3,4,5'> <option value='<c:out value="${item}"/>'> <c:out value='${item}'/> </option> </c:forEach> </select> <p><input type='submit'/> </form> </body> </html>

Listing 9.9 scroll_query.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Scrolling Through Database Queries</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c'%> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <c:choose> <%-- When there is no scroll request parameter --%> <c:when test='${empty param.scroll}'> <%-- This query is executed solely to ascertain the number of customers in the database, which is used to set the scrollMax attribute below --%> <sql:query var='exploring'> SELECT * FROM CUSTOMERS </sql:query> <%-- Set scrollMax to the number of rows in the Customers table --%> <c:set var=' scrollMax ' value='${exploring.rowCount}' scope='session'/> <%-- Set scrollStart to 0 and scrollInc to the value of the request parameter scrollIncrement --%> <c:set var=' scrollStart ' value='0' scope='session'/> <c:set var=' scrollInc ' value='${param.scrollIncrement}' scope='session'/> </c:when> <%-- When the scroll request parameter is forward --%> <c:when test='${param.scroll == "forward"}'> <%-- If it's valid to increment scrollStart --%> <c:if test='${scrollStart + scrollInc < scrollMax}'> <%-- Increment scrollStart by scrollInc --%> <c:set var=' scrollStart ' value='${scrollStart + scrollInc}' scope='session'/> </c:if> </c:when> <%-- When the scroll request parameter is backward --%> <c:when test='${param.scroll == "backward"}'> <%-- If it's valid to decrement scrollStart --%> <c:if test='${scrollStart - scrollInc >= 0}'> <%-- Decrement scrollStart by scrollInc --%> <c:set var=' scrollStart ' value='${scrollStart - scrollInc}' scope='session'/> </c:if> </c:when> </c:choose> <%-- Perform a customer query limited by the scrollStart and scrollInc variables --%> <sql:query var='customers' startRow='${ scrollStart }' maxRows='${ scrollInc }'> SELECT * FROM CUSTOMERS ORDER BY CUST_ID </sql:query> <%-- Create a table with column names and row data --%> <p><table border='1'> <tr> <c:forEach var='columnName' items='${customers.columnNames}'> <th><c:out value='${columnName}'/></th> </c:forEach> </tr> <c:forEach var='row' items='${customers.rowsByIndex}'> <tr> <c:forEach var='rowData' items='${row}'> <td><c:out value='${rowData}'/></td> </c:forEach> </tr> </c:forEach> </table> <%-- Create anchors that point back to this page with a request parameter indicating scroll direction --%> <p> <a href='?scroll=forward'>Scroll Forward</a><br> <a href='?scroll=backward'>Scroll Backward</a> <%-- Show the values of the scrollStart, scrollInc, and scrollMax session attributes --%> <p><c:out value='Scroll Start: ${scrollStart}'/>, <c:out value='Scroll Inc: ${scrollInc}'/>, <c:out value='Scroll Max: ${scrollMax}'/><br> </body> </html>

Prepared Statements and SQL Parameters

Web applications often need to execute queries with parameters. For example, the Web application shown in Figure 9-5 accesses orders for a specified customer that are greater than a certain amount. Both the customer and the order amount are entered by a user and are, therefore, query parameters.

Figure 9-5. Using SQL Parameters to Select Customer/Order Amount.

The top picture shows a JSP page that lets users select a customer and an order amount. The bottom picture shows orders for that customer that are greater than $19.99.

The top picture in Figure 9-5 shows a JSP page that lets you select a customer and specify a minimum order amount. When the Show Orders button is activated, the JSP page shown in the bottom picture is loaded. That JSP page executes a query with the specified customer and order amount, and displays the result in an HTML table.

The JSP page shown in the top picture in Figure 9-5 is listed in Listing 9.10.

The preceding JSP page executes a query against all the customers in the database. That query is subsequently used to populate an HTML select element, which lets users select a customer. After a user selects a customer and a minimum order amount, clicking on the submit button takes the user to show_orders.jsp , which is listed in Listing 9.11.

The preceding JSP page executes a query, specifying the customer and minimum order amount with <sql:param> actions. That action has the following syntax:

<sql:param value/>

Listing 9.10 index.jsp (Using SQL Parameters: 1)

You can also specify the value in the body of the <sql:param> action with this syntax:

<sql:param> value </sql:param>

Listing 9.11 show_orders.jsp (Selecting Orders)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Using SQL Parameters</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <%@ taglib uri='http://java.sun.com/jstl/fmt' prefix='fmt'%> <font size='5'> Orders Over <fmt: formatNumber type='currency'> <c:out value='${param.amount}'/> </fmt:formatNumber> for <c:out value='${param.customer}'/>: </font> <%-- Select orders over a specified amount for a specified customer --%> <sql:query var='orders'> SELECT * FROM Orders JOIN Customers WHERE Amount > ? AND Name = ? AND Customers.Cust_ID = Orders.Cust_ID <%-- Specify a parameter value with the value attr --%> <sql:param value='${param.amount}'/> <%-- Specify a parameter value in the body content --%> <sql:param> <c:out value='${param.customer}'/> </sql:param> </sql:query> <%-- Display the query results --%> <p><table border='1'> <tr> <th>ORDER_NUMBER</th> <th>CUST_ID</th> <th>NAME</th> <th>AMOUNT</th> <th>DESCRIPTION</th> </tr> <c:forEach var='row' items='${orders.rows}'> <fmt: formatNumber var='amount' type='currency'> <c:out value='${row.AMOUNT}'/> </fmt:formatNumber> <tr> <td><c:out value='${row.Order_Number}'/></td> <td><c:out value='${row.Cust_ID}'/></td> <td><c:out value='${row.Name}'/></td> <td><c:out value='${amount}'/></td> <td><c:out value='${row.Description}'/></td> </tr> </c:forEach> </table> </body> </html>

Notice that you must use <c:out> to specify SQL parameters in the body of an <sql:param> actionyou cannot specify a JSTL EL expression directly in the body of an <sql:param> action, like this:

<%-- This code fragment will not work with JSP 1.2 --%> <sql:param> ${param.someRequestParameter} </sql:param>

JSTL EL expressions are not supported in JSP 1.2, so you cannot use them directly in the body of an action, but support for JSTL EL expressions is planned for JSP 2.0. In the meantime, you can use the <c:out> action, like this:

<%-- This code fragment will not work with JSP 1.2 --%> <sql:param> <c:out value='${param.someRequestParameter}'/> </sql:param>

As illustrated in Listing 9.11, specifying SQL query parameters is a two-step process:

In the query, specify dynamic values with a question mark.

In the body of the <sql:query> action, include an <sql:param> action for each of the query parameters.

The order of the question marks and the <sql:param> actions is significant: the value specified for the first <sql:param> action corresponds to the first question mark in the query, the second <sql:param> action's value corresponds to the second question mark, and so on.

Date Parameters

"Prepared Statements and SQL Parameters" on page 389 showed you how to use the <sql:param> action to specify parameters for <sql:query> and <sql:update> actions, but that approach will not work for parameters that are instances of java.util.Date . If you specify an instance of java.util.Date as a parameter representing an SQL date, time, or timestamp, that java.util.Date instance requires special handling. That special handling involves wrapping instances of java.util.Date in an instance of java.sql.Date (for dates), java.sql.Time (for times), and java.sql.Timestamp (for dates and times). Fortunately, you need not be concerned with those details, because JSTL provides an <sql:dateParam> action that makes instances of java.util.Date suitable for SQL dates, times, and timestamp parameters. That action has the following syntax: ^[13]

^[13] Items in brackets are optional. See "<sql:dateParam>" on page 540 for a more complete description of <sql:dateParam> syntax.

<sql:dateParam value [type]/>

The value attribute must be an instance of java.util.Date , and the type attribute must be either date , time , or timestamp , to indicate the type of SQL parameter that the value attribute represents. The default value for the type attribute is timestamp .

Figure 9-6 shows a Web application, similar to the Web application shown in Figure 9-5 on page 390, that searches for orders that match a certain criterion. For the Web application shown in Figure 9-6, that criterion is the date the orders were placed. The top picture in Figure 9-6 shows a JSP page that lets you select an order date, and the bottom picture in Figure 9-6 shows a JSP page that displays all of the orders placed on the date that you specified.

Figure 9-6. Using SQL Date Parameters to Select Order Date.

The top picture shows a JSP page that lets users select an order date. The bottom picture shows all orders for that date.

The JSP page shown in the top picture in Figure 9-6 is listed in Listing 9.12.

The preceding JSP page creates a simple HTML form that lets you select a date. That form's action is show_orders.jsp , which is listed in Listing 9.13.

The preceding JSP page creates an instance of java.util.Date by parsing the date request parameter, using the <fmt:parseDate> action. See "Formatting and Parsing Dates and Times" on page 333 for more information about <fmt:parseDate>. Subsequently, the JSP page executes a database query that joins the Orders and Customers tables, where the order date is specified with a request parameter that's made available to the <sql:query> action with an <sql:dateParam> action. Notice that the <sql:param> action in that JSP page is commented; if you uncomment that action and comment the <sql:dateParam> action, the resulting query will contain no row data, which illustrates that you cannot use the <sql:param> action for parameters that are instances of java.util.Date . After the query has completed, the JSP page displays the result of that query in an HTML table.

Anytime you have user input that is used in a database query, as is the case for the Web application shown in Figure 9-5 on page 390 or Figure 9-6 on page 395, you will need to use the <sql:param> or <sql:dateParam> actions or a custom actionsee "Implementing Database Custom Actions" on page 418 for more information on implementing custom actions that supply SQL parameters. In addition to using <sql:param> and <sql:dateParam> actions with <sql:query>, you can also use them with <sql:update>. The <sql:update> action is discussed in the next section.

Listing 9.12 index.jsp (Using SQL Parameters: 2)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Using SQL Date Parameters</title> </head> <body> <font size='5'>Order Search</font><hr> <%-- A form with an HTML select element and a submit button --%> <form action='show_orders.jsp'> Find all orders for this date: <select name='date'> <option value='05/20/2002'>05/20/2002</option> <option value='05/21/2002'>05/21/2002</option> <option value='05/22/2002'>05/22/2002</option> <option value='05/23/2002'>05/23/2002</option> <option value='05/24/2002'>05/24/2002</option> </select> <p><input type='submit' value='Show Orders'> </form> </body> </html>

Listing 9.13 show_orders.jsp (Selecting a Date)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Using SQL Date Parameters</title> </head> <body> <%@ taglib uri='http://java.sun.com/jstl/core' prefix='c' %> <%@ taglib uri='http://java.sun.com/jstl/sql' prefix='sql'%> <%@ taglib uri='http://java.sun.com/jstl/fmt' prefix='fmt'%> <%-- Parse the date request parameter into a java.util.Date object and store it in a scoped variable in page scope --%> <fmt: parseDate var='javaDate' value='${param.date}' dateStyle='short'/> <font size='5'> Orders for <c:out value='${param.date}'/>: </font> <%-- Select orders placed on the date specified by the request parameter --%> <sql:query var='orders'> SELECT * FROM Orders JOIN Customers WHERE Orders.Order_Date = ? AND Customers.Cust_ID = Orders.Cust_ID <%-- You can't just specify a Java date with <sql:param>, like this... <sql:param value='${javaDate}'/> --%> <%--...instead, you have to use <sql:dateParam>: --%> <sql: dateParam value='${javaDate}' type='date'/> </sql:query> <%-- Display the query results --%> <p><table border='1'> <tr> <th>CUST_ID</th> <th>NAME</th> <th>ORDER_NUMBER</th> <th>ORDER_DATE</th> <th>AMOUNT</th> <th>DESCRIPTION</th> </tr> <c:forEach var='row' items='${orders.rows}'> < fmt:formatNumber var='amount' type='currency'> <c:out value='${row.AMOUNT}'/> </fmt:formatNumber> <tr> <td><c:out value='${row.Cust_ID}'/></td> <td><c:out value='${row.Name}'/></td> <td><c:out value='${row.Order_Number}'/></td> <td><c:out value='${row.Order_Date}'/></td> <td><c:out value='${amount}'/></td> <td><c:out value='${row.Description}'/></td> </tr> </c:forEach> </table> </body> </html>