Core JSTL Mastering the JSPT Standard Tag Library [Electronic resources] نسخه متنی

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.

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.

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.

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]

^[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:

^[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 mannerthey 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]

^[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:

^[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 Accessing Query Properties" on page 382 for more information about the use of that interface in practice.

The 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. 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]

^[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:

^[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 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]

^[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:

^[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.

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:

^[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.

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:

^[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.

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.