Many dynamic web applications need to access relational databases for the dynamic aspects of their presentation layer. While it can be argued that database operations should be handled in the business logic of a web application designed with an MVC architecture, the fact of the matter is that real world applications will sometimes require this capability within the JSP pages for various reasons (e.g. prototyping/testing, small scale/simple applications, lack of developer resources, etc).
With JSTL, we do not want to force a unique way to design web applications. Our goal is to provide the right set of common tools needed by page authors to be successful in their projects. A set of database tags has been clearly identified as one of these required common tools
The JSTL database actions allow a page author to
DataSource
Database actions operate on a DataSource. A DataSource is an object associated with the database to be accessed. It provides a factory for Connection objects. With the JSTL database tagset, a DataSource can be specified either as a javax.sql.DataSource object, or as a path to a JNDI resource (in containers that support it; i.e. J2EE containers, or others that support this specific mechanism for referencing resources.)
There are many ways by which a page author can get access to a DataSource, namely:
1. Transparent collaboration (implicit scoped attribute)
Initialization code in the application logic (e.g. application event listener, initialization servlet) can be used to set the default DataSource associated with a web application via the scoped attribute "javax.servlet.jsp.jstl.DataSource". With this approach, an application with a single database makes the DataSource that is being used by the database actions totally transparent to the page author
2. Explicit collaboration via application logic
The controller code in an MVC-based application sets a JSP scoped attribute representing the DataSource. The attribute's name and scope are communicated to the page author who uses that information in the "dataSource" attribute of the database actions. For example:
<sql:query dataSource="$dataSource" ...>3. Explicit collaboration via <sql:driver> action
If a DataSource cannot be set within the application logic (prototype/simple project with no developer at hand), the <sql:driver> action can be used as a simplified alternative for the creation of a DataSource object wrapped around a JDBC driver. For example:
<sql:driver var="dataSource"
driver="org.gjt.mm.mysql.Driver"
url="//localHost/myDB">
<sql:query dataSource="$dataSource" .../>
Querying a database
The most common usage of the database actions is to query a database and display the results. In the example below, customers from China are selected from the "customers" table of the database, they are ordered by last name, and finally displayed in an HTML table.
<sql:query var="customers" dataSource="$dataSource">
SELECT * FROM customers
WHERE country = 'China'
ORDER BY lastname
</sql:query>
<table>
<jc:forEach var="row" items="$customers.rows">
<tr>
<td><jc:expr value="$row.lastName"/></td>
<td><jc:expr value="$row.firstName"/></td>
<td><jc:expr value="$row.address"/></td>
</tr>
</jc:forEach>
</table>
Updating a database
It is possible to update a database via the <sql:update> action. Updates to the database can be grouped within a <sql:transaction> action to ensure database integrity. For example, the following code transfers money between two accounts.
<sql:transaction dataSource="$dataSource">
<sql:update>
UPDATE account
SET Balance = Balance - ?
WHERE accountNo = ?
<sql:param value="$transferAmount">
<sql:param value="$accountFrom">
</sql:update>
<sql:update>
UPDATE account
SET Balance = Balance + ?
WHERE accountNo = ?
<sql:param value="$transferAmount">
<sql:param value="$accountTo">
</sql:update>
</sql:transaction>
SQL Statement Parameters
The JSTL database tagset also supports SQL statements that take parameters to supply values to be used in place of question mark placeholders (as seen in example above). This support is extensible via interface SQLExecutionTag so that custom "parameter" tags can be developed to easily set the value of statement parameters from any kind of input data.
<sql:query> is the general purpose action for performing SQL queries on a database and gettting back a single result set containing rows of data.
This action may or not have a body. If the action has no body, it is of the form:
<sql:query var="varName" sql="sqlQuery" [dataSource=dataSourceSpec]
[maxRows="maxRows"] [startRow="startRow"]/>
If the action has a body, it is of the form:
<sql:query var="varName" [sql="sqlQuery"] [dataSource=dataSourceSpec]>
[maxRows="maxRows"] [startRow="startRow"]>
... optional query statement ...
... optional <sql:param> actions ...
</sql:query>In the "body" form, the SQL query statement can be specified either via attribute "sql" or within the tag's body.
dataSourceSpec ::= javax.sql.DataSource object | "jndiPathToDataSource"
The results of the query are exposed via an object that implements the Result interface (see section 4.1) in the attribute named by "var".
For example:
<sql:query var="customers" dataSource="$dataSource">
SELECT * FROM employees
WHERE country = 'China'
ORDER BY lastname
</sql:query>
If the query produces no results, then an empty (i.e. size is 0) Result object is returned.
The SQL query statement can be specified either via attribute "sql" or within the tag's body (mutually exclusive). Optional parameter markers (?) can be specified in the query statement as supported by JDBC. The values of these parameters are set via subtag <sql:param>. <sql:query> implements interface SQLExecutionTag (see section 4.2) to make this extensible to custom tags.
The set of rows stored within the Result data structure can be limited by attribute "maxRows". If unspecified, all rows resulting from the query are stored. Attribute "startRow" can be used to specify the starting row for the results. For example, if set at 10, this means that the first 9 rows will be skipped before the rows returned by the query are stored in the Result data structure (up to maxRows if applicable).
These two attributes protect against "runaway queries", allow efficient
access to the top rows of large result sets, and also provide a poor-man's way
of paging through a large set of results by bumping the startRow up by maxRows
on each page.
It is also possible to configure a default "maxRows" value that applies
by default to all <sql:query> actions. This is done via context parameter
"javax.servlet.jsp.jstl.sql.maxRows". This global value for maxRows
can be turned off by giving the maxRows attribute of an <sql:query> action
the value -1.
The tag retrieves and releases a Connection using the following algorithm:
It is illegal for <sql:query> to specify a DataSource when nested within <sql:transaction> (throws JspTagException).
<sql:update> executes an SQL INSERT, UPDATE or DELETE statement. In addition, SQL statements that return nothing, such as SQL DDL statements, can be executed.
This action may or not have a body. If the action has no body, it is of the form:
<sql:update sql="sqlUpdate"
[var="varName"]
[dataSource=dataSourceSpec]/>
If the action has a body, it is of the form:
<sql:update [sql="sqlUpdate"]
[var="varName"]
[dataSource=dataSourceSpec]>
... optional update statement ...
... optional <sql:param> actions ...
</sql:query>The SQL update statement can be specified either via attribute "sql" or within the tag's body.
dataSourceSpec ::= javax.sql.DataSource object |
"jndiPathToDataSource"
The result of the database update is optionally exposed in the attribute named by "var". This result is a java.lang.Integer object that tells how many rows were affected by the statement. The value 0 is returned if no rows were affected by INSERT, DELETE, or UPDATE, as well as for DDL statements that return nothing (e.g. CREATE TABLE). This is the same behavior as Statement.executeUpdate() in JDBC.
For example:
<sql:update>
UPDATE account
SET BALANCE = ?
WHERE accountNo = ?
<sql:param value="$balance">
<sql:param value="$accountNo">
</sql:update>
The tag acts identically to <sql:query> with respect to connection management, the "sql" and "dataSource" attributes, PreparedStatement lifecycle, etc.
<sql:transaction> serves to establish a transaction context for its <sql:query> and <sql:update> subtags.
<sql:transaction [dataSource=dataSourceSpec]
[transactionIsolation="transactionIsolationLevel"]>
... <sql:query> and <sql:update> statements ...
</sql:transaction>
dataSourceSpec ::= javax.sql.DataSource object | "jndiPathToDataSource"
transactionIsolationLevel ::= "none" | "read_committed"
| "read_uncommitted" |
"repeatable_read"
| "serializable"
The transaction isolation levels are the same as the ones supported in JDBC
by java.sql.Connection.
Throughout the transaction, any SQLException that occurs is simply propagated.
The same connection-management scheme as <sql:query> is used to acquire a Connection, except that the parent isn't checked for DataSource (<sql:transaction> tags cannot be nested as a means of propagating a Connection). It is iIllegal for nested <sql:query> and <sql:update> subtags to specify a DataSource.
The behavior of the <sql:transaction> action is undefined if it executes in the context of a larger JTA user transaction.
<sql:driver> facilitates the wrapping of a DataSource object around a JDBC driver for prototype/simple applications.
<sql:driver var="varName"
[driver="driverClassName"]
[url="jdbcUrl"]
[user="userName"]
/>
The <sql:driver> action is only intended for prototyping and simple applications. An application's business logic (e.g. via a life cycle event listener or controller servlet) should normally be used to create a DataSource and make it available to the rest of the application.
Four configuration parameters can be specified to create a DataSource object wrapped around a JDBC driver manager:
All parameters, except for "password", can be specified via an action attribute or a context init parameter (see below). Action attributes take precedence over context init parameters. The password configuration parameter can only be specified via a context init parameter to prevent situations where sensitive information would be hard coded in a JSP page.
The context init parameters are:
For example, assuming a prototype web application whose database is accessible by anyone without any password:
<sql:driver var="dataSource"
driver="org.gjt.mm.mysql.Driver"
url="//localhost/appDB"/>
<sql:param> is used as a subtag of SQLExecutionTag actions such as <sql:query> and <sql:update> to set the values of parameter markers ('?') specified in the SQL statement.
This action may or not have a body. If the action has no body, it is of the form:
<sql:param value="parameterValue"/>
If the action has a body, it is of the form:
<sql:param>
... parameter value ...
</sql:param>
If tag body is used, it is first run through String.trim().
With the value specified, <sql:param> locates its nearest SQLExecutionTag ancestor and calls SQLExecutionTag.addSQLParameter(value). The mapping from Java object types to SQL types is handled according to the JDBC specification (see method java.sql.PreparedStatement.setObject()).
public interface Result {
public Row[] getRows();
public ResultMetaData getmetaData();
public int getSize(); // number of rows in the Result
public boolean isLimitedByMaxRows();
}
public interface ResultMetaData {
public ColumnMetaData get(String name);
public ColumnMetaData get(int index);
public ColumnMetaData[] getColumns();
// @@@ non-column meta data here as well
}
public interface Row {
public Column get(String name);
public Column get(int index);
public Column[] getColumns();
}
public interface ColumnMetaData {
public String getName();
public int getType();
public String getTypeName();
... all other relevant
... java.sql.ResultSetMetaData information
}
public interface Column extends ColumnMetaData {
public Object getValue();
public String toString();
}
One key incentive for defining new interfaces representing the result of a query is to allow simpler access to result data via the Expression Language. Here are some sample use cases:
Use case 1: iteration (probably most common use case)
<c:forEach var="row" items="$result.rows">
Price is: <c:expr value="$row.get('price')"/>
Quantity is: <c:expr value="$row.columns[3]"/>
</c:forEach>
[Note: $row.get('price') could be replaced by $row.price
if EL supports the mapping $a.prop -> a.get("prop")]
Use case 2: direct access from $result
Price is: $result.rows[24].price
Price is: $result.rows[24].columns[3]
Use case 3: Putting it all together:
<table>
<!-- header -->
<tr>
<!-- for each column in the result -->
<c:forEach var="metaData" items="$result.metaData.columns">
<!-- Column Name -->
<th>$metaData.name</th>
</c:forEach>
</tr>
<!-- for each row in the result -->
<c:forEach var="row" items="$result.rows">
<tr>
<!-- for each column in the row -->
<c:forEach var="column" items="$row.columns">
<td><c:expr value="$column"/></td>
</c:forEach>
</tr>
</c:forEach>
</table>
The SQLExecutionTag interface identifies a tag handler that can accept parameter values from nested tag handlers.
public interface SQLExecutionTag {
public void addSQLParameter(Object value) throws SQLException;
}
This interface is implemented by both <sql:query> and <sql:update>. Action <sql:param> invokes method addSQLParameter() of the SQLExecutionTag interface to set parameter values.
The parameter's index and semantic interpretation are both the responsibility of the tag handler; a typical implementation will keep an internal index and increment it once for each call.
This interface is public to allow for custom actions that can facilitate the setting of statement parameters. For example, let's assume an end-user enters a date in three separate fields: year, month, and day. If the database table has a single column for the complete date, one could develop a <foo:dateParam> action to set the corresponding parameter as follows:
<foo:dateParam year="$year" month="$month" day="$day" />
Please note that the JSTL internationalization tags support the parsing of string representations of dates and numbers into their associated data type (java.util.Date and java.lang.Number respectively). For example:
<fmt:parseDate var="myDate" value="$param:someDate />
<sql:param value="$myDate" />
| SQL Actions | |
| Action | Sample usage |
|
General purpose action for performing SQL queries on a database and gettting back a single result set containing rows of data. |
|
|
General purpose action for "updates"; i.e. statements that update the database (insert, update, delete) and return nothing or a single integer. |
|
|
|
|
|
Facilitates the setup of a DataSource object around a JDBC driver for prototype/simple applications. |
|
|
Subtag of SQLExecutionTag actions such as <sql:query> and <sql:update> to set the values of parameter markers ('?') specified in the SQL statement. |
|
|
|
|
|
Identifies a tag handler that can accept parameter values from nested tag handlers. |
|