Oracle JavaServer Pages Developer's Guide and Reference Release 8.1.7 Part Number A83726-01 |
Library | Service | Contents | Index

## Database-Access JavaBean Samples

This section provides examples using the Oracle database-access JavaBeans. These beans are provided with OracleJSP but are generally portable to other JSP environments. Note, however, that the connection caching bean relies on the Oracle JDBC implementation of connection caching.

`DBBean` is the simplest of these JavaBeans, with its own connection functionality and supporting queries only. For more complicated operations, use appropriate combinations of `ConnBean` (for simple connections), `ConnCacheBean` (for connection caching), and `CursorBean` (for general SQL DML operations).

For more information, see "Oracle Database-Access JavaBeans".

The following examples are included:

- Page Using DBBean--DBBeanDemo.jsp

- Page Using ConnBean--ConnBeanDemo.jsp

- Page Using CursorBean--CursorBeanDemo.jsp

- Page Using ConnCacheBean--ConnCacheBeanDemo.jsp

  Note: Oracle also provides custom tags for SQL functionality that use these JavaBeans behind the scenes. For samples using these tags, see "SQL Tag Examples".

### Page Using DBBean--DBBeanDemo.jsp

This page uses a `DBBean` object to connect to the database, execute a query, and output the results as an HTML table.

    <%@ page import="java.sql.*" %>

    <!------------------------------------------------------------------
     * This is a basic JavaServer Page that uses a DB Access Bean and queries
     * dept and emp tables in schema scott and outputs the result in an html table.
     *
    --------------------------------------------------------------------!>

    <jsp:useBean id="dbbean" class="oracle.jsp.dbutil.DBBean" scope="session">
      <jsp:setProperty name="dbbean" property="User" value="scott"/>
      <jsp:setProperty name="dbbean" property="Password" value="tiger"/>
      <jsp:setProperty name="dbbean" property="URL" value=
           "<%= (String)session.getValue(\"connStr\") %>" />
    </jsp:useBean>

    <HTML>
      <HEAD>
        <TITLE>
          DBBeanDemo JSP
        </TITLE>
      </HEAD>
     <BODY BGCOLOR=EOFFFO>
     <H1> Hello
      <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %>
     !  I am DBBeanDemo JSP.
     </H1>
     <HR>
     <B> I'm using DBBean and querying DEPT & EMP tables in schema SCOTT.....
         I get all employees who work in the Research department.
     </B>

     <P>
    <%
        try {

          String sql_string = " select ENAME from EMP,DEPT " +
                              " where DEPT.DNAME = 'RESEARCH' " +
                              " and DEPT.DEPTNO = EMP.DEPTNO";

          // Make the Connection
          dbbean.connect();

          // Execute the SQL and get a HTML table
          out.println(dbbean.getResultAsHTMLTable(sql_string));

          // Close the Bean to close the connection
          dbbean.close();
        } catch (SQLException e) {
          out.println("<P>" + "There was an error doing the query:");
          out.println ("<PRE>" + e + "</PRE> \n <P>");
        }
    %>

     </BODY>
    </HTML>

### Page Using ConnBean--ConnBeanDemo.jsp

This page uses a `ConnBean` object (for a simple connection) to retrieve a `CursorBean` object, then uses the `CursorBean` object to output query results as an HTML table.

    <%@ page import="java.sql.* , oracle.jsp.dbutil.*" %>

    <!------------------------------------------------------------------
     * This is a basic JavaServer Page that uses a Connection Bean and queries
     * emp table in schema scott and outputs the result in an html table.
     *
    --------------------------------------------------------------------!>

    <jsp:useBean id="cbean" class="oracle.jsp.dbutil.ConnBean" scope="session">
      <jsp:setProperty name="cbean" property="User" value="scott"/>
      <jsp:setProperty name="cbean" property="Password" value="tiger"/>
      <jsp:setProperty name="cbean" property="URL" value=
           "<%= (String)session.getValue(\"connStr\") %>"/>
      <jsp:setProperty name="cbean" property="PreFetch" value="5"/>
      <jsp:setProperty name="cbean" property="StmtCacheSize" value="2"/>
    </jsp:useBean>

    <HTML>
      <HEAD>
        <TITLE>
          Connection Bean Demo JSP
        </TITLE>
      </HEAD>
     <BODY BGCOLOR=EOFFFO>
     <H1> Hello
      <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %>
     !  I am Connection Bean Demo JSP.
     </H1>
     <HR>
     <B> I'm using connection and a query bean and querying employee names
         and salaries from EMP table in schema SCOTT..
     </B>

     <P>
    <%
        try {

          // Make the Connection
          cbean.connect();

          String sql = "SELECT ename, sal FROM scott.emp ORDER BY ename";

          // get a Cursor Bean
          CursorBean cb = cbean.getCursorBean  (CursorBean.PREP_STMT, sql);

          out.println(cb.getResultAsHTMLTable());

          // Close the cursor bean
          cb.close();
          // Close the Bean to close the connection
          cbean.close();
        } catch (SQLException e) {
          out.println("<P>" + "There was an error doing the query:");
          out.println ("<PRE>" + e + "</PRE> \n <P>");
        }
    %>

     </BODY>
    </HTML>

### Page Using CursorBean--CursorBeanDemo.jsp

This page uses a `ConnBean` object (for a simple connection) and a `CursorBean` object to execute a PL/SQL statement, get a `REF CURSOR`, and translate the results into an HTML table.

    <%@ page import="java.sql.* , oracle.jsp.dbutil.*" %>

    <!------------------------------------------------------------------
     * This is a basic JavaServer Page that uses a Cursor and Conn Beans and queries
     * dept table in schema scott and outputs the result in an html table.
     *
    --------------------------------------------------------------------!>

    <jsp:useBean id="connbean" class="oracle.jsp.dbutil.ConnBean" scope="session">
      <jsp:setProperty name="connbean" property="User" value="scott"/>
      <jsp:setProperty name="connbean" property="Password" value="tiger"/>
      <jsp:setProperty name="connbean" property="URL" value=
           "<%=    (String)session.getValue(\"connStr\") %>" />
    </jsp:useBean>
    <jsp:useBean id="cbean" class="oracle.jsp.dbutil.CursorBean" scope="session">
      <jsp:setProperty name="cbean" property="PreFetch" value="10"/>
      <jsp:setProperty name="cbean" property="ExecuteBatch" value="2"/>
    </jsp:useBean>

    <HTML>
      <HEAD>
        <TITLE>
          CursorBean Demo JSP
        </TITLE>
      </HEAD>
     <BODY BGCOLOR=EOFFFO>
     <H1> Hello
      <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %>
     !  I am Cursor Bean  JSP.
     </H1>
     <HR>
     <B> I`m using cbean and i'm quering department names from DEPT table
         in schema SCOTT..
     </B>

     <P>
    <%

        try {

          // Make the Connection
          connbean.connect();

          String sql = "BEGIN OPEN ? FOR SELECT DNAME FROM DEPT; END;";

          // Create a Callable Statement
          cbean.create ( connbean, CursorBean.CALL_STMT, sql);
          cbean.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR);

          // Execute the PLSQL
          cbean.executeUpdate ();

          // Get the Ref Cursor
          ResultSet rset = cbean.getCursor(1);

          out.println(oracle.jsp.dbutil.BeanUtil.translateToHTMLTable (rset));

          // Close the RefCursor
          rset.close();

          // Close the Bean
          cbean.close();

          // Close the connection
          connbean.close();

        } catch (SQLException e) {
          out.println("<P>" + "There was an error doing the query:");
          out.println ("<PRE>" + e + "</PRE> \n <P>");
        }
    %>

     </BODY>
    </HTML>

### Page Using ConnCacheBean--ConnCacheBeanDemo.jsp

This page uses a ConnCacheBean object to obtain a connection from a connection cache. It then uses standard JDBC functionality to execute a query, formatting the results as an HTML table.

    <%@ page import="java.sql.*, javax.sql.*, oracle.jsp.dbutil.ConnCacheBean" %>

    <!------------------------------------------------------------------
     * This is a basic JavaServer Page that does a JDBC query on the
     * emp table in schema scott and outputs the result in an html table.
     * Uses Connection Cache Bean.
    --------------------------------------------------------------------!>

    <jsp:useBean id="ccbean" class="oracle.jsp.dbutil.ConnCacheBean"
                 scope="session">
      <jsp:setProperty name="ccbean" property="user" value="scott"/>
      <jsp:setProperty name="ccbean" property="password" value="tiger"/>
      <jsp:setProperty name="ccbean" property="URL" value=
           "<%= (String)session.getValue(\"connStr\") %>"  />
      <jsp:setProperty name="ccbean" property="MaxLimit" value="5"  />
      <jsp:setProperty name="ccbean" property="CacheScheme" value=
           "<%= ConnCacheBean.FIXED_RETURN_NULL_SCHEME %>"  />
    </jsp:useBean>
    <HTML>
      <HEAD>
        <TITLE>
          SimpleQuery JSP
        </TITLE>
      </HEAD>
     <BODY BGCOLOR=EOFFFO>
     <H1> Hello
      <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %>
     !  I am Connection Cache Demo Bean
     </H1>
     <HR>
     <B> I will do a basic JDBC query to get employee data
         from EMP table in schema SCOTT. The connection is obtained from
         the Connection Cache.
     </B>

     <P>
    <%
        try {
          Connection conn = ccbean.getConnection();

          Statement stmt = conn.createStatement ();
          ResultSet rset = stmt.executeQuery ("SELECT ename, sal " +
                                    "FROM scott.emp ORDER BY ename");
          if (rset.next()) {
    %>
          <TABLE BORDER=1 BGCOLOR="C0C0C0">
          <TH WIDTH=200 BGCOLOR="white"> <I>Employee Name</I> </TH>
          <TH WIDTH=100 BGCOLOR="white"> <I>Salary</I> </TH>
          <TR> <TD ALIGN=CENTER> <%= rset.getString(1) %> </TD>
               <TD ALIGN=CENTER> $<%= rset.getDouble(2) %> </TD>
          </TR>

    <%      while (rset.next()) {
    %>

          <TR> <TD ALIGN=CENTER> <%= rset.getString(1) %> </TD>
               <TD ALIGN=CENTER> $<%= rset.getDouble(2) %> </TD>
          </TR>

    <% }
    %>
          </TABLE>
    <%  }
          else {
    %>
          <P> Sorry, the query returned no rows! </P>

    <%
          }
          rset.close();
          stmt.close();
          conn.close();
          ccbean.close();
        } catch (SQLException e) {
          out.println("<P>" + "There was an error doing the query:");
          out.println ("<PRE>" + e + "</PRE> \n <P>");
        }
    %>

     </BODY>
    </HTML>

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index