Oracle JavaServer Pages Developer's Guide and Reference Release 8.1.7 Part Number A83726-01 |
|
The Oracle extensions documented in this section are implemented either through the Oracle JSP Markup Language (JML) sample tag library or through custom JavaBeans. These extensions are portable to any standard JSP environment. This includes the following:
To use any of the JML functionality, see "Overview of the JSP Markup Language (JML) Sample Tag Library".
Important:
To work around shortcomings for JSP usage in the Java primitive datatypes and java.lang
wrapper types (as discussed in "OracleJSP Extended Datatypes"), OracleJSP provides the following JavaBean classes in the oracle.jsp.jml
package to act as wrappers for the most common Java datatypes:
JmlBoolean
to represent a boolean
value
JmlNumber
to represent an int
value
JmlFPNumber
to represent a double
value
JmlString
to represent a String
value
Each of these classes has a single attribute, value
, and includes methods to get the value, set the value from input in various formats, test whether the value is equal to a value specified in any of several formats, and convert the value to a string.
Alternatively, instead of using the getValue()
and setValue()
methods, you can use the jsp:getProperty
and jsp:setProperty
tags, as with any other bean.
The following example creates a JmlNumber
instance called count
that has application
scope.
<jsp:useBean id="count" class="oracle.jsp.jml.JmlNumber" scope="application" />
Later, assuming that the value has been set elsewhere, you can access it as follows:
<h3> The current count is <%=count.getValue() %> </h3>
The following example creates a JmlNumber
instance called maxSize
that has request
scope, and sets it using setProperty
:
<jsp:useBean id="maxSize" class="oracle.jsp.jml.Number" scope="request" > <jsp:setProperty name="maxSize" property="value" value="<%= 25 %>" /> </jsp:useBean>
The remainder of this section documents the public methods of the four extended datatype classes, followed by an example.
A JmlBoolean
object represents a Java boolean
value.
The getValue()
and setValue()
methods get or set the bean's value
property as a Java boolean
value.
The setTypedValue()
method has several signatures and can set the bean's value
property from a string (such as "true" or "false"), a java.lang.Boolean
value, a Java boolean
value, or a JmlBoolean
value. For the string input, conversion of the string is performed according to the same rules as for the standard java.lang.Boolean.valueOf()
method.
void setTypedValue(String)
void setTypedValue(Boolean)
void setTypedValue(boolean)
void setTypedValue(JmlBoolean)
The equals()
method tests whether the bean's value
property is equal to the specified Java boolean
value.
The typedEquals()
method has several signatures and tests whether the bean's value
property has a value equivalent to a specified string (such as "true" or "false"), java.lang.Boolean
value, or JmlBoolean
value.
The toString()
method returns the bean's value
property as a java.lang.String
value, either "true" or "false".
A JmlNumber
object represents a 32-bit number equivalent to a Java int
value.
The getValue()
and setValue()
methods get or set the bean's value
property as a Java int
value.
The setTypedValue()
method has several signatures and can set the bean's value
property from a string, a java.lang.Integer
value, a Java int
value, or a JmlNumber
value. For the string input, conversion of the string is performed according to the same rules as for the standard java.lang.Integer.decode()
method.
void setTypedValue(String)
void setTypedValue(Integer)
void setTypedValue(int)
void setTypedValue(JmlNumber)
The equals()
method tests whether the bean's value property is equal to the specified Java int
value.
The typedEquals()
method has several signatures and tests whether the bean's value
property has a value equivalent to a specified string (such as "1234"), java.lang.Number
value, or JmlNumber
value.
The toString()
method returns the bean's value
property as an equivalent java.lang.String
value (such as "1234"). This method has the same functionality as the standard java.lang.Integer.toString()
method.
A JmlFPNumber
object represents a 64-bit floating point number equivalent to a Java double
value.
The getValue()
and setValue()
methods get or set the bean's value
property as a Java double
value.
The setTypedValue()
method has several signatures and can set the bean's value
property from a string (such as "3.57"), a java.lang.Integer
value, a Java int
value, a java.lang.Float
value, a Java float
value, a java.lang.Double
value, a Java double
value, or a JmlFPNumber
value. For the string input, conversion of the string is according to the same rules as for the standard java.lang.Double.valueOf()
method.
void setTypedValue(String)
void setTypedValue(Integer)
void setTypedValue(int)
void setTypedValue(Float)
void setTypedValue(float)
void setTypedValue(Double)
void setTypedValue(double)
void setTypedValue(JmlFPNumber)
The equals()
method tests whether the bean's value
property is equal to the specified Java double
value.
The typedEquals()
method has several signatures and tests whether the bean's value
property has a value equivalent to a specified string (such as "3.57"), java.lang.Integer
value, Java int
value, java.lang.Float
value, Java float
value, java.lang.Double
value, Java double
value, or JmlFPNumber
value.
boolean typedEquals(String)
boolean typedEquals(Integer)
boolean typedEquals(int)
boolean typedEquals(Float)
boolean typedEquals(float)
boolean typedEquals(Double)
boolean typedEquals(JmlFPNumber)
The toString()
method returns the bean's value property as a java.lang.String
value (such as "3.57"). This method has the same functionality as the standard java.lang.Double.toString()
method.
A JmlString
object represents a java.lang.String
value.
The getValue()
and setValue()
methods get or set the bean's value
property as a java.lang.String
value. If the input in a setValue()
call is null, then the value property is set to an empty (zero-length) string.
The toString()
method is functionally equivalent to the getValue()
method.
The setTypedValue()
method sets the bean's value
property according to the specified JmlString
value. If the JmlString
value is null, then the value property is set to an empty (zero-length) string.
The isEmpty()
method tests whether the bean's value
property is an empty (zero-length) string: ""
The equals()
method has two signatures and tests whether the bean's value
property is equal to a specified java.lang.String
value or JmlString
value.
This example illustrates use of JML datatype JavaBeans for management of simple datatypes at scope. The page declares four session objects--one for each JML type. The page presents a form that allows you to enter values for each of these types. Once new values are submitted, the form displays both the new values and the previously set values. In the process of generating this output, the page updates the session objects with the new form values.
<jsp:useBean id = "submitCount" class = "oracle.jsp.jml.JmlNumber" scope = "session" /> <jsp:useBean id = "bool" class = "oracle.jsp.jml.JmlBoolean" scope = "session" > <jsp:setProperty name = "bool" property = "value" param = "fBoolean" /> </jsp:useBean> <jsp:useBean id = "num" class = "oracle.jsp.jml.JmlNumber" scope = "session" > <jsp:setProperty name = "num" property = "value" param = "fNumber" /> </jsp:useBean> <jsp:useBean id = "fpnum" class = "oracle.jsp.jml.JmlFPNumber" scope = "session" > <jsp:setProperty name = "fpnum" property = "value" param = "fFPNumber" /> </jsp:useBean> <jsp:useBean id = "str" class = "oracle.jsp.jml.JmlString" scope = "session" > <jsp:setProperty name = "str" property = "value" param = "fString" /> </jsp:useBean> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1"> <META NAME="GENERATOR" Content="Visual Page 1.1 for Windows"> <TITLE>OracleJSP Extended Datatypes Sample</TITLE> </HEAD> <BODY BACKGROUND="images/bg.gif" BGCOLOR="#FFFFFF"> <% if (submitCount.getValue() > 1) { %> <h3> Last submitted values </h3> <ul> <li> bool: <%= bool.getValue() %> <li> num: <%= num.getValue() %> <li> fpnum: <%= fpnum.getValue() %> <li> string: <%= str.getValue() %> </ul> <% } if (submitCount.getValue() > 0) { %> <jsp:setProperty name = "bool" property = "value" param = "fBoolean" /> <jsp:setProperty name = "num" property = "value" param = "fNumber" /> <jsp:setProperty name = "fpnum" property = "value" param = "fFPNumber" /> <jsp:setProperty name = "str" property = "value" param = "fString" /> <h3> New submitted values </h3> <ul> <li> bool: <jsp:getProperty name="bool" property="value" /> <li> num: <jsp:getProperty name="num" property="value" /> <li> fpnum: <jsp:getProperty name="fpnum" property="value" /> <li> string: <jsp:getProperty name="str" property="value" /> </ul> <% } %> <jsp:setProperty name = "submitCount" property = "value" value = "<%= submitCount.getValue() + 1 %>" /> <FORM ACTION="index.jsp" METHOD="POST" ENCTYPE="application/x-www-form-urlencoded"> <P> <pre> boolean test: <INPUT TYPE="text" NAME="fBoolean" VALUE="<%= bool.getValue() %>" > number test: <INPUT TYPE="text" NAME="fNumber" VALUE="<%= num.getValue() %>" > fpnumber test: <INPUT TYPE="text" NAME="fFPNumber" VALUE="<%= fpnum.getValue() %>" > string test: <INPUT TYPE="text" NAME="fString" VALUE= "<%= str.getValue() %>" > </pre> <P> <INPUT TYPE="submit"> </FORM> </BODY> </HTML>
JSP technology can be used to produce dynamic XML pages as well as dynamic HTML pages. OracleJSP supports the use of XML and XSL technology with JSP pages in two ways:
Additionally, the oracle.xml.sql.query.OracleXMLQuery
class is provided with Oracle8i as part of the XML-SQL utility for XML functionality in database queries. This class requires file xsu12.jar
(for JDK 1.2.x) or xsu111.jar
(for JDK 1.1.x), which is also required for XML functionality in the OracleJSP database-access JavaBeans, and which is provided with Oracle8i release 8.1.7.
For a JSP sample using OracleXMLQuery
, see "XML Query--XMLQuery.jsp".
For information about the OracleXMLQuery
class and other XML-SQL utility features, refer to the Oracle8i Application Developer's Guide - XML.
JSP tags, such as <%...%>
for scriptlets, <%!...%>
for declarations, and <%=...%>
for expressions, are not syntactically valid within an XML document. Sun Microsystems addressed this in the JavaServer Pages Specification, Version 1.1 by defining equivalent JSP tags using syntax that is XML-compatible. This is implemented through a standard DTD that you can specify within a jsp:root
start tag at the beginning of an XML document.
This functionality allows you, for example, to write XML-based JSP pages in an XML authoring tool.
OracleJSP does not use this DTD directly or require you to use a jsp:root
tag, but the OracleJSP translator includes logic to recognize the alternative syntax specified in the standard DTD. Table 5-1 documents this syntax.
JSP action tags, such as jsp:useBean
, for the most part already use syntax that complies with XML. Changes due to quoting conventions or for request-time attribute expressions may be necessary, however.
Many uses of XML and XSL for dynamic pages require an XSL transformation to occur in the server before results are returned to the client.
OracleJSP provides two synonymous JML tags to simplify this process. Use either the JML transform
tag or the JML styleSheet
tag (their effects are identical), as in the following example:
<jml:transform href="xslRef" > ...Tag body contains regular JSP commands and static text that produce the XML code that the stylesheet is to be applies to... </jml:transform >
(The jml:
prefix is used by convention, but you can specify any prefix in your taglib
directive.)
Important: If you will use any JML tags, refer to "Overview of the JSP Markup Language (JML) Sample Tag Library". |
Note the following regarding the href
parameter:
http://
host[:port]/yourpath
), an application-relative JSP reference (starting with "/"), or a page-relative JSP reference (not starting with "/"). See "Indirectly Requesting a JSP Page" for information about application-relative and page-relative paths.
href
is a static Java string. However, you can use standard JSP expression syntax to provide a dynamically computed value.
Typically, you would use the transform
or styleSheet
tag to transform an entire page. However, the tag applies only to what is in its body, between its start and end tags. Therefore, you can have distinct XSL blocks within a page, each block bounded by its own transform
or styleSheet
tag set, specifying its own href
pointer to the appropriate stylesheet.
This section provides a sample XSL stylesheet and a sample JSP page that uses the jml:transform
tag to filter its output through the stylesheet. (This is a simplistic example--the XML in the page is static. A more realistic example might use the JSP page to dynamically generate all or part of the XML before performing the transformation.)
<?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="page"> <html> <head> <title> <xsl:value-of select="title"/> </title> </head> <body bgcolor="#ffffff"> <xsl:apply-templates/> </body> </html> </xsl:template> <xsl:template match="title"> <h1 align="center"> <xsl:apply-templates/> </h1> </xsl:template> <xsl:template match="paragraph"> <p align="center"> <i> <xsl:apply-templates/> </i> </p> </xsl:template> </xsl:stylesheet>
<%@ page session = "false" %> <%@ taglib uri="/WEB-INF/jmltaglib.tld" prefix="jml" %> <jml:transform href="style/hello.xsl" > <page> <title>Hello</title> <content> <paragraph>This is my first XML/XSL file!</paragraph> </content> </page> </jml:transform>
This example results in the following output:
OracleJSP supplies a set of custom JavaBeans for accessing an Oracle database. The following beans are included in the oracle.jsp.dbutil
package:
ConnBean
opens a simple database connection.
ConnCacheBean
uses Oracle's connection caching implementation for database connections. (This requires JDBC 2.0.)
DBBean
executes a database query.
CursorBean
provides general DML support for queries; UPDATE
, INSERT
, and DELETE
statements; and stored procedure calls.
For examples using these beans, see "Database-Access JavaBean Samples".
All four beans implement the OracleJSP JspScopeListener
interface for event notification. See "OracleJSP Event Handling--JspScopeListener".
This section presumes a working knowledge of Oracle JDBC. Consult the Oracle8i JDBC Developer's Guide and Reference as necessary.
Use oracle.jsp.dbutil.ConnBean
to establish a simple database connection (one that uses no connection pooling or caching).
ConnBean
has the following properties:
user
(user ID for database schema)
password
(user password)
URL
(database connection string)
stmtCacheSize
(cache size for Oracle JDBC statement caching)
Setting stmtCacheSize
enables the Oracle JDBC statement caching feature. See "JDBC Statement Caching" for a brief overview of statement caching features and limitations.
executeBatch
(batch size for Oracle JDBC update batching)
Setting executeBatch
enables Oracle JDBC update batching. See "Update Batching" for a brief overview of this feature.
preFetch
(number of statements to prefetch in Oracle JDBC row prefetching)
Setting preFetch
enables Oracle JDBC row prefetching. Refer to "Row Prefetching" for a brief overview of this feature.
ConnBean
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()
void setStmtCacheSize(int)
int getStmtCacheSize()
void setExecuteBatch(int)
int getExecuteBatch()
void setPreFetch(int)
int getPreFetch()
Use the following methods to open and close a connection:
void connect()
--Establish a database connection using ConnBean
property settings.
void close()
--Close the connection and any open cursors.
Use the following method to open a cursor and return a CursorBean
object:
or:
CursorBean getCursorBean(int)
Input 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)
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. For a brief overview of connection caching, see "Database Connection Caching".
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 are using fewer than this number, then 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. See "JDBC Statement Caching" for a brief overview of Oracle JDBC statement caching features and limitations.
cacheScheme
(type of cache):
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 (null
is returned) until connection objects have been released.
The ConnCacheBean
class supports methods defined in the Oracle JDBC OracleConnectionCacheImpl
class, including 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 Oracle8i JDBC Developer's Guide and Reference.
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). See "Update Batching" and "Row Prefetching" for brief overviews of these features.
Use oracle.jsp.dbutil.DBBean
to execute queries only.
DBBean
has the following properties:
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()
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 with 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 Oracle8i JDBC Developer's Guide and Reference, release 8.1.6 or later.
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 result set):
TYPE_FORWARD_ONLY
(default)--A result set that can scroll only forward (using the next()
method) and is not positionable.
TYPE_SCROLL_INSENSITIVE
--A result set that can scroll forward or backward and is positionable, but is not sensitive to underlying database changes.
TYPE_SCROLL_SENSITIVE
--A result set that can scroll forward or backward, is positionable, and is sensitive to underlying database changes.
See the Oracle8i JDBC Developer's Guide and Reference for information about result set scrollability types.
resultSetConcurrency
(updatability of result set):
CONCUR_READ_ONLY
(default)--A result set that is read-only (cannot be updated).
CONCUR_UPDATABLE
--A result set that is updatable.
See the Oracle8i JDBC Developer's Guide and Reference for information about updatable result sets.
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
.
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. You can use the following methods to create the cursor and supply a connection in separate steps:
Or you can combine the process into a single step:
(Set up the ConnBean
object as described in "ConnBean for a Database Connection".)
Then use the following method to specify and execute a query. (This uses a JDBC plain Statement
object behind the scenes.)
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. You can use the following methods to create the cursor (specifying a statement type as an integer and SQL statement as a string) and supply a connection:
Or you can combine the process into a single step:
(Set up the ConnBean
object as described in "ConnBean for a Database Connection".)
The int
input is to specify 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.
Then use the following method to execute the INSERT
, UPDATE
, or DELETE
statement. (You can ignore the boolean
return value.)
Or for update batching, use the following method, which returns the number of rows affected. (See below for how to enable update batching.)
int executeUpdate()
Additionally, CursorBean
supports Oracle JDBC statement and result set functionality such as the registerOutParameter()
method, setXXX()
methods, and getXXX()
methods.
Use the following method to close the database cursor:
With release 8.1.7, OracleJSP supplies a custom tag library for SQL functionality (separate from the JML custom tag library).
The following tags are provided:
dbOpen
--Open a database connection.
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).
These tags are described in the following subsections. For examples, see "SQL Tag Examples".
Note the following requirements for using SQL tags:
ojsputil.jar
and include it in your classpath. This file is provided with the OracleJSP installation.
sqltaglib.tld
, is deployed with the application and is in the location specified in the taglib
directives of your JSP pages, such as in the following example:
<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %>
For general information about JSP 1.1 tag library usage, including tag library description files and taglib
directives, see "Standard Tag Library Framework".
Use the dbOpen
tag to open a database connection.
<sql:dbOpen [ connId="connection-id" ] user="username" password="password" URL="databaseURL" > ... </sql:dbOpen>
Nested code that you want to execute through this connection can go into the tag body, between the dbOpen
start and end tags. (See "SQL Tag Examples".) If you use the optional connId
parameter to set a connection identifier, then code to execute through this connection can reference the connection identifier and does not have to be between the dbOpen
start and end tags. (The connection identifier can be any arbitrary string.)
Note that you do not have to hardcode a password into the JSP page (which would be a security concern). Instead, you can get it and other parameters from the request
object, as follows:
<sql:dbOpen connId="conn1" user=<%=request.getParameter("user")%> password=<%=request.getParameter("password")%> URL="url" />
(In this example you do not need a tag body for code that will use this connection; statements using the connection can reference it through the conn1
value of connId
.)
If you set a connection identifier, then the connection is not closed until you close it explicitly with a dbClose
tag. Without a connection identifier, the connection is closed automatically when the </sql:dbOpen>
end tag is encountered.
This tag uses a ConnBean
object for the connection. You can optionally set the additional ConnBean
properties stmtCacheSize
, preFetch
, and batchSize
to enable those Oracle JDBC features. See "ConnBean for a Database Connection" for more information.
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; no dbClose
tag is required.
<sql:dbClose connId="connection-id" />
Note:
In an OracleJSP environment, you can have the connection closed automatically with session-based event handling through the Oracle |
Use the dbQuery
tag to execute a query, outputting the result either as a JDBC result set, HTML table, or XML string. Place the SELECT
statement (one only) in the tag body, between the dbQuery
start and end tags.
<sql:dbQuery [ queryId="query-id" ] [ connId="connection-id" ] [ output="HTML|XML|JDBC"] > ...SELECT statement (one only)... </sql:dbQuery>
All parameters of this tag are optional, depending on your intended uses as described below.
You must use the queryId
parameter to set a query identifier if you want to process the result set using a dbNextRow
tag. The queryId
can be any arbitrary string.
Additionally, if the queryId
parameter is present, then the cursor is not closed until you close it explicitly with a dbCloseQuery
tag. Without a query identifier, the cursor is closed automatically when the </sql:dbQuery>
end tag is encountered.
If connId
is not specified, then dbQuery
must be nested within the body of a dbOpen
tag and will use the connection opened in the dbOpen
tag.
For the output type:
HTML
puts the result set into an HTML table (default).
XML
puts the result set into an XML string.
JDBC
puts the result set into a JDBC ResultSet
object that can be processed using the dbNextRow
tag to iterate through the rows.
This tag uses a CursorBean
object for the cursor. See "CursorBean for DML and Stored Procedures" for information about CursorBean
functionality.
Use the dbCloseQuery
tag to close a cursor associated with the optional queryId
parameter specified in a dbQuery
tag. If queryId
is not used in the dbQuery
tag, then the cursor is closed automatically when the dbQuery
end tag is reached; no dbCloseQuery
tag is required.
<sql:dbCloseQuery queryId="query-id" />
Note:
In an OracleJSP environment, you can have the cursor closed automatically with session-based event handling through the Oracle |
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 and end tags. The body is executed for each row of the result set.
For you to use the dbNextRow
tag, the dbQuery
tag must specify output=JDBC
, and specify a queryId
for the dbNextRow
tag to reference.
<sql:dbNextRow queryId="query-id" > ...Row processing... </sql:dbNextRow >
The result set object is created in an instance of the tag-extra-info class of the dbQuery
tag (see "Tag Library Description Files" for information about tag-extra-info classes).
Use the dbExecute
tag to execute any DML or DDL statement (one only). Place the statement in the tag body, between the dbExecute
start and end tags.
<sql:dbExecute [connId="connection-id"] [output="yes|no"] > ...DML or DDL statement (one only)... </sql:dbExecute >
If you do not specify connId
, then you must nest dbExecute
within the body of a dbOpen
tag and use the connection opened in the dbOpen
tag.
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 setting is no
.
This tag uses a CursorBean
object for the cursor. See "CursorBean for DML and Stored Procedures" for information about CursorBean
functionality.
The following examples show how to use the OracleJSP SQL tags. (To run them yourself, you will need to set the URL, user name, and password appropriately.)
<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %> <HTML> <HEAD> <TITLE>A simple example with open, query, and close tags</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <HR> <sql:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger" connId="con1"> </sql:dbOpen> <sql:dbQuery connId="con1"> select * from EMP </sql:dbQuery> <sql:dbClose connId="con1" /> <HR> </BODY> </HTML>
<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %> <HTML> <HEAD> <TITLE>Nested Tag with Query inside Open </TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <HR> <sql:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger"> <sql:dbQuery> select * from EMP </sql:dbQuery> </sql:dbOpen> <HR> </BODY> </HTML>
<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %> <HTML> <HEAD> <TITLE>A simple tagLib with XML output</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <HR> <sql:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger"> <sql:dbQuery output="xml"> select * from EMP </sql:dbQuery> </sql:dbOpen> <HR> </BODY> </HTML>
<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %> <HTML> <HEAD> <TITLE>Result Set Iteration Sample </TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <HR> <sql:dbOpen connId="con1" URL="jdbc:oracle:thin:@dlsun991:1521:816" 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" /> <HR> </BODY> </HTML>
This example uses an HTML form to let the user specify what kind of DML or DDL statement to execute.
<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="sql" %> <HTML> <HEAD><TITLE>DML Sample</TITLE></HEAD> <FORM METHOD=get> <INPUT TYPE="submit" name="drop" VALUE="drop table test_table"><br> <INPUT TYPE="submit" name="create" VALUE="create table test_table (col1 NUMBER)"><br> <INPUT TYPE="submit" name="insert" VALUE="insert into test_table values (1234)"><br> <INPUT TYPE="submit" name="select" VALUE="select * from test_table"><br> </FORM> <BODY BGCOLOR="#FFFFFF"> Result: <HR> <sql:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger"> <% if (request.getParameter("drop")!=null) { %> <sql:dbExecute output="yes"> drop table test_table </sql:dbExecute> <% } %> <% if (request.getParameter("create")!=null) { %> <sql:dbExecute output="yes"> create table test_table (col1 NUMBER) </sql:dbExecute> <% } %> <% if (request.getParameter("insert")!=null) { %> <sql:dbExecute output="yes"> insert into test_table values (1234) </sql:dbExecute> <% } %> <% if (request.getParameter("select")!=null) { %> <sql:dbQuery> select * from test_table </sql:dbQuery> <% } %> </sql:dbOpen> <HR> </BODY> </HTML>
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|