11.8 Database Actions

JSTL database actions let you connect to a database, query a database, update a database, and execute database transactions.

Overview of JSTL SQL Actions

Table 11.19 lists the JSTL database actions.

Table 11.19. Database Actions

Action	Description
<sql:setDataSource>	Stores a data source in a scoped variable or the SQL_DATA_SOURCE configuration setting.
<sql:query>	Queries a database and stores the query result in a scoped variable.
<sql:update>	Updates a database with a Data Manipulation Language (DML) command or a Data Definition Language (DDL) command.
<sql:param>	Sets SQL parameters for enclosing <sql:query> and <sql:update> actions.
<sql:dateParam>	Sets SQL date parameters for enclosing <sql:query> and <sql:update> actions.
<sql:transaction>	Establishes a transaction for enclosed <sql:query> and <sql:update> actions.

JSTL database actions are discussed in "Database Actions" on page 356. This section provides summaries for each of those database actions.

JSTL also exposes two interfaces and a single class in the javax.servlet.jsp.jstl.sql package, as discussed in "Exposed Classes and Interface" on page 541.

JSTL SQL Configuration Settings

The following configuration settings are supported by JSTL for SQL actions:

• SQL_DATA_SOURCE

• SQL_MAX_ROWS

The SQL_DATA_SOURCE configuration setting is listed in Table 11.20.

Table 11.20. SQL_DATA_SOURCE

Config Constant	SQL_DATA_SOURCE
Name	javax.servlet.jsp.jstl.sql.dataSource
Type	java.lang.String or javax.sql.DataSource
Set by	<sql:setDataSource>, Deployment Descriptor, Config class
Used by	<sql:query>, <sql:update>, and <sql:transaction>

The SQL_DATA_SOURCE configuration setting specifies a data source used by <sql:query>, <sql:update>, and <sql:transaction> actions. You can specify that configuration setting in a deployment descriptor or business component, or in a JSP page with the <sql:setDataSource> action. The SQL_DATA_SOURCE configuration setting can be specified as an object that implements the javax.sql.DataSource interface or as a string that represents a JNDI resource or JDBC parameters.

The SQL_MAX_ROWS configuration setting is listed in Table 11.21.

Table 11.21. SQL_MAX_ROWS

Config Constant	SQL_MAX_ROWS
Name	javax.servlet.jsp.jstl.sql.maxRows
Type	java.lang.String or java.lang.Integer
Set by	Deployment Descriptor, Config class
Used by	<sql:query>

The SQL_MAX_ROWS configuration setting lets you specify a maximum limit for database queries; for example, if you specify a value of 25 for the SQL_MAX_ROWS configuration setting, database queries performed by the <sql:query> action will be limited to 25 rows. There are no JSTL actions that set the SQL_MAX_ROWS configuration setting, so you must specify that configuration setting in a deployment descriptor or in a business component with the Config.set methods .

JSTL SQL Actions

<sql:setDataSource>

Exposes a data source

Syntax: ^[24]

^[24] Items in brackets are optional.

 <sql:setDataSource {dataSource  url [driver] [user] [password]}      [var] [scope]/> 

Description:

The <sql:setDataSource> action stores a data source in a scoped variable or in the SQL_DATA_SOURCE configuration variable. JSTL implementations may choose to expose an existing data source with the exact same characteristics instead of creating a new one.

Attributes:

Attribute [a]	Type	Description
dataSource	java.lang.String or javax.sql.DataSource	A string or a scoped variable that references a string or an instance of javax.sql.DataSource . If you specify a string, it must represent either a JNDI relative path to a data source or a comma-separated combination of JDBC url and, optionally , driver, user, and password.
url	String	A JDBC url, e.g.: jdbc:mysql://localhost/ core -jstl
driver	String	A JDBC driver, e.g.: org.gjt.mm.mysql.Driver
user	String	A user name.
password	String	The user's password.
var	String	The name of a scoped variable that references the data source.
scope	String	Specifies the scope of the variable whose name is specified with the var attribute; otherwise , specifies the scope of the SQL_DATA_SOURCE configuration setting. The default is page scope.

^[a] static dynamic

Constraints and Error Handling:

• If the dataSource attribute is null , <sql:setDataSource> will throw an exception.

In a Nutshell:

You can do two things with <sql:setDataSource>:

• Specify a data source with a Java object or a string representing a JNDI relative path or JDBC parameters

• Store that data source in a scoped variable or the SQL_DATA_SOURCE configuration setting

You can specify your data source with the dataSource attribute, which can be either a string or an instance of javax.sql.DataSource . If it's the latter, <sql:setDataSource> uses it as is; if it's the former, <sql:setDataSource> first assumes the string represents a relative path to a JNDI resource. If <sql:setDataSource> cannot find that resource, it then assumes that the string represents JDBC parameters and tries to establish a JDBC connection. You can specify JDBC parameters with the url attribute and, optionally the driver , user , and password attributes.

Typically, <sql:setDataSource> creates the data source that it stores in a scoped variable or the SQL_DATA_SOURCE configuration setting; however, if a data source exists with the exact same characteristics, JSTL implementations are encouraged to expose the existing data source instead of creating a new one. That lets you put an <sql:setDataSource> action at the top of all your JSP pages (or include a JSP page with an <sql:setDataSource> action) without having to worry about creating unnecessary duplicates of that data source. Realize however, that JSTL implementations do not have to expose existing data sources in this manner ”they are only encouraged to do so.

Here's how you tell <sql:setDataSource> where to store your data source: If you specify the var attribute, <sql:setDataSource> will store that data source in a scoped variable whose name corresponds to the value of that attribute. By default, <sql:setDataSource> stores that scoped variable in page scope, but if you specify the scope attribute in conjunction with the var attribute, you can store that scoped variable in request, session, or application scope. If you use <sql:setDataSource> to store a data source in a scoped variable as described above, the <sql:query>, <sql:update>, and <sql:transaction> actions must specify that data source explicitly with their dataSource attributes.

If you don't specify the var attribute, <sql:setDataSource> stores the data source in the SQL_DATA_SOURCE configuration setting. If you don't specify the var or the scope attributes, that configuration setting applies to page scope, but you can specify a different scope with the scope attribute. (Note that if you specify both the var and scope attributes, <sql:setDataSource> will store a data source in a scoped variable as discussed in the preceding paragraph, instead of storing it the SQL_DATA_SOURCE configuration setting). If you use <sql:setDataSource> to store a data source in the <sql:setDataSource> configuration setting, <sql:query>, <sql:update>, and <sql:transaction> can implicitly access that data source without having to specify their dataSource attributes.

<sql:query>

Executes a database query

Syntax: ^[25]

^[25] Items in brackets are optional.

Syntax #1: Without a body

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

Syntax #2: With a body, specifying SQL query parameters

 <sql:query sql var [scope] [dataSource] [startRow] [maxRows]>  <sql:param> or <sql:dateParam> actions, or both  </sql:query> 

Syntax #3: With a body, specifying an SQL statement and optional query parameters

 <sql:query var [scope] [dataSource] [startRow] [maxRows]>  SQL query statement   optional <sql:param> or <sql:dateParam> actions, or both  <sql:query> 

Description:

The <sql:query> action executes a database query and stores the result of that query in a scoped variable that you specify with the var attribute.

Attributes:

Attribute [a]	Type	Description
sql	String	An SQL query statement, which can optionally be specified in the body of the action.
dataSource	String or javax.sql.DataSource	A string or a scoped variable that references a string or an instance of javax.sql.DataSource . If you specify a string, it must represent either a JNDI relative path to a data source or a comma-separated combination of JDBC url and, optionally, driver, user, and password.
startRow	int	The starting row for the query. The first row of a query is designated with the value 0; the last row is n -1, where n equals the number of rows in the query. The default value is .
maxRows	int	The maximum number of rows in the query. By default, database queries are not limited.
var	String	The name of a scoped variable that contains the query result. The type of that scoped variable is javax.servlet.jsp.jstl.sql.Result .
scope	String	The scope of the scoped variable whose name is specified by the var attribute; default is page scope.

^[a] static dynamic

Constraints and Error Handling:

• If the dataSource attribute is null , <sql:query> will throw an exception.

• The maxRows attribute must be 1 .

• If the dataSource attribute is specified, the action must not be contained in the body of an <sql:transaction> action.

In a Nutshell:

The <sql:query> action executes a database query and stores the result in a scoped variable. That scoped variable, whose name is specified with the var attribute, is an object whose type is javax.servlet.jsp.jstl.sql.Result , which is easier to work with than java.sql.ResultSet . See "Result" on page 541 for an exact definition of the javax.servlet.jsp.jstl.sql.Result interface, and see "Accessing Query Properties" on page 382 for more information about the use of that interface in practice.

The query specified by the sql attribute can contain parameter markers, specified with a question mark, that identify prepared statement parameters. For every parameter marker in an SQL query, there should be a corresponding <sql:param> or <sql:dateParam> action, or a custom action that supplies a parameter to its enclosing <sql:query> (or <sql:update>) action. See "Implementing Database Custom Actions" on page 418 for more information about implementing custom tags that supply SQL parameters to enclosing <sql:query> (or <sql:update>) actions.

You can also specify values for the startRow and maxRows attributes to limit the size of your query. Those attributes can be used to prevent so-called runaway queries, and they can also be used to scroll through large query results. See "Scrolling Through Large Queries" on page 385 for more information on the use of those attributes.

You can specify the dataSource attribute as either a string or an instance of javax.sql.DataSource , the same way you specify the dataSource attribute for the <sql:setDataSource> action; see "<sql:setDataSource>" on page 531 for more information about that action. If an <sql:query> action is nested in an <sql:transaction> action, that <sql:query> action must not specify the dataSource attribute. See "<sql:transaction>" on page 537 for more information about the <sql:transaction> action.

<sql:update>

Updates a database

Syntax: ^[26]

^[26] Items in brackets are optional.

Syntax #1: Without a body

 <sql:update sql [var] [scope] [dataSource]/> 

Syntax #2: With a body, specifying SQL update arguments

 <sql:update sql [var] [scope] [dataSource]>  optional <sql:param> or <sql:dateParam> actions, or both  </sql:update> 

Syntax #3: With a body, specifying an SQL statement and optional update arguments

 <sql:update [var] [scope] [dataSource]>  SQL update statement   <sql:param> or <sql:dateParam> actions, or both  </sql:update> 

Description:

The <sql:update> action executes a database update with a Data Definition Language (DDL) command (insert, update, or delete rows) or with a Data Manipulation Language (DML) command (create, alter, or drop tables).

Attributes:

Attribute [a]	Type	Description
sql	String	An SQL update statement, which can optionally be specified in the body of the action.
dataSource	String or javax.sql.DataSource	A string or a scoped variable that references a string or an instance of javax.sql.DataSource . If you specify a string, it must represent either a JNDI relative path to a data source or a comma-separated combination of JDBC url and, optionally, driver, user, and password.
var	String	The name of a scoped variable that contains the number of rows affected by the database update.
scope	String	The scope of the scoped variable whose name is specified by the var attribute; default is page scope.

^[a] static dynamic

Constraints and Error Handling:

• If the dataSource attribute is null , <sql:update> will throw an exception.

• If you specify scope , you must also specify var .

• If the dataSource attribute is specified, the action must not be contained in the body of a <sql:transaction> action.

In a Nutshell:

The <sql:update> action is almost identical to <sql:query>, except that <sql:query> performs database queries and <sql:update> performs database updates. Also, <sql:update> does not have startRows and maxRows attributes, and the var attribute for <sql:update> is not required. Other than those differences, the two actions have nearly identical behavior: both let you specify an SQL query or update statement with an attribute or in the body of the action.

The update statement specified by the sql attribute can contain parameter markers, specified with a question mark, that identify prepared statement parameters. For every parameter marker in an SQL update statement, there should be a corresponding <sql:param> or <sql:dateParam> action, or a custom action that supplies a parameter to its enclosing <sql:update> action. See "Implementing Database Custom Actions" on page 418 for more information about implementing custom tags that supply SQL parameters to enclosing <sql:update> or <sql:update> actions.

You can specify the dataSource attribute as either a string or an instance of javax.sql.DataSource , the same way you specify the dataSource attribute for the <sql:setDataSource> action; see "<sql:setDataSource>" on page 531 for more information about that action. If an <sql:update> action is nested in an <sql:transaction> action, that <sql:update> action must not specify the dataSource attribute. See <sql:transaction> below for more information about the <sql:transaction> action.

<sql:transaction>

Performs a database transaction

Syntax: ^[27]

^[27] Items in brackets are optional.

 <sql:transaction [dataSource] [isolation]>  <sql:update> or <sql:query> actions, or both  </sql:transaction> 

Description:

The <sql:transaction> action wraps a database transaction around <sql:update> and <sql:query> actions.

Attributes:

Attribute [a]	Type	Description
dataSource	String or javax.sql.DataSource	A string or a scoped variable that references a string or an instance of javax.sql.DataSource . If you specify a string, it must represent either a JNDI relative path to a data source or a comma-separated combination of JDBC url and, optionally, driver, user, and password.
isolation	String	An isolation level for a transaction. Valid values are read_committed , read_uncommitted , repeatable_read , and serializable . The default isolation level is the isolation level originally set (presumably by the database) for the data source.

^[a] static dynamic

Constraints and Error Handling:

• If the dataSource attribute is null , <sql:transaction> will throw an exception.

• <sql:update> and <sql:query> actions in the body of an <sql:transaction> action must not specify a data source.

In a Nutshell:

If your database does not support transactions, <sql:transaction> will throw an exception; otherwise, the <sql:transaction> start tag saves the current autocommit mode, opens a database connection and disables autocommit. When the <sql:transaction> action completes, it closes the connection and restores the original autocommit mode.

If you specify a transaction isolation level with the isolation attribute, the <sql:transaction> start tag saves the current isolation level and sets the transaction isolation level to the value specified with the isolation attribute. When the <sql:transaction> action completes, it restores the original isolation level.

If all of the enclosed <sql:update> and <sql:query> actions execute successfully, the <sql:transaction> end tag commits the transaction; otherwise, if an exception is thrown, <sql:transaction> catches the exception, executes a rollback, and rethrows the exception.

Because <sql:transaction> manages database connections, enclosed <sql:update> and <sql:query> actions must not specify a data source; if they do, that error will be caught by the JSTL tag library validator.

<sql:param>

Specifies an SQL parameter for enclosing <sql:query> or <sql:update> actions

Syntax:

Syntax #1: Without a body

 <sql:param value/> 

Syntax #2: With a body, specifying an SQL query argument

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

Description:

The <sql:param> action specifies an SQL parameter for an enclosing <sql:query> or <sql:update> action.

Attributes:

Attribute [a]	Type	Description
value	Object	This attribute specifies an SQL parameter for an enclosing <sql:query> and <sql:update> actions. That value can also be specified in the body of the <sql:param> action.

^[a] static dynamic

Constraints and Error Handling:

• If you specify a null value for the value attribute, the corresponding parameter is set to the SQL value NULL .

In a Nutshell:

The <sql:param> action lets you specify an SQL parameter for an enclosing <sql:query> or <sql:update> action. You can specify that parameter with the <sql:param> action's value attribute, or you can specify it in the body of the <sql:param> action.

If you want to specify an SQL date, time, or timestamp and you want to specify that parameter with an instance of java.util.Date , you must use the <sql:dateParam> action instead of <sql:param>; see <sql:dateParam> below for more information about the <sql:dateParam> action.

<sql:dateParam>

Specifies an SQL date, time, or timestamp parameter for enclosing <sql:query> or <sql:update> actions

Syntax: ^[28]

^[28] Items in brackets are optional.

 <sql:dateParam value [type]/> 

Description:

Converts an instance of java.util.Date into an object suitable for an SQL date, time, or timestamp parameter and passes that object to an enclosing <sql:query> or <sql:update> action.

Attributes:

Attribute [a]	Type	Description
value	java.util.Date	A parameter corresponding to an SQL date, time, or timestamp.
type	String	The type of values that the value attribute represents; valid attributes are date , time , or timestamp . The default value is timestamp .

^[a] static dynamic

Constraints and Error Handling:

• If you specify a null value for the value attribute, the corresponding parameter will be set to the SQL value NULL .

In a Nutshell:

If you want to specify an SQL date, time, or timestamp parameter for <sql:query> or <sql:update> actions with an instance of java.util.Date , you must use <sql:dateParam> instead of <sql:param>. Depending on the value of the type attribute, <sql:dateParam> converts an instance of java.util.Date into an instance of java.sql.Date , java.sql.Time or java.sql.Timestamp and passes that object to its enclosing <sql:query> or <sql:update> action.

Exposed Classes and Interface

JSTL exposes two interfaces and one class for database access: Result and SQLExecutionTag (interfaces) and ResultSupport (class).The interfaces and the class are exposed by being placed in javax.servlet.jsp.jstl.sql . This section defines the interfaces and the class and also discusses the rationale for exposing them. Table 11.22 lists the interfaces and the class.

Table 11.22. Exposed Classes Interfaces and for Database Access

Name	Interface or Class	Description
Result	Interface	Represents a query result.
SQLExecutionTag	Interface	The interface implemented by <sql:query> and <sql:update>.
ResultSupport	Class	A support class for the Result interface.

The interfaces and the class listed above are described below.

Result

An interface for accessing query results

Definition:

 interface Result {      public java.util.SortedMap[] getRows()     public Object[][] getRowsByIndex()     public String[] getColumnNames()     public int getRowCount()     public boolean isLimitedByMaxRows() } 

Description:

The Result interface provides a simpler and more user friendly mechanism than java.sql.ResultSet for accessing database queries.

The result of a database query executed by <sql:query> is an object of type Result . See "Accessing Query Properties" on page 382 for more information about how you can access a result's properties in JSP pages.

SQLExecutionTag

The interface implemented by <sql:query> and <sql:update>

Definition:

 interface SQLExecutionTag {      public void addSQLParameter(Object value) } 

Description:

Both the <sql:query> and <sql:update> tag handlers implement the SQLExecutionTag interface. That interface allows <sql:query> and <sql:update> to receive SQL parameters.

The SQLExecutionTag interface is exposed (in other words, it resides in the javax.servlet.jsp.jstl.sql package) so that you can implement custom actions that pass SQL parameters to <sql:query> and <sql:update>. See "Implementing Database Custom Actions" on page 418 for more information about how you can implement such a custom tag.

ResultSupport

A support class for the Result interface

Definition:

 class ResultSupport {       public static Result toResult(java.sql.ResultSet rs)      public static Result toResult(java.sql.ResultSet rs, int maxRows) } 

Description:

The JSTL expert group thought that the java.sql.ResultSet interface was too difficult for page authors to work with, so they defined a simpler interface: javax.servlet.jsp.jstl.sql.Result . The ResultSupport class provides two methods to convert result sets into results. Those methods are used by the JSTL reference implementation, and the JSTL expert group thought that you might have some use for those methods also, so the ResultSupport class was placed in the javax.servlet.jsp.jstl.sql package.