Oracle® Application Server Containers for J2EE JSP Tag Libraries and Utilities Reference
10g Release 2 (10.1.2) B14016-02 |
|
Previous |
Next |
This chapter describes JavaBeans and tags provided with OC4J for use in accessing a database from servlets and JSP pages.
The chapter consists of the following sections:
The OC4J product includes a set of JavaBeans that you can use to access a database. The following sections describe the beans:
Data-Access JavaBean Descriptions
Note: The JavaBeans described here are used by the tags discussed in "SQL Tags for Data Access". Generally speaking, these beans and tags can be used with non-Oracle databases, assuming you have appropriate JDBC driver classes; however, numerous features described below, as noted, are Oracle-specific. |
OC4J supplies a set of custom JavaBeans for database access. The following beans are included in the oracle.jsp.dbutil
package:
ConnBean
opens a database connection. This bean also supports data sources and connection pooling. See "Data-Access Support for Data Sources and Pooled Connections" for related information.
ConnCacheBean
uses the Oracle JDBC connection caching implementation for database connections. This requires JDBC 2.0.
DBBean
executes a database query. It also has its own connection mechanism, but does not support data sources.
CursorBean
provides general DML support for queries; UPDATE
, INSERT
, and DELETE
statements; and stored procedure calls.
This section presumes a working knowledge of Oracle JDBC. Consult the Oracle Database JDBC Developer's Guide and Reference as necessary.
To use the data-access JavaBeans, verify that the file ojsputil.jar
is installed and in your classpath. This file is provided with the OC4J installation. For XML-related methods and functionality, you will also need the file xsu12.jar
(for JDK 1.2.x or higher), which is provided with Oracle Application Server.
You will also need appropriate JDBC driver classes installed and in your classpath, such as ojdbc14.jar
for Oracle Database and JDK 1.4.
The data-access JavaBeans, as well as the data-access tag library, support the use of data sources to specify connection properties. This is also how support for connection pooling is implemented. This mechanism supports both Oracle connection objects and OC4J connection objects.
To use a data source in a JSP page, you must define the data source, its JNDI name, and its connection and pooling properties. In OC4J, do this in a <data-source>
element in the data-sources.xml
file. Here is an example:
<data-source class="oracle.jdbc.pool.OracleDataSource" name="jdbc/ejbpool/OracleDS" location="jdbc/ConnectionDS" ejb-location="jdbc/ejbpool/OracleDS" url="jdbc:oracle:thin:@myhost:1521/myservice" username="scott" password="tiger" min-connections="3" max-connections="50" wait-timeout="10" inactivity-timeout="30" />
It is advisable to use only the ejb-location
JNDI name in the JNDI lookup for an emulated data source. See the Oracle Application Server Containers for J2EE Services Guide for more information about data sources.
The following sections describe attributes and methods of the data-access JavaBeans—ConnBean
, ConnCacheBean
, DBBean
, and CursorBean
—and concludes with an example that uses a data source:
Use oracle.jsp.dbutil.ConnBean
to establish a simple database connection, one that uses no connection pooling or caching.
Note: For queries only, if you do not require a data source, it is simpler to useDBBean , which has its own connection mechanism.
|
ConnBean
has the following properties. The user
, password
, and URL
properties are not required if you use a data source.
dataSource
: JNDI name for a data source location
This is valid only for an environment that supports data sources. See "Data-Access Support for Data Sources and Pooled Connections" for information about how to set up a data source in OC4J.
user
: user ID for database schema
password
: user password
URL
: database connection string
stm
tCacheSize
: cache size for Oracle JDBC statement caching
Setting stmtCacheSize
enables Oracle JDBC statement caching.
exe
cuteBatch
: batch size for Oracle JDBC update batching
Setting executeBatch
enables Oracle JDBC update batching.
pr
eFetch
: number of statements to prefetch in Oracle JDBC row prefetching
Setting preFetch
enables Oracle JDBC row prefetching.
commitOnClose
: "true
" or "false
" for whether to execute commit
when the connection is closed
The value of commitOnClose
indicates whether an automatic commit
should be executed when the connection is closed. A "true
" setting results in a commit
; a "false
" setting results in a rollback
. Prior to Oracle9iAS Release 2, an automatic commit
was always executed, but in current releases the default is an automatic rollback
. The commitOnClose
property allows for backward compatibility to ease migration.
Be aware that there can be an application-wide commit-on-close
setting in the application web.xml
file, but the setting of the ConnBean
property is not automatically dependent on that setting. If a JSP pages uses ConnBean
instead of a dbOpen
tag, the value of the commit-on-close
context parameter should be retrieved and then explicitly set as the commitOnClose
value in the ConnBean
instance. For reference, here is a sample web.xml
entry that sets the commit-on-close
context parameter:
<context-param> <param-name>commit-on-close</param-name> <param-value>true</param-value> </context-param>
Note: See the Oracle Database JDBC Developer's Guide and Reference for information about statement caching, update batching, and row prefetching. |
ConnBean
provides the following setter and getter methods for these properties:
void setDataSource(String)
String getDataSource()
void setUser(String)
String getUser()
void setPassword(String)
String getPassword()
void setURL(String)
String getURL()
void setStmtCacheSize(int)
int getStmtCacheSize()
void setExecuteBatch(int)
int getExecuteBatch()
void setPreFetch(int)
int getPreFetch()
void setCommitOnClose(String)
String getCommitOnClose()
Note: As with any JavaBean you use in a JSP page, you can set any of theConnBean properties with a jsp:setProperty action instead of using the setter method directly.
|
Use the following methods to open and close a connection or to verify its status:
void connect()
Establish a database connection using ConnBean
property settings.
void close()
Close the connection and any open cursors.
boolean isConnectionClosed()
Determine if the connection is closed.
Use the following method to open a cursor and return a CursorBean
object:
CursorBean getCursorBean(int, String)
or:
CursorBean getCursorBean(int)
Input the following:
One of the following int
constants to specify the type of JDBC statement you want: CursorBean.PLAIN_STMT
for a Statement
object, CursorBean.PREP_STMT
for a PreparedStatement
object, or CursorBean.CALL_STMT
for a CallableStatement
object
A string specifying the SQL operation to execute (optional)
Alternatively, you can specify the SQL operation in the CursorBean
method call that executes the statement.
See "CursorBean for DML and Stored Procedures" for information about CursorBean
functionality.
Use oracle.jsp.dbutil.ConnCacheBean
to use the Oracle JDBC connection caching mechanism, using JDBC 2.0 connection pooling, for your database connections. Refer to the Oracle Database JDBC Developer's Guide and Reference for information about connection caching.
Note: To use data sources or simple connection objects, useConnBean instead.
|
ConnCacheBean
has the following properties:
user
: user ID for database schema
password
: user password
URL
: database connection string
maxLimit
: maximum number of connections allowed by this cache
minLimit
: minimum number of connections existing for this cache
If you use fewer than this number, there will also be connections in the idle pool of the cache.
stmtCacheSize
: cache size for Oracle JDBC statement caching
Setting stmtCacheSize
enables the Oracle JDBC statement caching feature. Refer to the Oracle Database JDBC Developer's Guide and Reference for information about Oracle JDBC statement caching features and limitations.
cacheScheme
: type of cache
This is indicated by one of the following int
constants.
DYNAMIC_SCHEME
: New pooled connections can be created above and beyond the maximum limit, but each one is automatically closed and freed as soon as the logical connection instance that it provided is no longer in use.
FIXED_WAIT_SCHEME
: When the maximum limit is reached, any new connection waits for an existing connection object to be released.
FIXED_RETURN_NULL_SCHEME
: When the maximum limit is reached, any new connection fails, returning null
, until connection objects have been released.
The ConnCacheBean
class includes the following getter and setter methods for its properties:
void setUser(String)
String getUser()
void setPassword(String)
String getPassword()
void setURL(String)
String getURL()
void setMaxLimit(int)
int getMaxLimit()
void setMinLimit(int)
int getMinLimit()
void setStmtCacheSize(int)
int getStmtCacheSize()
void setCacheScheme(int)
Specify ConnCacheBean.DYNAMIC_SCHEME
, ConnCacheBean.FIXED_WAIT_SCHEME
, or ConnCacheBean.FIXED_RETURN_NULL_SCHEME
.
int getCacheScheme()
Returns ConnCacheBean.DYNAMIC_SCHEME
, ConnCacheBean.FIXED_WAIT_SCHEME
, or ConnCacheBean.FIXED_RETURN_NULL_SCHEME
.
The ConnCacheBean
class also inherits properties and related getter and setter methods from the oracle.jdbc.pool.OracleDataSource
class. This provides getter and setter methods for the following properties: databaseName
, dataSourceName
, description
, networkProtocol
, portNumber
, serverName
, and driverType
. For information about these properties and their getter and setter methods, see the Oracle Database JDBC Developer's Guide and Reference.
Note: As with any JavaBean you use in a JSP page, you can set any of theConnCacheBean properties with a jsp:setProperty action instead of using the setter method directly.
|
Use the following methods to open and close a connection:
Connection getConnection()
Get a connection from the connection cache using ConnCacheBean
property settings.
void close()
Close all connections and any open cursors.
Although the ConnCacheBean
class does not support Oracle JDBC update batching and row prefetching directly, you can enable these features by calling the setDefaultExecuteBatch(int)
and setDefaultRowPrefetch(int)
methods of the Connection
object that you retrieve from the getConnection()
method. Alternatively, you can use the setExecuteBatch(int)
and setRowPrefetch(int)
methods of JDBC statement objects that you create from the Connection
object. (Update batching is supported only in prepared statements.) Refer to the Oracle Database JDBC Developer's Guide and Reference for information about these features.
Note: When you useConnCacheBean , use normal Connection object functionality to create and execute statement objects (unlike the case with ConnBean ).
|
Use oracle.jsp.dbutil.DBBean
to execute queries only.
Notes:
|
DBBean
has the following properties:
user
: user ID for database schema
password
: user password
URL
: database connection string
DBBean
provides the following setter and getter methods for these properties:
void setUser(String)
String getUser()
void setPassword(String)
String getPassword()
void setURL(String)
String getURL()
Note: As with any JavaBean you use in a JSP page, you can set any of theDBBean properties with a jsp:setProperty statement instead of using the setter method directly.
|
Use the following methods to open and close a connection:
void connect()
Establish a database connection using DBBean
property settings.
void close()
Close the connection and any open cursors.
Use either of the following methods to execute a query:
String getResultAsHTMLTable(String)
Input a string that contains the SELECT
statement. This method returns a string with the HTML commands necessary to output the result set as an HTML table. SQL column names (or aliases) are used for the table column headers.
String getResultAsXMLString(String)
Input a string with the SELECT
statement. This method returns the result set as an XML string, using SQL names (or aliases) for the XML tags.
Use oracle.jsp.dbutil.CursorBean
for SELECT
, UPDATE
, INSERT
, or DELETE
operations, or stored procedure calls, on a simple connection. It uses a previously defined ConnBean
object for the connection.
You can specify a SQL operation in a ConnBean
object getCursorBean()
call or through a call to one of the create()
, execute()
, or executeQuery()
methods of a CursorBean
object as described below.
CursorBean
supports scrollable and updatable cursors, update batching, row prefetching, and query timeout limits. For information about these Oracle JDBC features, see the Oracle Database JDBC Developer's Guide and Reference.
Note: To use connection caching, useConnCacheBean and normal Connection object functionality. Do not use CursorBean .
|
CursorBean
has the following properties:
executeBatch
: batch size for Oracle JDBC update batching
Setting this property enables Oracle JDBC update batching.
preFetch
: number of statements to prefetch in Oracle JDBC row prefetching
Setting this property enables Oracle JDBC row prefetching.
queryTimeout
: number of seconds for the driver to wait for a statement to execute before issuing a timeout
resultSetType
: scrollability of the result set
This is indicated by one of the following int
constants.
TYPE_FORWARD_ONLY
(default): Use this for a result set that can scroll only forward (using the next()
method) and cannot be positioned.
TYPE_SCROLL_INSENSITIVE
: Use this for a result set that can scroll forward or backward and can be positioned, but is not sensitive to underlying data changes.
TYPE_SCROLL_SENSITIVE
: Use this for a result set that can scroll forward or backward, can be positioned, and is sensitive to underlying data changes.
resultSetConcurrency
: updatability of the result set
This is indicated by one of the following int
constants.
CONCUR_READ_ONLY
(default): Use this for a result set that is read-only (cannot be updated).
CONCUR_UPDATABLE
: Use this for a result set that is updatable.
You can set these properties with the following methods to enable Oracle JDBC features, as desired:
void setExecuteBatch(int)
int getExecuteBatch()
void setPreFetch(int)
int getPreFetch()
void setQueryTimeout(int)
int getQueryTimeout()
void setResultSetConcurrency(int)
Specify CursorBean.CONCUR_READ_ONLY
or CursorBean.CONCUR_UPDATABLE
.
int getResultSetConcurrency()
Returns CursorBean.CONCUR_READ_ONLY
or CursorBean.CONCUR_UPDATABLE
.
void setResultSetType(int)
Specify CursorBean.TYPE_FORWARD_ONLY
, CursorBean.TYPE_SCROLL_INSENSITIVE
, or CursorBean.TYPE_SCROLL_SENSITIVE
.
int getResultSetType()
Returns CursorBean.TYPE_FORWARD_ONLY
, CursorBean.TYPE_SCROLL_INSENSITIVE
, or CursorBean.TYPE_SCROLL_SENSITIVE
.
Note: As with any JavaBean you use in a JSP page, you can set any of theCursorBean properties with a jsp:setProperty action instead of using the setter method directly.
|
To execute a query once a CursorBean
instance has been defined in a jsp:useBean
statement, you can use CursorBean
methods to create a cursor in one of two ways. Use the following methods to create the cursor and supply a connection in separate steps:
void create()
void setConnBean(ConnBean)
Alternatively, use the following method to combine the process into a single step:
void create(ConnBean)
Set up the ConnBean
object as described in "ConnBean for a Database Connection".
Use the following method to specify and execute a query (using a JDBC plain Statement
object behind the scenes):
ResultSet executeQuery(String)
Input a string that contains the SELECT
statement.
Alternatively, if you want to format the result set as an HTML table or XML string, use either of the following methods instead of executeQuery()
:
String getResultAsHTMLTable(String)
Returns a string with HTML statements to create an HTML table for the result set. Specify a string with the SELECT
statement.
String getResultAsXMLString(String)
Returns the result set data in an XML string. Specify a string with the SELECT
statement.
To execute an UPDATE
, INSERT
, or DELETE
statement once a CursorBean
instance has been defined in a jsp:useBean
action, you can use CursorBean
methods to create a cursor in one of two ways. Use the following methods to create the cursor, specifying a statement type as an integer and specifying a SQL statement as a string, and supply a connection:
void create(int, String)
void setConnBean(ConnBean)
Alternatively, use the following method to combine the process into a single step:
void create(ConnBean, int, String)
Set up the ConnBean
object as described in "ConnBean for a Database Connection".
The int
input takes one of the following constants to specify the type of JDBC statement you want: CursorBean.PLAIN_STMT
for a Statement
object, CursorBean.PREP_STMT
for a PreparedStatement
object, or CursorBean.CALL_STMT
for a CallableStatement
object. The String
input is to specify the SQL statement.
Use the following method to execute the INSERT
, UPDATE
, or DELETE
statement. You can ignore the boolean
return value.
boolean execute()
Alternatively, for update batching, use the following method, which returns the number of rows affected.
int executeUpdate()
Note: Specify the SQL operation either during statement creation or during statement execution, but not both. Theexecute() and executeUpdate() methods can optionally take a string to specify a SQL operation. This is also true of the create() method, as well as the getCursorBean() method in ConnBean .
|
Additionally, CursorBean
supports Oracle JDBC functionality such as registerOutParameter()
for callable statements, set
XXX
()
methods for prepared statements and callable statements, and get
XXX
()
methods for result sets and callable statements.
Use the following method to close the database cursor:
void close()
This following is a sample JSP page that uses ConnBean
with a data source to open a connection, then uses CursorBean
to execute a query.
<%@ page import="java.sql.*, oracle.jsp.dbutil.*" %> <jsp:useBean id="cbean" class="oracle.jsp.dbutil.ConnBean" scope="session"> <jsp:setProperty name="cbean" property="dataSource" value="<%=request.getParameter("datasource")%>"/> </jsp:useBean> <% try { cbean.connect(); String sql="SELECT ename, sal FROM scott.emp ORDER BY ename"; CursorBean cb = cbean.getCursorBean (CursorBean.PREP_STMT, sql); out.println(cb.getResultAsHTMLTable()); cb.close(); cbean.close(); } catch (SQLException e) { out.println("<P>" + "There was an error doing the query:"); out.println("<PRE>" + e + "</PRE>\n<P>"); } %>
OC4J includes a set of tags you can use in JSP pages to execute SQL commands to access a database. The following sections describe the tags:
Note: The custom SQL tag library provided with OC4J pre-dates the JavaServer Pages Standard Tag Library (JSTL) and has areas of duplicate functionality. For standards compliance, it is now generally advisable to use JSTL instead. See "Support for the JavaServer Pages Standard Tag Library".Oracle is not desupporting the existing library, however. For features in the custom library that are not yet available in JSTL, where there seems to be general usefulness, Oracle will try to have the features adopted into the JSTL standard as appropriate. |
OC4J supplies a custom tag library for SQL functionality, consisting of the following tags:
dbOpen
: Open a database connection. This tag also supports data sources and connection pooling. See "Data-Access Support for Data Sources and Pooled Connections" for related information.
dbClose
: Close a database connection.
dbQuery
: Execute a query.
dbCloseQuery
: Close the cursor for a query.
dbNextRow
: Process the rows of a result set.
dbExecute
: Execute any SQL statement (DML or DDL).
dbSetParam
: Set a parameter to bind into a dbQuery
or dbExecute
tag.
dbSetCookie
: Set a cookie.
Note the following requirements for using SQL tags:
You will need the appropriate JDBC driver file, such as ojdbc14.jar
for JDK 1.4, installed and in your classpath.
Verify that the file ojsputil.jar
is installed and in your classpath. This file is provided with the OC4J installation, in the "well-known" tag library directory.
The tag library descriptor,
sqltaglib.tld
, must be available to the application, and any JSP page using the library must have an appropriate taglib
directive. In an Oracle Application Server installation, the TLD is in ojsputil.jar
. The uri
value for sqltaglib.tld
is the following:
http://xmlns.oracle.com/j2ee/jsp/tld/ojsp/sqltaglib.tld
For general information about JSP tag library usage, including tag library descriptor files, taglib
directives, the well-known tag library directory, and the meaning of uri
values, refer to the Oracle Application Server Containers for J2EE Support for JavaServer Pages Developer's Guide.
Notes:
|
The following sections provide detailed syntax for the data-access tags and an example using dbOpen
and dbQuery
tags with a data source:
Example: Using dbOpen and dbQuery with a Data Source
Notes:
|
Use the dbOpen
tag to open a database connection for subsequent SQL operations through such tags as dbQuery
and dbExecute
. Do this by specifying a data source location, in which case connection caches are supported, or by specifying the user, password, and URL individually. See "Data-Access Support for Data Sources and Pooled Connections" for information about how to set up a data source in OC4J.
The implementation uses oracle.jsp.dbutil.ConnBean
instances. For simple connections, but not connection caches, you can optionally set ConnBean
properties such as stmtCacheSize
, preFetch
, and batchSize
to enable those Oracle JDBC features. See "ConnBean for a Database Connection" for more information.
The ConnBean
object for the connection is created in an instance of the tag-extra-info class of the dbOpen
tag. Refer to the Oracle Application Server Containers for J2EE Support for JavaServer Pages Developer's Guide for information about the standard JSP tag library framework and tag-extra-info classes.
<sql:dbOpen [ connId = "connection_id" ] [ scope = "page" | "request" | "scope" | "application" ] [ dataSource = "JNDI_name" ] [ user = "username" password = "password" URL = "databaseURL" ] [ commitOnClose = "true" | "false" ] > ... </sql:dbOpen>
Nested code that you want to execute through this connection can go into the tag body, between the dbOpen
start-tag and end-tag.
Note: You must set either thedataSource attribute or the user , password , and URL attributes. Optionally, you can use a data source to specify a URL, then use the dbOpen tag user and password attributes separately.
When a data source is used, and is for a cache of connections, the first use of the cache initializes it. If you specify the user and password through the |
connId
: Optionally use this to specify an ID name for the connection. You can then reference this ID in subsequent tags such as dbQuery
or dbExecute
. Alternatively, you can nest dbQuery
and dbExecute
tags inside the dbOpen
tag. You can also reference the connection ID in a dbClose
tag when you want to close the connection.
You can still specify a connection ID if you nest dbQuery
or dbExecute
tags inside the dbOpen
tag. In this case, the connection will be found through the connection ID. With the scope
attribute, it is possible to have multiple connections using the same connection ID but different scopes.
If you specify a connection ID, then the connection is not closed until you close it explicitly with a dbClose
tag. Without a connection ID, the connection is closed automatically when the dbOpen
end-tag is encountered.
scope
(used only with a connId
): Use this to specify the desired scope of the connection instance. The default is page
scope.
If you specify a scope setting in a dbOpen
tag, then you must specify the same scope setting in any other tag—dbQuery
, dbExecute
, or dbClose
—that uses the same connection ID.
dataSource
(required if you do not set the user
, password
, and URL
attributes): Optionally use this to specify the JNDI name of a data source for database connections. First set up the data source in the OC4J data-sources.xml
file. (See "Data-Access Support for Data Sources and Pooled Connections".) The dataSource
setting should correspond to the location
name, ejb-location
name, or pooled-location
name in a <data-source>
element in data-sources.xml
.
A data source must specify a URL setting, but does not have to specify a user/password pair. You can use the dbOpen
tag user
and password
attributes instead.
This attribute is supported only in OC4J environments.
Note: It is advisable to use only theejb-location JNDI name in the JNDI lookup for an emulated data source. See the Oracle Application Server Containers for J2EE Services Guide for more information about data sources.
|
user
(required if no user/password pair is specified through a data source): This is the user name for a database connection.
If a user name is specified through both a data source and the user
attribute, the user
attribute takes precedence. It is advisable to avoid such duplication, because conflicts could arise if the data source is a pooled connection with existing logical connections using a different user name.
password
(required if no user/password pair is specified through a data source): This is the user password for a database connection.
Note that you do not have to hardcode a password into the JSP page, which would be an obvious security concern. Instead, you can get the password and other parameters from the request
object, as follows:
<sql:dbOpen connId="conn1" user='<%=request.getParameter("user")%>' password='<%=request.getParameter("password")%>' URL="url" />
As with the user
attribute, if a password is specified through both a data source and the password
attribute, the password
attribute takes precedence.
URL
(required if no data source is specified): This is the URL for a database connection. If a URL is supplied through a data source, the dbOpen
tag URL
attribute is ignored.
commitOnClose
: Set this to "true
" for an automatic SQL commit when the connection is closed or goes out of scope. The default "false
" setting results in an automatic SQL rollback.
As a convenience, if you want to specify application-wide automatic commit
or rollback
behavior, set the parameter name commit-on-close
in the application web.xml
file, as in the following example:
<context-param> <param-name>commit-on-close</param-name> <param-value>true</param-value> </context-param>
The commitOnClose
setting in a dbOpen
tag takes precedence over the commit-on-close
setting in web.xml
.
Note: In previous releases, the behavior is always to commit automatically when the connection is closed. ThecommitOnClose attribute offers backward compatibility to simplify migration.
|
Use the dbClose
tag to close a connection associated with the optional connId
parameter specified in a dbOpen
tag. If connId
is not used in the dbOpen
tag, then the connection is closed automatically when the dbOpen
end-tag is reached; a dbClose
tag is not required.
<sql:dbClose connId = "connection_id" [ scope = "page" | "request" | "scope" | "application" ] />
connId
(required): This is the ID for the connection being closed, specified in the dbOpen
tag that opened the connection.
scope
: This is the scope of the connection instance. The default is "page
", but if the dbOpen
tag specified a scope other than page
, you must specify that same scope in the dbClose
tag.
Use the dbQuery
tag to execute a query, outputting the results either as a JDBC result set, HTML table, XML string, or XML DOM object. Place the SELECT
statement (one only) in the tag body, between the dbQuery
start-tag and end-tag.
This tag uses an oracle.jsp.dbutil.CursorBean
object for the cursor, so you can set properties such as the result set type, result set concurrency, batch size, and prefetch size, if desired. See "CursorBean for DML and Stored Procedures" for information about CursorBean
functionality.
For XML usage, this tag acts as an XML producer. See "XML Producers and XML Consumers" for more information. Also see "Example Using the transform and dbQuery Tags".
<sql:dbQuery [ queryId = "query_id" ] [ connId = "connection_id" ] [ scope = "page" | "request" | "scope" | "application" ] [ output = "HTML" | "XML" | "JDBC" ] [ maxRows = "number" ] [ skipRows = "number" ] [ bindParams = "value" ] [ toXMLObjName = "objectname" ] > ...SELECT statement (one only)... </sql:dbQuery>
Important:
|
queryId
: You can use this to specify an ID name for the cursor. This is required if you want to process the results using a dbNextRow
tag.
If the queryId
parameter is present, then the cursor is not closed until you close it explicitly with a dbCloseQuery
tag. Without a query ID, the cursor is closed automatically when the dbQuery
end-tag is encountered. This is not a request-time attribute, meaning it cannot take a JSP expression value.
connId
: This is the ID for a database connection, according to the connId
setting in the dbOpen
tag that opened the connection. If you do not specify connId
in a dbQuery
tag, then the tag must be nested within the body of a dbOpen
tag and will use the connection opened in the dbOpen
tag. This is not a request-time attribute.
scope
: This is the scope of the connection instance. The default is "page
", but if the associated dbOpen
tag specified a scope other than page
, you must specify that same scope in the dbQuery
tag. This is not a request-time attribute.
output
: This is the desired output format, one of the following.
HTML
specifies that the result set is to be output as an HTML table (default).
XML
specifies that the result set is to be output as an XML string, or an XML DOM object if an object name is specified in the toXMLObjName
attribute.
JDBC
specifies that the result set is to be output as a JDBC ResultSet
object that can be processed using the dbNextRow
tag to iterate through the rows.
maxRows
: This is the maximum number of rows of data to display. The default is all rows.
skipRows
: This is the number of data rows to skip in the query results before displaying results. The default is 0.
bindParams
: Use this to bind a parameter into the query. The following example is from an application that prompts the user to enter an employee number, using bindParams
to bind the specified value into the empno
field of the query:
<sql:dbQuery connId="con1" bindParams="empno"> select * from EMP where empno=? </sql:dbQuery>
Alternatively, you can set a parameter value with the dbSetParam
tag to bind it in through the bindParams
attribute. See "SQL dbSetParam Tag".
toXMLObjName
: Specify an XML object name if you want to output the results as an XML DOM object. To use this, you must also set output
to "XML
".
Use the dbCloseQuery
tag to close a cursor associated with the optional queryId
parameter specified in a dbQuery
tag. If queryId
is not specified in the dbQuery
tag, then the cursor is closed automatically when the dbQuery
end-tag is reached; a dbCloseQuery
tag is not required.
Use the dbNextRow
tag to process each row of a result set obtained in a dbQuery
tag and associated with the specified queryId
. Place the processing code in the tag body, between the dbNextRow
start-tag and end-tag. The body is executed for each row of the result set.
To use the dbNextRow
tag, the dbQuery
tag must set output
to "JDBC
" and specify a queryId
for the dbNextRow
tag to reference.
The result set object is created in an instance of the tag-extra-info class of the dbQuery
tag. Refer to the Oracle Application Server Containers for J2EE Support for JavaServer Pages Developer's Guide for information about the standard JSP tag library framework and tag-extra-info classes.
queryId
(required): This is the ID of the cursor containing the results to be processed, specified in the dbQuery
tag that opened the cursor.
The following example shows the combined use of a dbOpen
, dbQuery
, and dbNextRow
tag.
<sql:dbOpen connId="con1" URL="jdbc:oracle:thin:@myhost:1521/myservice" user="scott" password="tiger"> </sql:dbOpen> <sql:dbQuery connId="con1" output="jdbc" queryId="myquery"> select * from EMP </sql:dbQuery> <sql:dbNextRow queryId="myquery"> <%= myquery.getString(1) %> </sql:dbNextRow> <sql:dbCloseQuery queryId="myquery" /> <sql:dbClose connId="con1" />
Use the dbExecute
tag to execute a single DML or DDL statement. Place the statement in the tag body, between the dbExecute
start-tag and end-tag.
This tag uses an oracle.jsp.dbutil.CursorBean
object for the cursor. See "CursorBean for DML and Stored Procedures" for information about CursorBean
functionality.
<sql:dbExecute [ connId = "connection_id" ] [ scope = "page" | "request" | "scope" | "application" ] [ output = "yes" | "no" ] [ bindParams = "value" ] > ...DML or DDL statement (one only)... </sql:dbExecute >
Important:
|
connId
: This is the ID of a database connection, according to the connId
setting in the dbOpen
tag that opened the connection. If you do not specify connId
in a dbExecute
tag, then the tag must be nested within the body of a dbOpen
tag and will use the connection opened in the dbOpen
tag.
scope
: This is the scope of the connection instance. The default is "page
", but if the dbOpen
tag specified a scope other than page
, you must specify that same scope in the dbExecute
tag.
output
: If output="yes"
, then for DML statements the HTML string "number row[s] affected" will be output to the browser to notify the user how many database rows were affected by the operation. For DDL statements, the statement execution status will be printed. The default is "no
".
bindParams
: Use this to bind a parameter into the SQL statement. The following example is from an application that prompts the user to enter an employee number, using bindParams
to bind the specified value into the empno
field of the DELETE
statement:
<sql:dbExecute connId="con1" bindParams="empno"> delete from EMP where empno=? </sql:dbExecute>
Alternatively, you can set a parameter value with the dbSetParam
tag to bind it in through the bindParams
attribute. See the next section, "SQL dbSetParam Tag".
You can use this tag to set a parameter value to bind into a query, through the dbQuery
tag, or to bind into any other SQL operation, through the dbExecute
tag.
Note: For applications using the data-access tags, consider using thedbSetParam tag to supply only parameter values rather than textual completion of the SQL statement itself. This avoids the possibility of what is referred to as "SQL poisoning", where users might enter more SQL code in addition to the expected value.
|
<sql:dbSetParam name = "param_name" value = "param_value" [ scope = "page" | "request" | "scope" | "application" ] />
name
(required): This is the name of the parameter to set.
value
(required): This is the desired value of the parameter.
scope
: This is the scope of the bind parameter. The default is page
scope.
The following example uses a dbSetParam
tag to set the value of a parameter named id2
. This value is then bound into the SQL statement in the dbExecute
tag.
<sql:dbSetParam name="id2" value='<%=request.getParameter("id")%>' scope="session" /> Result: <HR> <sql:dbOpen dataSource="<%= dataSrcStr %>" > <sql:dbExecute output="yes" bindParams="id2 name job sal"> insert into emp(empno, ename, deptno, job, sal) values (?, ?, 20, ?, ?) </sql:dbExecute> </sql:dbOpen> <HR>
You can use this tag to set a cookie. The dbSetCookie
tag wraps functionality of the standard javax.servlet.http.Cookie
class.
<sql:dbSetCookie name = "cookie_name" [ value = "cookie_value" ] [ domain = "domain_name" ] [ comment = "comment" ] [ maxAge = "age" ] [ version = "protocol_version" ] [ secure = "true" | "false" ] [ path = "path" ] />
name
(required): This is the name of the cookie.
value
: This is the desired value of the cookie. Because it is permissible to have a null-value cookie, this attribute is not required.
domain
: This is the domain name for the cookie. The form of the domain name is according to the RFC 2019 specification.
comment
: This is for a comment describing the purpose of the cookie.
maxAge
: This is the maximum allowable age of the cookie, in seconds. Use a setting of "-1
" for the cookie to persist until the browser is shut down.
version
: This is the version of the HTTP protocol that the cookie complies with.
secure
: This informs the browser whether the cookie should be sent using a secure protocol, such as HTTPS.
path
: This specifies a file system path for the cookie, the location to which the client should return the cookie.
This section provides a sample JSP page that uses a dbOpen
tag with a data source to open a connection, then uses a dbQuery
tag to execute a query.
<%@ taglib uri="http://xmlns.oracle.com/j2ee/jsp/tld/ojsp/ sqltaglib.tld" prefix="sql" %> <HTML> <BODY> <sql:dbOpen dataSource='<%=request.getParameter("datasource") %>' connId="con1"> </sql:dbOpen> <sql:dbQuery connId="con1"> SELECT * FROM emp ORDER BY ename </sql:dbQuery> <sql:dbClose connId="con1" /> </BODY> </HTML>