| So far, we have examined the <sql:setDataSource> tag. JSTL makes available many other tags to facilitate the programming of SQL data sources. Let's examine each of these tags, beginning with the <sql:query> tag. Using the <sql:query> Tag The <sql:query> tag is used to perform a query of the database. A query is a SQL command that causes data to return. Not all SQL commands function in this way. The SELECT SQL command is most commonly used with the <sql:query> tag. If you are going to execute a SQL statement that does not return data for example, INSERT, UPDATE, or DELETE you must use the <sql:update> tag. The <sql:query> tag takes three forms: // Syntax 1: Without body content <sql:query sql="sqlQuery" var="varName" [scope="{page|request|session|application}"] [dataSource="dataSource"] [maxRows="maxRows"] [startRow="startRow"]/> // Syntax 2: With a body to specify query arguments <sql:query sql="sqlQuery" var="varName" [scope="{page|request|session|application}"] [dataSource="dataSource"] [maxRows="maxRows"] [startRow="startRow"]> <sql:param> actions </sql:query> // Syntax 3: With a body to specify query and optional query parameters <sql:query var="varName" [scope="{page|request|session|application}"] [dataSource="dataSource"] [maxRows="maxRows"] [startRow="startRow"]> query optional <sql:param> actions </sql:query> The <sql:query> tag accepts the following attributes: | dataSource | N | Specifies the data source to be used with this query. | | maxRows | N | Specifies the maximum number of rows to be returned after startRow. | | scope | N | Specifies the scope for the scoped variable specified by the var attribute. This attribute defaults to page scope. | | sql | N | Specifies the SQL command that is to be executed. | | startRow | N | Specifies the starting row that should be returned in the result set. | | var | N | Specifies the scoped variable that will receive the results of this query. | The first syntax does not include a body. This allows you to specify a short SQL command as the sql attribute. If you are going to use longer SQL commands, it will be more practical to use the second syntax form, which lets you insert your SQL command into the body of the tag. This way, you can insert long SQL commands that include carriage returns to properly format the SQL. The third syntax allows you to specify optional parameters that will be inserted into the SQL. Just as in JSTL, you may insert question marks (?) into your SQL. The question marks will be filled in with the parameter data that you specify. To learn more about the parameters, see the section about the <c:param> tag that appears later in this chapter. You can control the number of rows returned by using the maxRows and startRow attributes. The maxRows attribute lets you specify the maximum number of rows that you would like returned. The startRow attribute lets you specify the row that you would like the results to begin with. Using these two attributes together, you can take subsections of data from a very large query. The query tag will return a collection of the retrieved records. This collection will be stored in the scoped variable specified by the var attribute. The records returned by the query may be iterated by using the <c:forEach> tag. The following code demonstrates this: <c:forEach var="row" items="${users.rows}"> <tr> <c:out value="${row.c_uid}"/><br/> </c:forEach> This code displays the field named c_uid for every record in the collection users. Using the <sql:update> Tag Not all SQL commands return rows of data. The <sql:update> tag is used to invoke a SQL command that does not return data. Examples of SQL commands that do not return data are the UPDATE, DELETE, and INSERT commands. The <sql:update> tag has three forms: // Syntax 1: Without body content <sql:update sql="sqlUpdate" [dataSource="DataSource"] [var="varName"] [scope="{page|request|session|application}"]/> // Syntax 2: With a body to specify update parameters <sql:update sql="sqlUpdate" [dataSource="DataSource"] [var="varName"] [scope="{page|request|session|application}"]> <sql:param> actions </sql:update> // Syntax 3: With a body to specify update statement and optional update parameters <sql:update [dataSource="DataSource"] [var="varName"] [scope="{page|request|session|application}"]> update statement optional <sql:param> actions </sql:update> The <sql:update> tag accepts these attributes: | dataSource | N | Specifies the data source to be used with this command. | | scope | N | Specifies the scope for the scoped variable specified by the var attribute. This attribute defaults to page scope. | | sql | N | Specifies the SQL command that is to be executed. | | var | N | Specifies the scoped variable that will receive the results of this command. | The first syntax does not include a body. This allows you to specify a short SQL command as the sql attribute. If you are going to use longer SQL commands, you should use the second syntax form, which lets you insert your SQL command into the body of the tag. This allows you to insert long SQL commands that may include carriage returns to properly format the SQL. The third syntax allows you to specify optional parameters that will be inserted into the SQL. Just as in JSTL, you can insert question marks (?) into your SQL. The question marks will be filled in with the parameter data that you specify. For more information about the parameters, see the section about the <c:param> tag that appears later in this chapter. Just as with the <sql:query> tag, the <sql:update> tag returns a variable specified by the var attribute. This time, the variable is an Integer object, not a result set. This integer specifies the number of rows affected by the SQL command. Using the <sql:transaction> Tag Often, one SQL command cannot completely accomplish the task that you need. This can become a problem if one of the SQL commands fails to execute. Consider the example of two UPDATE commands that are used to move money from one account to another. The first UPDATE command inserts the money into the new account, and the second UPDATE command removes the money from the source account. If the second UPDATE command fails to execute yet the first one did, the user will be left with extra money. To alleviate this problem, you should use a transaction. This will cause both commands to fail if just one of the commands actually failed. JSTL allows you to use transactions through the <sql:transaction> tag. You use this tag to specify a data source that you can use in calls to their SQL tags. There is one form of the <sql:transaction> tag: <sql:transaction [dataSource="DataSource"] [isolation=isolationLevel] <sql:query> and <sql:update> statements </sql:transaction> isolationLevel ::= "read_committed" | "read_uncommitted" | "repeatable_read" | "serializable" The attributes accepted by the <sql:transaction> tag are as follows: | dataSource | N | Specifies the data source to be used with this transaction. | | isolationLevel | N | Specifies the isolation level for this transaction. | The body of the <sql:transaction> tag is used to specify other <sql:update> tags that perform the component commands of the transaction. You are given one parameter that allows you to specify the isolation level. These four transaction isolation levels are the same as the isolation levels specified to the JDBC Connection class: -
TRANSACTION_READ_COMMITTED Dirty reads are prevented; nonrepeatable reads and phantom reads can occur. -
TRANSACTION_READ_UNCOMMITTED Dirty reads, nonrepeatable reads, and phantom reads can occur. -
TRANSACTION_REPEATABLE_READ Dirty reads and nonrepeatable reads are prevented; phantom reads can occur. -
TRANSACTION_SERIALIZABLE Dirty reads, nonrepeatable reads, and phantom reads are prevented. Using the <sql:param> Tag We have already briefly mentioned that you can use parameters with your SQL commands. You use the <sql:param> tag to represent these parameters. There are two forms of the <sql:param> tag: // Syntax 1: Parameter value specified in attribute "value" <sql:param value="value"/> // Syntax 2: Parameter value specified in the body content <sql:param> parameter value </sql:param> The <sql:dateParam> accepts one parameter: | value | N | The value to be inserted for this parameter. | The first syntax is the most common. In this form, you specify the value of the parameter by using the value attribute. The second syntax also lets you specify the value of this attribute in the body of the tag. The order of the parameter tags in a <sql:update> or <sql:query> tag is important. The question mark (?) parameters specified in the SQL command are replaced in the same order as the <sql:param> tags are encountered. The <sql:param> tag should be used with all data types except dates. To specify a date, you need to use the <sql:dateParam> tag, as we explain next. Using the <sql:dateParam> Tag To specify date parameters, you should use the <sql:dateParam> tag. There are two forms of the <sql:dateParam> tag: // Syntax 1: Parameter value specified in attribute "value" <sql:dateParam value="value" type="[timestamp|time|date]" /> The <sql:dateParam> accepts two parameters: | type | N | The type: timestamp, time, or date. The default value is timestamp. | | value | N | The value to be inserted for this parameter. | You specify the value of this parameter by using the value attribute. The second syntax also lets you specify the value of and type of this attribute in the body of the tag. If the time type is specified, only a time will be used. If the date type is specified, only a date will be used. To use both, you should specify timestamp. | 
Print 
E-Mail 
Add Note 
Add Bookmark
