B.4 Database Access Actions
The
<sql:dateParam>
action is used as a nested action for
<sql:query> and
<sql:update> to supply a date and time value
for a value placeholder. If a null value is
provided, the value is set to SQL NULL for the
placeholder. To ensure portability between different database
engines, this action must be used when setting values for
DATE, TIME, and
TIMESTAMP columns. The value must be of type java.util.Date or one of
the SQL specific subclasses: java.sql.Date,
java.sql.Time or
java.sql.Timestamp. If it's a
java.util.Date, the action converts it to the
specified subclass.
Syntax
<sql:dateParam value="parameterValue "
[type="timestamp |time|date"] />
Attributes
|
value
|
java.util.Date
|
Yes
|
The parameter value.
|
type
|
String
|
Yes
|
The SQL type the value should be converted to, if needed.
|
Example
<sql:update>
UPDATE Employee SET EmpDate = ? WHERE EmpId = ?
<sql:dateParam value="${empDate}" />
<sql:param value="${empId}" />
</sql:update>
The
<sql:param>
action is used as a nested action for
<sql:query> and
<sql:update> to supply a value for a value
placeholder. If a null value is provided, the
value is set to SQL NULL for the placeholder. To
ensure portability between different database engines, the value type
must be a supported type for the target column, for instance a
numeric value for an INT or
FLOAT column and a String value for a
CHAR or VARCHAR column. See
<sql:dateParam> for setting values for
DATE, TIME, and
TIMESTAMP columns.
Syntax 1: Without a body
<sql:param value="parameterValue" />
Syntax 2: With a body
<sql:param>
parameterValue
</sql:param>
Attributes
|
value
|
Object
|
Yes
|
The parameter value.
|
Example
<sql:update>
DELETE * FROM Employee WHERE Id = ?
<sql:param value="${empId}" />
</sql:update>
The
<sql:query>
action executes an SQL SELECT statement and saves
the result in a scoped variable. The statement may contain question
marks as placeholders for values assigned by nested
<sql:param> and
<sql:dateParam> actions. The action uses a connection provided by an
<sql:transaction> action, or�if not
part of a transaction�from the explicitly specified data source
or the data-source configuration setting. The number of rows to
retrieve can be limited explicitly or by the maximum rows
configuration setting.
Syntax 1: Without a body
<sql:query sql="sqlSelectStatement "
[dataSource="dataSource "]
[maxRows="maxRows "]
[startRow="index "]
var="var " [scope="page |request|session|application"] />
Syntax 2: With a body for parameters
<sql:query sql="sqlSelectStatement"
[dataSource="dataSource "]
[maxRows="maxRows "]
[startRow="index "]
var="var " [scope="page |request|session|application"]>
<sql:param> actions
</sql:query>
Syntax 3: With a body for the statement and parameters
<sql:query
[dataSource="dataSource "]
[maxRows="maxRows "]
[startRow="index "]
var="var " [scope="page |request|session|application"]>
sqlSelectStatement
<sql:param> actions
</sql:query>
Attributes
|
sql
|
String
|
Yes
|
The SQL statement.
|
dataSource
|
String or javax.sql.DataSource
|
Yes
|
The data source to use. If specified as a String,
it can be either a JNDI path or a list of JDBC parameters as
described for the data-source configuration setting.
|
maxRows
|
int
|
Yes
|
The maximum number of rows to include in the result. If omitted or
-1, all rows are included.
|
startRow
|
int
|
Yes
|
The 0-based index for the first row to include in the result.
|
var
|
String
|
No
|
The variable name. The type is javax.servlet.jsp.jstl.sql.Result.
|
scope
|
String
|
No
|
The variable scope.
|
Example
<sql:query var="result">
SELECT * FROM Employee WHERE Name = ?
<sql:param value="${param:empName}" />
</sql:query>
The
<sql:setDataSource>
action sets the data source configuration variable, or saves the data
source information in a scoped variable that can be used as input to
the other JSTL database actions. This action is primarily intended for prototyping and small, simple
applications. See Chapter 12 and Chapter 24 for alternative ways to make a data source
available.
Syntax
<sql:setDataSource
[dataSource="dataSource " |
url="url "
[driver="driverClassName "]
[user="username "]
[password="password "]]
[var="var "] [scope="page |request|session|application"] />
Attributes
|
dataSource
|
String or javax.sql.DataSource
|
Yes
|
The data source to expose. If specified as a
String, it can be either a JNDI path or a list of
JDBC parameters as described for the data-source configuration
setting.
|
url
|
String
|
Yes
|
The JDBC URL.
|
driver
|
String
|
Yes
|
The JDBC driver class name.
|
user
|
String
|
Yes
|
The database username.
|
password
|
String
|
Yes
|
The database user password.
|
var
|
String
|
No
|
The variable name. If omitted, the data source configuration variable
is set. The type is either javax.sql.DataSource or
String.
|
scope
|
String
|
No
|
The variable or configuration variable scope.
|
Example
<sql:setDataSource var="snapshot"
url="jdbc:odbc:snapshot"
driver="sun.jdbc.odbc.JdbcOdbcDriver" />
<sql:query dataSource="${snapshot}" sql="..." var="result" />
The
<sql:transaction>
action establishes a transaction context for a set of
<sql:query> and
<sql:update> actions. It ensures that the
database modifications performed by the nested actions are either
committed or rolled back if an exception is thrown by any nested
action. The action provides a connection to the nested database actions,
either from the explicitly specified data source or from the
data-source configuration setting.
Syntax
<sql:transaction [dataSource="dataSource"]
[isolation="read_committed|read_uncommitted|repeatable_read|
serializable"]>
<sql:query> and <sql:update> actions, and optionally other JSP elements
</sql:transaction>
Attributes
|
dataSource
|
String or javax.sql.DataSource
|
Yes
|
The data source to use. If specified as a String,
it can be either a JNDI path or a list of JDBC parameters as
described for the data-source configuration setting.
|
isolation
|
String
|
Yes
|
The transaction isolation level. If omitted, the isolation level the
data source has been configured with is used.
|
Example
<sql:transaction>
<sql:update>
UPDATE Account
SET Balance = Balance - ?
WHERE AccountNo = ?
<sql:param value="${amount}" />
<sql:param value="${fromAccount}" />
</sql:update>
<sql:update>
UPDATE Account
SET Balance = Balance + ?
WHERE AccountNo = ?
<sql:param value="${amount}" />
<sql:param value="${toAccount}" />
</sql:update>
</sql:transaction>
The
<sql:update>
action executes an SQL statement that updates the database, such as
an INSERT, UPDATE, or
DELETE statement, and optionally saves the number
of affected rows in a scoped variable. SQL DDL statements, such as
CREATE TABLE, can also be executed with this
action. The statement may contain question marks as placeholders for
values assigned by nested <sql:param> and
<sql:dateParam> actions. The action uses a connection provided by an
<sql:transaction> action, or�if not
part of a transaction�from the explicitly specified data source
or the data-source configuration setting.
Syntax 1: Without a body
<sql:update sql="sqlStatement "
[dataSource="dataSource "]
[var="var " [scope="page |request|session|application"]] />
Syntax 2: With a body for parameters
<sql:update sql="sqlStatement "
[dataSource="dataSource "]
[var="var " [scope="page |request|session|application"]]>
<sql:param> actions
</sql:update>
Syntax 3: With a body for the statement and parameters
<sql:update
[dataSource="dataSource "]
[var="var " [scope="page |request|session|application"]]>
sqlStatement
<sql:param> actions
</sql:update>
Attributes
|
sql
|
String
|
Yes
|
The SQL statement.
|
dataSource
|
String or javax.sql.DataSource
|
Yes
|
The data source to use. If specified as a String,
it can be either a JNDI path or a list of JDBC parameters as
described for the data-source configuration setting.
|
var
|
String
|
No
|
The variable name. The type is Integer.
|
scope
|
String
|
No
|
The variable scope.
|
Example
<sql:update>
DELETE * FROM Employee WHERE Id = ?
<sql:param value="${empId}" />
</sql:update>
| 
No comments:
Post a Comment