9.1 Overview

9.1 Overview In this section we introduce the JSTL SQL actions and the configuration settings used in conjunction with those actions. SQL Actions JSTL provides six SQL actions that let you do the following: Connect to a database Query a database and access query results Perform database queries with prepared statements Update a database Execute database transactions The JSTL SQL actions are listed in Table 9.1. The <sql:setDataSource> action lets you specify a data source for your database. You can specify that data source as an instance of javax.sql.DataSource, or as a string representing either a Java Naming and Directory Interface (JNDI) relative path or JDBC parameters. You can store that data source in the SQL_DATA_SOURCE configuration setting or in a scoped variable. The <sql:query> action queries a database and stores the result in a scoped variable. The <sql:query> action lets you use prepared statements with SQL query parameters and also lets you limit the number of rows in a query. The <sql:update> action updates a database (by inserting, updating, or deleting rows) or modifies one (by creating, altering, or dropping tables) and lets you store the number of rows affected by the update in a scoped variable. Like <sql:query>, <sql:update> can execute prepared statements. Table 9.1. JSTL 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. The <sql:transaction> action defines a database transaction with nested <sql:query> and <sql:update> actions. If one of the nested queries or updates fails, <sql:transaction> rolls back the entire transaction and throws an exception; otherwise, <sql:transaction> commits the transaction. The <sql:param> and <sql:dateParam> actions supply SQL parameters for prepared statements to <sql:query> or <sql:update> actions. Configuration Settings JSTL uses two configuration settings in conjunction with the SQL actions: SQL_DATA_SOURCE and SQL_MAX_ROWS.[2] You can use the former to specify a data source, and the latter to limit the number of rows returned by database queries. [2] See "Configuration Settings" on page 230 for more information about configuration settings. The SQL_DATA_SOURCE configuration setting is listed in Table 9.2. The SQL_DATA_SOURCE configuration setting is used by the <sql:query>, <sql:update>, and <sql:transaction> actions. You can set that configuration setting in the deployment descriptor, in a business component with the Config class, or with the <sql:setDataSource> action. See "How JSTL Locates Data Sources" on page 363 for more information about how the SQL_DATA_SOURCE configuration setting is used, and see "Creating Data Sources" on page 365 for more information about how you can set that configuration setting. Table 9.2. The SQL_DATA_SOURCE Configuration Setting 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_MAX_ROWS configuration setting is listed in Table 9.3. Table 9.3. The SQL_MAX_ROWS Configuration Setting 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 is not set by any JSTL actions, but you can set that configuration setting in the deployment descriptor or in a business component with the Config class. See "Using <sql:query>" on page 378 for more information about the SQL_MAX_ROWS configuration setting.