4 Data-Access JavaBeans and Tags

ConnBean for a Database Connection

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

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

• stmtCacheSize: cache size for Oracle JDBC statement caching

  Setting stmtCacheSize enables Oracle JDBC statement caching.

• executeBatch: batch size for Oracle JDBC update batching

  Setting executeBatch enables Oracle JDBC update batching.

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

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

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 Oracle Database JDBC Developer's Guide and Reference for information about 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 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.

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.

DBBean for Queries Only

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

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

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

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 the CursorBean 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()

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:

• void close()

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

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.

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

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

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.

<sql:dbCloseQuery queryId = "query_id" />

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

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

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

SQL dbExecute Tag

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 > 

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.

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

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

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.

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

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