Sample Applications

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

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>