The Relational Database Tags (SQL) The relational database tags allow a Web application to access a SQL database. You must have a JDBC driver and know the correct URL to use to access this database. You can use the SQL tags to perform queries, updates, and use transactions. The SQL tags are intended primarily for rapid prototyping or very small Web applications. For most nontrivial Web applications, we suggest that you isolate your database access into tag libraries of your own. Chapter 11 shows how to do this. The <sql:dateParam> Tag If you are going to specify a parameter to the <sql:query> or <sql:update> tags and that parameter is a date, you must use the <sql:dateParam> tag. If the parameter is not a date, then use the <sql:param> tag. <sql:dateParam value="value" type="[timestamp|time|date]" /> The <sql:dateParam> tag has the following attributes: | value | Y | The value to be inserted for this parameter. | | type | N | Must be timestamp, time, or date. The default is timestamp. | The <sql:param> Tag If you are going to specify a parameter to the <sql:query> or <sql:update> tags and that parameter is any value but a date, you must use the <sql:param> tag. If the value is a date, then you should use the <sql:dateParam> 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:param> tag has one attribute: | value | N | The value to be inserted for this parameter. | The <sql:query> Tag A query, as defined by JSTL, is any SQL command that is going to return records. The most common SQL commands that return records are the SELECT SQL command and calls to stored procedures. Using the <sql:query> tag, you can submit a query and receive back a collection of records. You can then process these records using the core iteration tags, such as <c:forEach>. // 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 has 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. | | scope | N | Specifies the scope for the scoped variable referenced by the var attribute. Defaults to page. | | 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 <sql:setDataSource> Tag To use a <sql:update>, <sql:query>, or <sql:transaction> tag, you must have a data source. The <sql:setDataSource> tag is used to open a connection to a data source. You must know both the driver name and access URL for the data source. Consult the documentation associated with your database to see what these values are. <sql:setDataSource var="varName" [scope="{page|request|session|application}"] [driver="driverClassName"] [url="jdbcUrl"] [user="userName"]/> The <sql:setDataSource> tag has the following attributes: | dataSource | N | The data source that should be used. | | driver | N | The classname of the JDBC data source that should be used. | | scope | N | The scope of the scoped variable referenced by the attribute var. The default is page. | | url | N | The URL of the data source. | | password | N | The password used to establish the connection. | | user | N | The user that should be used to log into the data source. | | var | Y | The scoped variable that will hold the newly created data source. | The <sql:transaction> Tag Often, when updating a database, you must perform several updates to complete one transaction. Usually, it is preferable that if a single operation fails, the entire transaction will fail. This is the purpose of the <sql:transaction> tag. Any <sql:update> tags inside the body of the <sql:transaction> tag will operate as one transaction. If any one of these <sql:update> tags fails, the entire transaction fails. [isolation=isolationLevel] <sql:query> and <sql:update> statements </sql:transaction> The <sql:transaction> tag has the following attributes: | dataSource | N | Specifies the data source to be used with this transaction. | | isolationLevel | N | Specifies the isolation level for this transaction TRANSACTION_READ_COMMITTED, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_REPEATABLE_READ, or TRANSACTION_SERIALIZABLE). | The <sql:update> Tag JSTL defines an update as a SQL command that does not return a result set. SQL updates usually modify the database in some way, though this is not an absolute requirement. The changes made by the <sql:update> tag are immediate, unless the tag is being used as part of a <sql:transaction> block. // 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 has the following attributes: | dataSource | N | Specifies the data source to be used with this command. | | scope | N | Specifies the scope for the scoped variable referenced by the var attribute. Defaults to page. | | sql | N | Specifies the SQL command that should be executed. | | var | N | Specifies the scoped variable that will receive the results of this command. | | 
Print 
E-Mail 
Add Note 
Add Bookmark
