ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

JSTL 1.0: What JSP Applications Need, Part 2
Pages: 1, 2, 3, 4

Writing Database Data

Besides reading data from a database, you can use JSTL to update information, as well. This example shows how to insert a new row in a table:

<c:catch var="error">
<fmt:parseDate var="empDate" value="${param.empDate}" 
  pattern="yyyy-MM-dd" />
<c:if test="${error != null}">
<jsp:useBean id="empDate" class="java.util.Date" />

INSERT INTO Employee (FirstName, LastName, EmpDate)
  VALUES(?, ?, ?)
<sql:param value="${param.firstName}" />
<sql:param value="${param.lastName}" />
<sql:dateParam value="${empDate}" type="date" />

Before inserting the row, this example illustrates how to use the JSTL parsing actions, as I promised earlier. The page expects all data for the new row to be sent as request parameters (perhaps entered in an HTML form), including an employment date. Before the date can be inserted into the database, it must be converted to its native Java form. That's what the <fmt:parseDate> action does. The value attribute contains an EL expression that gets the value of the empDate request parameter. The action tries to interpret it as a date written in the format specified by the pattern attribute (a four-digit year followed by a two-digit month and a two-digit day, separated by dashes). If it's successful, it stores the date in its native form in a variable with the name specified by the var attribute.

The <c:catch> action takes care of invalid date strings. If the parameter value can not be interpreted as a date, the <fmt:parseDate> throws an exception, which the <c:catch> action catches and saves in the specified variable. When this happens, the <c:if> action's test condition evaluates to true, so the variable for the employment date is instead created by the nested <jsp:useBean> action.

To insert the row, you use the <sql:update> action. As with the query action, the SQL statement can be specified as the element's body or by an sql attribute. The <sql:update> action can be used to execute INSERT, UPDATE, and DELETE statements, as well as statements that create or remove database objects, such as CREATE TABLE and DROP TABLE. The number of rows affected by the statement can optionally be captured in a variable named by a var attribute.

In this example (as in most real-world applications), the column values are not known until runtime; they come from request parameters. The SQL INSERT statement therefore includes one question mark per value as a placeholder and nested parameter actions that set the value dynamically. The FirstName and LastName columns are text columns, and <sql:param> actions set their values to the values of the corresponding request parameters.

The EmpDate column, however, is a date column, demanding special attention. First of all, you must use a variable that holds a date in its native form (a java.util.Date object), so instead of using the request parameter value, we use the variable created by the <fmt:parseDate> or <jsp:useBean> actions. Second, you must use the <sql:dateParam> action to set the value. In this example, I'm using only the date part, so I also set the optional type attribute to date. Other valid values are time and timestamp (the default), for columns that take only the time or both the date and time.

There's one more JSTL database action that I have not described so far: <sql:transaction>. You can use this action to group multiple update (or even query) actions when they must all be executed as part of the same database transaction. The standard example is transferring an amount of money from one account to another, implemented as one SQL statement that removes the money from the first account and another statement that adds it to the other. The JSTL specification and my book include detailed examples of how to use transactions.

If you encapsulate all database access in Java classes instead of using the JSTL database action, there's still one part of JSTL that you may find useful. It's a class named javax.servlet.jsp.jstl.sql.ResultSupport, with these two methods:

public static Result toResult(java.sql.ResultSet rs);
public static Result toResult(java.sql.ResultSet rs, int maxRows);

You can use this class to turn a standard JDBC ResultSet object into a JSTL Result object before forwarding it to a JSP page for display. The JSTL actions can easily access the data in a Result object, as shown earlier. Another, arguably better, approach is to pass the query result to the JSP page as a custom data structure, such as a List of beans that contain the data for each row, but the Result object is still a good candidate for prototypes and small applications.


In this installment, I have described the JSTL support for internationalization and database access, including how some of the resources used by these actions can be configured through the web.xml file, JNDI, and servlets, letting other components of an MVC-based application interact with the JSTL actions. It's impossible to discuss everything about these subjects in detail in an article, but I hope that what I've described is enough to get you started.

In the next part, we will look at how classes defined by the JSTL specification can be used to simplify development of your own custom actions.


Hans Bergsten is the founder of Gefion Software and author of O'Reilly's JavaServer Pages, 3rd Edition.

Return to ONJava.com.