JSTL database actions let you connect to a database, query a database, update a database, and execute database transactions. Overview of JSTL SQL ActionsTable 11.19 lists the JSTL database actions. Table 11.19. Database 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 SettingsThe following configuration settings are supported by JSTL for SQL actions:
The SQL_DATA_SOURCE configuration setting is listed in Table 11.20. Table 11.20. SQL_DATA_SOURCE
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
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
Syntax: [24]
<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:
Constraints and Error Handling:
In a Nutshell: You can do two things with <sql:setDataSource>:
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.
Syntax: [25]
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:
Constraints and Error Handling:
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.
Syntax: [26]
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:
Constraints and Error Handling:
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.
Syntax: [27]
<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:
Constraints and Error Handling:
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.
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:
Constraints and Error Handling:
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.
Syntax: [28]
<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:
Constraints and Error Handling:
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 InterfaceJSTL 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
The interfaces and the class listed above are described below.
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.
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.
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. |