Skip Headers

Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities Reference
Release 2 (9.0.2)
Part Number A95883-01
Go To Documentation Library
Home		 Go To Product List
Solution Area		 Go To Table Of Contents
Contents		 Go To Index
Index

Go to previous page Go to next page

4
Data-Access JavaBeans and Tags

This chapter describes portable JavaBeans and tags provided with OC4J for use in accessing a database from servlets and JSP pages.

The chapter is organized as follows:

JavaBeans for Data Access

The OC4J product includes a set of JavaBeans you can use to access a database. This section, organized as follows, describes the beans:

Introduction to Data-Access JavaBeans

OC4J supplies a set of custom JavaBeans for database access. The following beans are included in the oracle.jsp.dbutil package:

This section presumes a working knowledge of Oracle JDBC. Consult the Oracle9i 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 xsu111.jar (for JDK 1.1.x), both of which are provided with Oracle9iAS.

You will also need appropriate JDBC driver classes installed and in your classpath, such as classes12.zip for an Oracle database and JDK 1.2 or higher.

Notes:

The Oracle data-access JavaBeans implement the Oracle JspScopeListener interface for event notification. Refer to "JSP Event-Handling--JspScopeListener" for information about this interface.

Data-Access Support for Data Sources and Pooled Connections

The data-access JavaBeans, as well as the data-access tag library, supports 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/pool/OracleDS"
   location="jdbc/ConnectionDS"
   pooled-location="jdbc/pool/OracleDS"
   url="jdbc:oracle:thin:@myhost:1521:orcl"
   username="scott"
   password="tiger"
   min-connections="3"
   max-connections="50"
   wait-timeout="10"
   inactivity-timeout="30" />

See the Oracle9iAS Containers for J2EE Services Guide for more information about data sources.

Data-Access JavaBean Descriptions

This section describes attributes and methods of the data-access JavaBeans--ConnBean, ConnCacheBean, DBBean, and CursorBean--and concludes with an example that uses a data source:

ConnBean for a Database Connection

Use oracle.jsp.dbutil.ConnBean to establish a simple database connection (one that uses no connection pooling or caching).

Notes:

For queries only, if you do not require a data source, it is simpler to use DBBean, 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.

ConnBean provides the following setter and getter methods for these properties:

Use the following methods to open and close a connection, or to verify its status:

Use the following method to open a cursor and return a CursorBean object:

or:

See "CursorBean for DML and Stored Procedures" for information about CursorBean functionality.

ConnCacheBean for Connection Caching

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 Oracle9i JDBC Developer's Guide and Reference for information about connection caching.

Notes:

  • To use data sources or simple connection objects, use ConnBean instead.

  • ConnCacheBean extends OracleConnectionCacheImpl, which extends OracleDataSource (both in Oracle JDBC package oracle.jdbc.pool).

ConnCacheBean has the following properties:

The ConnCacheBean class supports methods defined in the Oracle JDBC OracleConnectionCacheImpl class, including the following getter and setter methods for its properties:

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 Oracle9i JDBC Developer's Guide and Reference.

Note:

As with any JavaBean you use in a JSP page, you can set any of the ConnCacheBean properties with a jsp:setProperty action instead of using the setter method directly.

Use the following methods to open and close a connection:

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 Oracle9i JDBC Developer's Guide and Reference for information about these features.

Notes:

  • ConnCacheBean has the same functionality as the OracleConnectionCacheImpl class. See the Oracle9i JDBC Developer's Guide and Reference for more information.

  • Unlike ConnBean, when you use ConnCacheBean, you use normal Connection object functionality to create and execute statement objects.

DBBean for Queries Only

Use oracle.jsp.dbutil.DBBean to execute queries only.

Notes:

  • DBBean has its own connection mechanism but does not support data sources. If you require a data source, use ConnBean instead. If you do not require a data source, ConnBean is not required.

  • Use CursorBean for any other DML operations (UPDATE, INSERT, DELETE, or stored procedure calls).

DBBean has the following properties:

DBBean provides the following setter and getter methods for these properties:

Use the following methods to open and close a connection:

Use either of the following methods to execute a query:

CursorBean for DML and Stored Procedures

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 Oracle9i JDBC Developer's Guide and Reference.

Note:

To use connection caching, use ConnCacheBean and normal Connection object functionality. Do not use CursorBean.

CursorBean has the following properties:

You can set these properties with the following methods to enable Oracle JDBC features, as desired:

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:

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():

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 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 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.

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.)

Additionally, CursorBean supports Oracle JDBC functionality such as registerOutParameter() for callable statements, setXXX() methods for prepared statements and callable statements, and getXXX() methods for result sets and callable statements.

Use the following method to close the database cursor:

Example: Using ConnBean and CursorBean with a Data Source

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>"); }
%>

SQL Tags for Data Access

OC4J includes a set of tags you can use in JSP pages to execute SQL commands to access a database. This section, organized as follows, describes the tags:

Introduction to Data-Access Tags

OC4J supplies a custom tag library for SQL functionality, consisting of the following tags:

These tags are described in the following subsections. For examples, see the OC4J demos.

Note the following requirements for using SQL tags:

For general information about JSP 1.1 tag library usage, including tag library description files and taglib directives, refer to the Oracle9iAS Containers for J2EE Support for JavaServer Pages Reference.

Data-Access Tag Descriptions

This section provides detailed syntax for the data-access tags and an example using dbOpen and dbQuery tags with a data source.

For a complete set of sample pages using these tags, see the OC4J demos.

Notes:

  • The prefix "sql:" is used in the tag syntax here. This is by convention but is not required. You can specify any desired prefix in your taglib directive.

  • See "Tag Syntax Symbology and Notes" for general information about tag syntax conventions in this manual.

SQL dbOpen Tag

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 Oracle9iAS Containers for J2EE Support for JavaServer Pages Reference for information about the standard JSP tag library framework and tag-extra-info classes.

Syntax

<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 and end tags.

Note:

You must either set the dataSource attribute or set 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 dbOpen tag user and password attributes, that will initialize the cache for that user and password. Subsequent uses of the cache are for the same user and password.

Attributes

SQL dbClose Tag

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.

Note that by using the JspScopeListener utility provided with OC4J, you can have the connection closed automatically with session-based event-handling. Refer to "JSP Event-Handling--JspScopeListener" for information.

Syntax

<sql:dbClose connId = "connection_id" 
           [ scope = "page" | "request" | "scope" | "application" ] />
Attributes

SQL dbQuery 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 and end tags.

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".

Syntax

<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:

  • In the current release, do not terminate the SELECT statement with a semicolon. This will result in a syntax error.

  • The dbQuery tag does not currently support LOB columns. This support is expected in a future release.

Attributes

SQL dbCloseQuery Tag

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.

Syntax

<sql:dbCloseQuery queryId = "query_id" />
Attributes

SQL dbNextRow Tag

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.

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 Oracle9iAS Containers for J2EE Support for JavaServer Pages Reference for information about the standard JSP tag library framework and tag-extra-info classes.

Syntax

<sql:dbNextRow queryId = "query_id" >
...Row processing...
</sql:dbNextRow >
Attributes

Example

The following example shows the combined use of a dbOpen, dbQuery, and dbNextRow tag. 

<sql:dbOpen connId="con1" URL="jdbc:oracle:thin:@myhost: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" />

SQL dbExecute Tag

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.

This tag uses an oracle.jsp.dbutil.CursorBean object for the cursor. See "CursorBean for DML and Stored Procedures" for information about CursorBean functionality.

Syntax

<sql:dbExecute 
           [ connId = "connection_id" ]
           [ scope = "page" | "request" | "scope" | "application" ]
           [ output = "yes" | "no" ] 
           [ bindParams = "value" ] >

   ...DML or DDL statement (one only)...

</sql:dbExecute >

Important:

  • In the current release, do not terminate the DML or DDL statement with a semicolon. This will result in a syntax error.

  • The dbExecute tag does not currently support LOB columns. This support is expected in a future release.

Attributes

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.

Syntax

<sql:dbSetParam name = "param_name"
                value = "param_value" 
              [ scope = "page" | "request" | "scope" | "application" ] />
Attributes

Example

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 URL="<%= connStr %>" user="scott" password="tiger">
        <sql:dbExecute output="yes" bindParams="id2 name job sal">
             insert into emp(empno, ename, deptno, job, sal) 
                    values (?, ?, 20, ?, ?)
        </sql:dbExecute>
    </sql:dbOpen>

SQL dbSetCookie Tag

You can use this tag to set a cookie. The dbSetCookie tag wraps functionality of the standard javax.servlet.http.Cookie class.

Syntax

<sql:dbSetCookie name = "cookie_name"
               [ value = "cookie_value" ]
               [ domain = "domain_name" ]
               [ comment = "comment" ]
               [ maxAge = "age" ]
               [ version = "protocol_version" ]
               [ secure = "true" | "false" ]
               [ path = "path" ] />
Attributes

Example

<sql:dbSetCookie name="cId" value='<%=request.getParameter("id")%>'
                 maxAge='800000' />

Example: Using dbOpen and dbQuery with a Data Source

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="/WEB-INF/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>

Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Solution Area		 Go To Table Of Contents
Contents		 Go To Index
Index