9
Sample Applications

This chapter provides a variety of code samples for JSP pages and the JavaBeans that they use (as applicable), in the following categories:

Basic Samples

This section provides JSP samples that are fairly basic but also exemplify use of the Oracle JML datatypes. This includes an elementary "hello" sample, a sample of using a JavaBean, and a more intermediate shopping cart example. The following samples are provided:

These examples could use standard datatypes instead, but JML datatypes offer a number of advantages, as described in "JML Extended Datatypes". JML datatypes are also portable to other JSP environments.

This section concludes with a sample that shows how to obtain environmental information, including the version number of your OracleJSP installation:

Information Page--info.jsp

Hello Page--hellouser.jsp

This sample is an elementary JSP "hello" page. Users are presented with a form to enter their name. After they submit the name, the JSP page redisplays the form with the name at the top.

<%-----------------------------------------------------------
   Copyright (c) 1999, Oracle Corporation. All rights reserved.
------------------------------------------------------------%>

<%@page session="false" %>

<jsp:useBean id="name" class="oracle.jsp.jml.JmlString" scope="request" >
   <jsp:setProperty name="name" property="value" param="newName" />
</jsp:useBean>

<HTML>
<HEAD>
<TITLE>
Hello User
</TITLE>
</HEAD>

<BODY>

<% if (!name.isEmpty()) { %>
<H3>Welcome <%= name.getValue() %></H3>
<% } %>

<P>
Enter your Name:
<FORM METHOD=get>
<INPUT TYPE=TEXT name=newName size = 20><br>
<INPUT TYPE=SUBMIT VALUE="Submit name">
</FORM>

</BODY>
</HTML>

Usebean Page--usebean.jsp

This page uses a simple JavaBean, NameBean, to illustrate usage of the jsp:useBean tag. Code for both the bean and the page is provided.

Code for usebean.jsp

<%-----------------------------------------------------------
   Copyright (c) 1999, Oracle Corporation. All rights reserved.
------------------------------------------------------------%>

<%@ page import="beans.NameBean"  %>

<jsp:useBean id="pageBean" class="beans.NameBean" scope="page" />
<jsp:setProperty name="pageBean" property="*" />

<jsp:useBean id="sessionBean" class="beans.NameBean" scope="session" />
<jsp:setProperty name="sessionBean" property="*" />

<HTML>
<HEAD> <TITLE> The UseBean JSP </TITLE> </HEAD>
<BODY BGCOLOR=white>

<H3> Welcome to the UseBean JSP </H3>
<P><B>Page bean: </B>
<% if (pageBean.getNewName().equals("")) { %>
  I don't know you. 
<% } else { %>
  Hello <%= pageBean.getNewName() %> !
<%  } %> 
  
<P><B>Session bean: </B>
<% if (sessionBean.getNewName().equals("")) { %>
  I don't know you either. 
<% } else {
       if ((request.getParameter("newName") == null) || 
           (request.getParameter("newName").equals(""))) { %>
         Aha, I remember you.   
<%     } %>   
       You are <%= sessionBean.getNewName() %>.
<% } %>

<P>May we have your name?
<FORM METHOD=get>
<INPUT TYPE=TEXT name=newName size = 20>
<INPUT TYPE=SUBMIT VALUE="Submit name">
</FORM>
</BODY>
</HTML>

Code for NameBean.java

package beans;

public class NameBean {

  String newName="";

  public void NameBean() {  }

  public String getNewName() {
    return newName;
  }
  public void setNewName(String newName) {
    this.newName = newName;
  }
}

Shopping Cart Page--cart.jsp

This sample shows how to use session state to maintain a shopping cart. The user chooses a T-shirt or sweatshirt to order and the order is then redisplayed. If shopping continues and the order is changed, the page redisplays the order, striking out the previous choices as appropriate.

The cart.jsp file is the primary source file; it references index.jsp. Code for both pages is provided.

Code for cart.jsp

<%-----------------------------------------------------------
   Copyright (c) 1999-2000, Oracle Corporation. All rights reserved.
------------------------------------------------------------%>
<jsp:useBean id="currSS" scope ="session" class="oracle.jsp.jml.JmlString" />
<jsp:useBean id="currTS" scope ="session" class="oracle.jsp.jml.JmlString" />

<HTML>

<HEAD>
        <TITLE>Java Store</TITLE>
</HEAD>

<BODY BACKGROUND=images/bg.gif BGCOLOR=#FFFFFF>

<jsp:useBean id="sweatShirtSize" scope="page" class="oracle.jsp.jml.JmlString" >
   <jsp:setProperty name="sweatShirtSize" property="value" param="SS" />
</jsp:useBean>
<jsp:useBean id="tshirtSize" scope="page" class="oracle.jsp.jml.JmlString" >
   <jsp:setProperty name="tshirtSize" property="value" param="TS" />
</jsp:useBean>

<jsp:useBean id="orderedSweatshirt" scope="page" 
class="oracle.jsp.jml.JmlBoolean" >
   <jsp:setProperty name="orderedSweatshirt" property="value" 
      value= '<%= !(sweatShirtSize.isEmpty() || 
sweatShirtSize.getValue().equals("none")) %>'  />
</jsp:useBean>

<jsp:useBean id="orderedTShirt" scope="page" class="oracle.jsp.jml.JmlBoolean" >
   <jsp:setProperty name="orderedTShirt" property="value"
      value='<%= !(tshirtSize.isEmpty() || tshirtSize.getValue().equals("none")) 
%>'  />
</jsp:useBean>

<P>
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 WIDTH=100% HEIGHT=553>
        <TR>
                <TD WIDTH=33% HEIGHT=61>&nbsp;</TD>
                <TD WIDTH=67% HEIGHT=61>&nbsp;</TD>
        </TR>
        <TR>
                <TD WIDTH=33% HEIGHT=246>&nbsp;</TD>
                <TD WIDTH=67% HEIGHT=246 VALIGN=TOP BGCOLOR=#FFFFFF>

   <% if (orderedSweatshirt.getValue() || orderedTShirt.getValue()) { %>
      Thank you for selecting our fine JSP Wearables!<P>
      
      <% if (!currSS.isEmpty() || !currTS.isEmpty()) { %>
      You have changed your order:
            <UL>
         <% if (orderedSweatshirt.getValue()) { %>
            <LI>1 Sweatshirt
            <% if (!currSS.isEmpty()) { %> 
               <S>(size: <%= currSS.getValue().toUpperCase() %>)</S>&nbsp
            <% } %>
            (size: <%= sweatShirtSize.getValue().toUpperCase() %> )
         <% } else if (!currSS.isEmpty()) { %>
               <LI><S>1 Sweatshirt (size: <%= currSS.getValue().toUpperCase()
               %>)</S>
         <% } %>

            <% if (orderedTShirt.getValue()) { %>
            <LI>1 Tshirt 
            <% if (!currTS.isEmpty()) { %> 
               <S>(size: <%= currTS.getValue().toUpperCase() %>)</S>&nbsp
            <% } %>
            (size: <%= tshirtSize.getValue().toUpperCase() %>)
         <% } else if (!currTS.isEmpty()) { %>
               <LI><S>1 Tshirt (size: <%= currTS.getValue().toUpperCase()
               %>)</S>
            <% } %>
      </UL>
      <% } else { %>
      You have selected: 
      <UL>
         <% if (orderedSweatshirt.getValue()) { %>
            <LI>1 Sweatshirt (size: <%= sweatShirtSize.getValue().toUpperCase()
                                  %>)
         <% } %>

         <% if (orderedTShirt.getValue()) { %>
            <LI>1 Tshirt (size: <%= tshirtSize.getValue().toUpperCase() %>)
         <% } %>

      </UL>
      <% } %>
   <% } else { %>
      Are you sure we can't interest you in something?
   <% } %>


   <CENTER>
      <FORM ACTION="index.jsp" METHOD="GET"
            ENCTYPE="application/x-www-form-urlencoded">
      <INPUT TYPE="IMAGE" SRC="images/shop_again.gif" WIDTH="91" HEIGHT="30"
            BORDER="0">
      </FORM>
   </CENTER>
   </TD></TR>
</TABLE>

</BODY>

</HTML>

<% 
if (orderedSweatshirt.getValue()) { 
   currSS.setValue(sweatShirtSize.getValue());
} else {
   currSS.setValue("");
}

if (orderedTShirt.getValue()) { 
   currTS.setValue(tshirtSize.getValue());
} else {
   currTS.setValue("");
}
%>

Code for index.jsp

<%-----------------------------------------------------------
   Copyright (c) 1999-2000, Oracle Corporation. All rights reserved.
------------------------------------------------------------%>
<jsp:useBean id="currSS" scope ="session" class="oracle.jsp.jml.JmlString" />
<jsp:useBean id="currTS" scope ="session" class="oracle.jsp.jml.JmlString" />

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>

<HEAD>
 <TITLE>untitled</TITLE>
</HEAD>

<BODY BACKGROUND="images/bg.gif" BGCOLOR="#FFFFFF">

<FORM ACTION="cart.jsp" METHOD="POST" 
ENCTYPE="application/x-www-form-urlencoded">
<P>
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" HEIGHT="553">
 <TR>
  <TD WIDTH="33%" HEIGHT="61">&nbsp;</TD>
  <TD WIDTH="67%" HEIGHT="61">&nbsp;</TD>
 </TR>
 <TR>
  <TD WIDTH="33%" HEIGHT="246">&nbsp;</TD>
  <TD WIDTH="67%" HEIGHT="246" VALIGN="TOP" BGCOLOR="#FFFFFF">
   <TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="81%">
    <TR>
     <TD WIDTH="100%" BGCOLOR="#CCFFFF">
      <H4>JSP Wearables
     </TD>
    </TR>
    <TR>
     <TD WIDTH="100%" BGCOLOR="#FFFFFF">

      <BLOCKQUOTE>
      Sweatshirt
      <SPACER TYPE="HORIZONTAL" SIZE="10">($24.95)<BR>
      <SPACER TYPE="HORIZONTAL" SIZE="30">
       <INPUT TYPE="RADIO" NAME="SS" VALUE="xl" 
          <%= currSS.getValue().equals("xl") ? "CHECKED" : "" %> >XL
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="SS" VALUE="l" <%= currSS.getValue().equals("l")
         ? "CHECKED" : "" %> >L
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="SS" VALUE="m" <%= currSS.getValue().equals("m")
         ? "CHECKED" : "" %> >M
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="SS" VALUE="s" <%= currSS.getValue().equals("s")
         ? "CHECKED" : "" %> >S
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="SS" VALUE="xs" 
          <%= currSS.getValue().equals("xs") ? "CHECKED" : "" %> >XS
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="SS" VALUE="none" 
          <%= currSS.getValue().equals("none") || currSS.isEmpty()  ? 
          "CHECKED" : "" %> >NONE
      <BR>
      <BR>
      T-Shirt<SPACER TYPE="HORIZONTAL" SIZE="10"> (14.95)<BR>
      <SPACER TYPE="HORIZONTAL" SIZE="30">
       <INPUT TYPE="RADIO" NAME="TS" VALUE="xl" 
          <%= currTS.getValue().equals("xl") ? "CHECKED" : "" %> >XL
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="TS" VALUE="l" <%= currTS.getValue().equals("l")
         ? "CHECKED" : "" %> >L
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="TS" VALUE="m" <%= currTS.getValue().equals("m")
         ? "CHECKED" : "" %> >M
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="TS" VALUE="s" <%= currTS.getValue().equals("s")
         ? "CHECKED" : "" %> >S
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="TS" VALUE="xs" 
          <%= currTS.getValue().equals("xs") ? "CHECKED" : "" %> >XS
      <SPACER TYPE="HORIZONTAL" SIZE="10">
       <INPUT TYPE="RADIO" NAME="TS" VALUE="none" 
          <%= currTS.getValue().equals("none") || currTS.isEmpty()  ? 
          "CHECKED" : "" %> >NONE
      </BLOCKQUOTE>
     </TD>
    </TR>
    <TR>
     <TD WIDTH="100%">
      <DIV ALIGN="RIGHT">
      <P><INPUT TYPE="IMAGE" SRC="images/addtobkt.gif" WIDTH="103" HEIGHT="22"
         ALIGN="BOTTOM" BORDER="0">
      </DIV>
     </TD>
    </TR>
   </TABLE>
  </TD>
 </TR>
</TABLE>

</FORM>

</BODY>

</HTML>

Information Page--info.jsp

This sample retrieves and displays the following information:

OracleJSP version number
Java classpath
OracleJSP build date
OracleJSP configuration parameter settings

<HTML> 
    <HEAD> 
        <TITLE>OJSP Information </TITLE> 
    </HEAD>
    <BODY BGCOLOR="#FFFFFF"> 
         OJSP Version:<BR><%=
                application.getAttribute("oracle.jsp.versionNumber") %>
         <BR>
         ClassPath:<BR><%=System.getProperty("java.class.path") %>
         <BR>
         OJSP BuildDate:<BR>
         <%
         try {
         %>
         <%=((oracle.jsp.runtime.OraclePageContext)pageContext).BUILD_DATE%>
         <%
         }catch(Exception e){
         }
         %>
         <BR>
         OJSP Init Parameters:<BR>
         <%
         for (Enumeration paraNames = config.getInitParameterNames();
                      paraNames.hasMoreElements() ;) {
             String paraName = (String)paraNames.nextElement();
         %>
         <%=paraName%> = <%=config.getInitParameter(paraName)%>
         <BR>
         <%
         }
         %>
    </BODY> 
</HTML>

JDBC Samples

Examples in this section use JDBC to query a database or the middle-tier database cache. For the most part they use standard JDBC functionality, although the connection caching examples use Oracle's particular connection caching implementation. The following examples are provided:

See the Oracle9i JDBC Developer's Guide and Reference for information about Oracle JDBC in general and the Oracle JDBC connection caching implementation in particular.

Simple Query--SimpleQuery.jsp

This page executes a simple query of the scott.emp table, listing employees and their salaries in an HTML table (ordered by employee name).

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

<!------------------------------------------------------------------
 * 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.
 *  
--------------------------------------------------------------------!>
<HTML> 
  <HEAD> 
    <TITLE>
       SimpleQuery JSP
    </TITLE>
  </HEAD>
 <BODY BGCOLOR=EOFFFO> 
 <H1> Hello 
  <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %>
 !  I am SimpleQuery JSP.
 </H1>
 <HR>
 <B> I will do a basic JDBC query to get employee data  
     from EMP table in schema SCOTT..
 </B> 
 <P>
<%
    try {
      // Use the following 2 files when running inside Oracle 9i
      // Connection conn = new oracle.jdbc.driver.OracleDriver().
      //                     defaultConnection ();
      Connection  conn = 
          DriverManager.getConnection((String)session.getValue("connStr"),
                                           "scott", "tiger");
      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();
    } catch (SQLException e) {
      out.println("<P>" + "There was an error doing the query:");
      out.println ("<PRE>" + e + "</PRE> \n <P>");
    }
%>
 </BODY>
</HTML>

User-Specified Query--JDBCQuery.jsp

This page queries the scott.emp table according to a user-specified condition and outputs the results.

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

<HTML>
<HEAD> <TITLE> The JDBCQuery JSP  </TITLE> </HEAD>
<BODY BGCOLOR=white>

<% String searchCondition = request.getParameter("cond"); 
   if (searchCondition != null) { %>
      <H3> Search results for : <I> <%= searchCondition %> </I> </H3>
      <%= runQuery(searchCondition) %>
      <HR><BR>
<% }  %>

<B>Enter a search condition:</B>
<FORM METHOD=get> 
<INPUT TYPE="text" NAME="cond" SIZE=30>
<INPUT TYPE="submit" VALUE="Ask Oracle");
</FORM>
</BODY>
</HTML>
<%! 
  private String runQuery(String cond) throws SQLException {
     Connection conn = null; 
     Statement stmt = null; 
     ResultSet rset = null; 
     try {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection((String)session.getValue("connStr"),
                                           "scott", "tiger");
        stmt = conn.createStatement();
        rset = stmt.executeQuery ("SELECT ename, sal FROM scott.emp "+ 
                                  (cond.equals("") ? "" : "WHERE " + cond ));
        return (formatResult(rset));
     } catch (SQLException e) { 
         return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
     } finally {
         if (rset!= null) rset.close(); 
         if (stmt!= null) stmt.close();
         if (conn!= null) conn.close();
     }
  }
  private String formatResult(ResultSet rset) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if (!rset.next())      
      sb.append("<P> No matching rows.<P>\n");
    else {  sb.append("<UL><B>"); 
         do {  sb.append("<LI>" + rset.getString(1) + 
                            " earns $ " + rset.getInt(2) + ".</LI>\n");
            } while (rset.next());
         sb.append("</B></UL>"); 
    }
    return sb.toString();
  }
%>

Query Using a Query Bean--UseHtmlQueryBean.jsp

This page uses a JavaBean, HtmlQueryBean, to query the scott.emp table according to a user-specified condition. HtmlQueryBean, in turn, uses the class HtmlTable to format the output into an HTML table. This sample includes code for the JSP page, HtmlQueryBean, and HtmlTable.

Code for UseHtmlQueryBean.jsp

<jsp:useBean id="htmlQueryBean" class="beans.HtmlQueryBean" scope="session" />
<jsp:setProperty name="htmlQueryBean" property="searchCondition" />

<HTML>
<HEAD> <TITLE> The UseHtmlQueryBean JSP  </TITLE> </HEAD>
<BODY BGCOLOR="white">
<%  String searchCondition = request.getParameter("searchCondition"); 
    if (searchCondition != null) { %>
      <H3>Search Results for : <I> <%= searchCondition %> </I> </H3>
      <%=  htmlQueryBean.getResult() %>
      <BR> <HR>
<%  }  %>
<P><B>Enter a search condition:</B></P>
<FORM METHOD=get> 
<INPUT TYPE=text NAME="searchCondition" SIZE=30>
<INPUT TYPE=submit VALUE="Ask Oracle">
</FORM>
</BODY>
</HTML>

Code for HtmlQueryBean.java

package beans;

import java.sql.*;

public class HtmlQueryBean {

  private String searchCondition = "";
  private String connStr = null;

  public String getResult() throws SQLException {
    return runQuery();
  }

  public void setSearchCondition(String searchCondition) {
    this.searchCondition = searchCondition;
  }

  
  public void setConnStr(String connStr) {
    this.connStr = connStr;
  }

  private String runQuery() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    try {
      if (conn == null) {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(connStr,
                               "scott","tiger");
      }
      stmt = conn.createStatement();
      rset = stmt.executeQuery ("SELECT ename as \"Name\", " + 
               "empno as \"Employee Id\","+ 
               "sal as \"Salary\"," +  
               "TO_CHAR(hiredate, 'DD-MON-YYYY') as \"Date Hired\"" + 
               "FROM scott.emp " + (searchCondition.equals("") ? "" :
               "WHERE " + searchCondition ));
      return  format(rset);;
    } catch (SQLException e) { 
      return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
    }
    finally {
    try {
      if (rset!= null) rset.close();
      if (stmt!= null) stmt.close(); 
      if (conn!= null) conn.close();
      } catch (SQLException ignored) {}
    }
  } 
  
  public static String format(ResultSet rs) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if (rs == null || !rs.next())
      sb.append("<P> No matching rows.<P>\n");
    else {
      sb.append("<TABLE BORDER>\n");
      ResultSetMetaData md = rs.getMetaData();
      int numCols = md.getColumnCount();
      for (int i=1; i<= numCols; i++) {
        sb.append("<TH><I>" + md.getColumnLabel(i) + "</I></TH>");
      }
      do {
        sb.append("<TR>\n");

        for (int i = 1; i <= numCols; i++) {
          sb.append("<TD>");
          Object obj = rs.getObject(i);
          if (obj != null)  sb.append(obj.toString());
          sb.append("</TD>");
        }
        sb.append("</TR>");
      } while (rs.next());
      sb.append("</TABLE>");
    }
    return sb.toString();
  }
}

Code for HtmlTable.java

import java.sql.*;

public class HtmlTable {

  public static String format(ResultSet rs) throws SQLException {
    StringBuffer sb = new StringBuffer();
    if (rs == null || !rs.next())      
      sb.append("<P> No matching rows.<P>\n");
    else {  
      sb.append("<TABLE BORDER>\n");
      ResultSetMetaData md = rs.getMetaData();
      int numCols = md.getColumnCount();
      for (int i=1; i<= numCols; i++) {
         sb.append("<TH><I>" + md.getColumnLabel(i) + "</I></TH>");
      }
      do { 
        sb.append("<TR>\n");

        for (int i = 1; i <= numCols; i++) {
        sb.append("<TD>");
        Object obj = rs.getObject(i);
        if (obj != null)  sb.append(obj.toString());
        sb.append("</TD>");
      } 
      sb.append("</TR>");
      } while (rs.next());
      sb.append("</TABLE>"); 
    }
    return sb.toString();
  }
}

Connection Caching--ConnCache3.jsp and ConnCache1.jsp

This section provides two examples of connection caching using Oracle's caching implementation. This implementation uses the Oracle JDBC OracleConnectionCacheImpl class. For introductory information, see "Database Connection Caching". For further information see, the Oracle9i JDBC Developer's Guide and Reference.

The first example, ConnCache3.jsp, performs its own cache setup.

The second example, ConnCache1.jsp, uses a separate page, setupcache.jsp, to do the setup.

Code is provided for all three pages.

Note:

As a more convenient alternative, you can use the ConnCacheBean JavaBean provided with OracleJSP. See "Page Using ConnCacheBean--ConnCacheBeanDemo.jsp".

Code for ConnCache3.jsp (with cache setup)

This sample page handles its own connection cache setup.

<%@ page import="java.sql.*, javax.sql.*, oracle.jdbc.pool.*" %>

<!------------------------------------------------------------------
 * This is a JavaServer Page that uses Connection Caching at Session
 * scope. 
--------------------------------------------------------------------!>

<jsp:useBean id="ods" class="oracle.jdbc.pool.OracleConnectionCacheImpl" 
scope="session" />

<HTML> 
  <HEAD> 
    <TITLE>
     ConnCache 3  JSP
    </TITLE>
  </HEAD>
 <BODY BGCOLOR=EOFFFO> 
 <H1> Hello 
  <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %>
 !  I am Connection Caching  JSP.
 </H1>
 <HR>
 <B> Session  Level Connection Caching.
 </B> 

 <P>
<%
    try {
      ods.setURL((String)session.getValue("connStr"));
      ods.setUser("scott");
      ods.setPassword("tiger");

      Connection conn = ods.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();  // Put the Connection Back into the Pool

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

 </BODY>
</HTML>

Code for ConnCache1.jsp and setupcache.jsp

This sample page statically includes another page, setupcache.jsp, for its connection cache setup. Code is provided for both pages.

ConnCache1.jsp

<%@ include file="setupcache.jsp" %>
<%@ page import="java.sql.*, javax.sql.*, oracle.jdbc.pool.*" %>

<!------------------------------------------------------------------
 * This is a JavaServer Page that uses Connection Caching over application
 * scope. The Cache is created in an application scope in setupcache.jsp
 * Connection is obtained from the Cache and recycled back once done.
--------------------------------------------------------------------!>

<HTML> 
  <HEAD> 
    <TITLE>
      ConnCache1 JSP
    </TITLE>
  </HEAD>
 <BODY BGCOLOR=EOFFFO> 
 <H1> Hello 
  <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %>
 !  I am Connection Caching JSP.
 </H1>
 <HR>
 <B> I get the Connection from the Cache and recycle it back.
 </B> 

 <P>
<%
    try {
      Connection conn = cods.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();  // Put the Connection Back into the Pool
    } catch (SQLException e) {
      out.println("<P>" + "There was an error doing the query:");
      out.println ("<PRE>" + e + "</PRE> \n <P>");
    }
%>

 </BODY>
</HTML>

setupcache.jsp

<jsp:useBean id="cods" class="oracle.jdbc.pool.OracleConnectionCacheImpl"
  scope="application">
<% 
    cods.setURL((String)session.getValue("connStr"));
    cods.setUser("scott");
    cods.setPassword("tiger");
    cods.setStmtCache (5); 
%>
</jsp:useBean>

Data-Access JavaBean Samples

This section provides examples using the Oracle data-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 Data-Access JavaBeans".

The following examples are included:

Page Using DBBean--DBBeanDemo.jsp

This page uses a DBBean object to connect to the database or middle-tier database cache, 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>

Custom Tag Samples

This section includes the following:

a sample using some of the Oracle JSP Markup Language (JML) custom tags
referrals to additional custom tag samples elsewhere in this document

JML Tag Sample--hellouser_jml.jsp

This section provides a basic sample using some of the Oracle JML custom tags.

This is a modified version of the hellouser.jsp sample provided earlier in this chapter. For contrast, both the JML code and the original code are provided here.

Note that the runtime implementation of the JML tag library is portable to other JSP environments. For an overview of the runtime implementation, see "Overview of the JSP Markup Language (JML) Sample Tag Library". For information about the compile-time (non-portable) implementation, see Appendix C, "Compile-Time JML Tag Support".

Code for hellouser_jml.jsp (using JML tags)

<%-----------------------------------------------------------
   Copyright (c) 1999, Oracle Corporation. All rights reserved.
------------------------------------------------------------%>

<%@page session="false" %>
<%@ taglib uri="WEB-INF/jml.tld" prefix="jml" %>

<jml:useForm id="name" param="newName" scope="request" />

<HTML>
<HEAD>
<TITLE>
Hello User
</TITLE>
</HEAD>

<BODY>

<jml:if condition="!name.isEmpty()" >
<H3>Welcome <jml:print eval="name.getValue()" /></H3>
</jml:if>

<P>
Enter your Name:
<FORM METHOD=get>
<INPUT TYPE=TEXT name=newName size = 20><br>
<INPUT TYPE=SUBMIT VALUE="Submit name">
</FORM>

</BODY>
</HTML>

Code for hellouser.jsp (not using JML tags)

<%-----------------------------------------------------------
   Copyright (c) 1999, Oracle Corporation. All rights reserved.
------------------------------------------------------------%>

<%@page session="false" %>

<jsp:useBean id="name" class="oracle.jsp.jml.JmlString" scope="request" >
      <jsp:setProperty name="name" property="value" param="newName" />
</jsp:useBean>

<HTML>
<HEAD>
<TITLE>
Hello User
</TITLE>
</HEAD>

<BODY>

<% if (!name.isEmpty()) { %>
<H3>Welcome <%= name.getValue() %></H3>
<% } %>

<P>
Enter your Name:
<FORM METHOD=get>
<INPUT TYPE=TEXT name=newName size = 20><br>
<INPUT TYPE=SUBMIT VALUE="Submit name">
</FORM>

</BODY>
</HTML>

Pointers to Additional Custom Tag Samples

Additional custom tag samples are provided elsewhere in this document:

For a complete example of defining and using a standard JSP 1.1-compliant custom tag, see "End-to-End Example: Defining and Using a Custom Tag".
For samples using the Oracle custom tag library for SQL functionality, see "SQL Tag Examples".

Samples for Oracle-Specific Programming Extensions

This section provides a variety of examples using Oracle-specific extensions. This includes the following:

Page Using JspScopeListener--scope.jsp

This sample illustrates the use of a JspScopeListener implementation to allow JSP objects attached to a scope to be notified when they are going "out of scope". The sample implements a generic listener that redispatches the out-of-scope notification to the registered object or method. In using this listener, scope.jsp is able to simulate page event handlers for request and page out-of-scope notification.

This sample creates and attaches a listener object to the request and page scopes. It registers local methods to handle out-of-scope notifications forwarded by the listener. To illustrate this, the sample keeps two counters--the first is a page count; the second is a count of the number of included files.

The current page count is logged when the page goes out of scope. The included page count is logged when the request goes out of scope. The sample then proceeds to include itself five times.

The sample outputs six messages indicating a page count of 1, followed by a single message indicating five jsp:include operations occurred.

For general information about the JspScopeListener mechanism, see "OracleJSP Event Handling--JspScopeListener".

Listener Implementation--PageEventDispatcher

PageEventDispatcher is a JavaBean that implements the JspScopeListener interface. The interface defines the outOfScope() event method, which takes a JspScopeEvent object as input. The outOfScope() method of a PageEventDispatcher object is called when the scope (application, session, page, or request) associated with the object is ending.

In this sample, a PageEventDispatcher object acts as a redispatcher for the JSP page, allowing the JSP page to host the equivalent of globals.jsa "on end" functionality for page and request events. The JSP page creates a PageEventDispatcher object for each scope for which it wants to provide an event handler. It then registers the event handler method with the PageEventDispatcher object. When the PageEventDispatcher object is notified that it is going out of scope, it calls the registered "on end" method of the page.

package oracle.jsp.sample.event;

import java.lang.reflect.*;
import oracle.jsp.event.*;

public class PageEventDispatcher extends Object implements JspScopeListener {

    private Object page;
    private String methodName;
    private Method method;

    public PageEventDispatcher() {
    }

    public Object getPage() {
        return page;
    }

    public void setPage(Object page) {
        this.page = page;
    }

    public String getMethodName() {
        return methodName;
    }

    public void setMethodName(String m) 
                throws NoSuchMethodException, ClassNotFoundException  {
        method = verifyMethod(m);
        methodName = m;
    }

    public void outOfScope(JspScopeEvent ae) {
        int scope = ae.getScope();

        if ((scope == javax.servlet.jsp.PageContext.REQUEST_SCOPE  || 
             scope == javax.servlet.jsp.PageContext.PAGE_SCOPE) && 
             method != null) {
            try {
                Object args[] = {ae.getApplication(), ae.getContainer()};
                method.invoke(page, args);
            } catch (Exception e) {
                // catch all and continue
            }
        }
    }

    private Method verifyMethod(String m) 
                throws NoSuchMethodException, ClassNotFoundException {
        if (page == null) throw new NoSuchMethodException
                                      ("A page hasn't been set yet.");

        /* Don't know whether this is a request or page handler so try one then
          the other
       */
        Class c = page.getClass();
        Class pTypes[] = {Class.forName("javax.servlet.ServletContext"),
                          Class.forName("javax.servlet.jsp.PageContext")};

        try {
            return c.getDeclaredMethod(m, pTypes);
        } catch (NoSuchMethodException nsme) {
            // fall through and try the request signature
        }

        pTypes[1] = Class.forName("javax.servlet.http.HttpServletRequest");
        return c.getDeclaredMethod(m, pTypes);
    }
}

scope.jsp Source

This JSP page uses the preceding PageEventDispatcher class (which implements the JspScopeListener interface) to track events of page or request scope.

<%-- declare request and page scoped beans here --%>

<jsp:useBean id = "includeCount" class = "oracle.jsp.jml.JmlNumber" scope = "request" />
<jsp:useBean id = "pageCount" class = "oracle.jsp.jml.JmlNumber" scope = "page" > 
   <jsp:setProperty name = "pageCount" 
                    property = "value" value = "<%= pageCount.getValue() + 1 %>" /> 
</jsp:useBean>

<%-- declare the event dispatchers --%>
<jsp:useBean id = "requestDispatcher" class = "oracle.jsp.sample.event.PageEventDispatcher" 
            scope = "request" >
   <jsp:setProperty name = "requestDispatcher" property = "page" value = "<%= this %>" />
   <jsp:setProperty name = "requestDispatcher" property = "methodName" 
                    value = "request_OnEnd" />
</jsp:useBean>

<jsp:useBean id = "pageDispatcher" class = "oracle.jsp.sample.event.PageEventDispatcher" 
             scope = "page" >
    <jsp:setProperty name = "pageDispatcher" property = "page" value = "<%= this %>" />
    <jsp:setProperty name = "pageDispatcher" property = "methodName" value = "page_OnEnd" />
</jsp:useBean>

<%! 
        // request_OnEnd Event Handler
        public void request_OnEnd(ServletContext application, HttpServletRequest request) {
                // acquire beans
                oracle.jsp.jml.JmlNumber includeCount = 
                    (oracle.jsp.jml.JmlNumber) request.getAttribute("includeCount");

                // now cleanup the bean
                if (includeCount != null) application.log
                   ("request_OnEnd: Include count = " + includeCount.getValue()); 
        }

        // page_OnEnd Event Handler
        public void page_OnEnd(ServletContext application, PageContext page) {
                // acquire beans
                oracle.jsp.jml.JmlNumber pageCount = 
                    (oracle.jsp.jml.JmlNumber) page.getAttribute("pageCount");

                // now cleanup the bean -- uncomment code for real bean
                if (pageCount != null) application.log
                   ("page_OnEnd: Page count = " + pageCount.getValue());
        }  
%>

<%-- Page implementation goes here --%>

<jsp:setProperty name = "includeCount" property = "value" 
                 value = '<%= (request.getAttribute("javax.servlet.include.request_uri")
                         != null) ? includeCount.getValue() + 1 : 0 %>' /> 

<h2> Hello World </h2>

Included: <%= request.getAttribute("javax.servlet.include.request_uri") %>   
              Count: <%= includeCount.getValue() %> <br>

<% if (includeCount.getValue() < 5) { %>
        <jsp:include page="scope.jsp" flush = "true" />
<% } %>

XML Query--XMLQuery.jsp

This example connects to a database or middle-tier database cache, executes a query, and uses functionality of the oracle.xml.sql.query.OracleXMLQuery class to output the results as an XML string.

This is Oracle-specific functionality. The OracleXMLQuery class is provided with Oracle9i as part of the XML-SQL utility.

For general information about XML and XSL usage with JSP pages, see "OracleJSP Support for XML and XSL".

<%-----------------------------------------------------------
   Copyright (c) 1999, Oracle Corporation. All rights reserved.
------------------------------------------------------------%>

<%@ page import = "java.sql.*,oracle.xml.sql.query.OracleXMLQuery" %>
<html>
  <head><title> The XMLQuery Demo </title></head>
<body>
<h1> XMLQuery Demo  </h1>
<h2> Employee List in XML </h2>
<b>(View Page Source in your browser to see XML output)</b>
<% Connection conn = null;
   Statement stmt = null;
   ResultSet rset = null;
   try {

     // determine JDBC driver name from session value
     // if null, use JDBC kprb driver if in Oracle9i, JDBC oci otherwise
     String dbURL = (String)session.getValue("connStr");
     if (dbURL == null) 
       dbURL = (System.getProperty("oracle.jserver.version") == null?
               "jdbc:oracle:oci8:@" : "jdbc:oracle:kprb:@");

     DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
     conn = DriverManager.getConnection(dbURL, "scott", "tiger");
     stmt =  conn.createStatement ();
     rset = stmt.executeQuery ("SELECT ename, sal " + 
                               "FROM scott.emp ORDER BY ename");
     OracleXMLQuery xq = new OracleXMLQuery(conn, rset);  %>
       <PRE> <%=   xq.getXMLString()  %>  </PRE>
<%   } catch (java.sql.SQLException e) {  %>
         <P> SQL error: <PRE> <%= e %> </PRE> </P>
<%   } finally { 
         if (stmt != null) stmt.close();
         if (rset != null) rset.close();
         if (conn != null) conn.close(); 
     }  %>
</body>
</html>

SQLJ Queries--SQLJSelectInto.sqljsp and SQLJIterator.sqljsp

This section provides examples of using SQLJ in JSP pages to query a database or the middle-tier database cache.

The first example, SQLJSelectInto.sqljsp, selects a single row using SQLJ SELECT INTO syntax.

The second example, SQLJIterator.sqljsp, selects multiple rows into a SQLJ iterator, which is similar to a JDBC result set.

For information about using SQLJ in JSP pages, see "OracleJSP Support for Oracle SQLJ".

For general information about Oracle SQLJ programming features and syntax, see the Oracle9i SQLJ Developer's Guide and Reference.

Code for SQLJSelectInto.sqljsp (select single row)

This example selects a single row from the database or middle-tier database cache, using SQLJ SELECT INTO syntax.

<%@ page import="sqlj.runtime.ref.DefaultContext,oracle.sqlj.runtime.Oracle" %>

<HTML>
<HEAD> <TITLE> The SQLJSelectInto JSP  </TITLE> </HEAD>
<BODY BGCOLOR=white>

<%
   String connStr=request.getParameter("connStr");
   if (connStr==null) {
     connStr=(String)session.getValue("connStr");
   } else {
     session.putValue("connStr",connStr);
   }
   if (connStr==null) { %>
<jsp:forward page="../setconn.jsp" />
<%
   }
%>

<%
   String empno = request.getParameter("empno"); 
   if (empno != null) { %>
      <H3> Employee # <%=empno %> Details: </H3>
      <%= runQuery(connStr,empno) %>
      <HR><BR>
<% }  %>

<B>Enter an employee number:</B>
<FORM METHOD=get> 
<INPUT TYPE="text" NAME="empno" SIZE=10>
<INPUT TYPE="submit" VALUE="Ask Oracle");
</FORM>
</BODY>
</HTML>
<%! 
  private String runQuery(String connStr, String empno) throws 
java.sql.SQLException {
     DefaultContext dctx = null;
     String ename = null;  double sal = 0.0;  String hireDate = null; 
     StringBuffer sb = new StringBuffer();
     try {
      dctx = Oracle.getConnection(connStr, "scott", "tiger");
      #sql [dctx] { SELECT ename, sal, TO_CHAR(hiredate, 'DD-MON-YYYY') 
                      INTO :ename, :sal, :hireDate
                      FROM scott.emp WHERE UPPER(empno) = UPPER(:empno)
      };
      sb.append("<BLOCKQUOTE><BIG><B><PRE>\n");
      sb.append("Name       : " + ename + "\n");
      sb.append("Salary     : " + sal + "\n");
      sb.append("Date hired : " + hireDate);
      sb.append("</PRE></B></BIG></BLOCKQUOTE>");

     } catch (java.sql.SQLException e) {
         sb.append("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
     } finally {
         if (dctx!= null) dctx.close();
     }
     return sb.toString();
  }
%>

Code for SQLJIterator.sqljsp (select multiple rows)

This example selects multiple rows from the database or middle-tier database cache, using a SQLJ iterator.

<%@ page import="java.sql.*" %>
<%@ page import="sqlj.runtime.ref.DefaultContext,oracle.sqlj.runtime.Oracle" %>

<!------------------------------------------------------------------
 * This is a SQLJ JavaServer Page that does a SQLJ query on the
 * emp table in schema scott and outputs the result in an html table.
 *  
--------------------------------------------------------------------!>

<%! #sql iterator Empiter(String ename, double sal, java.sql.Date hiredate) %>

<%
   String connStr=request.getParameter("connStr");
   if (connStr==null) {
     connStr=(String)session.getValue("connStr");
   } else {
     session.putValue("connStr",connStr);
   }
   if (connStr==null) { %>
<jsp:forward page="../setconn.jsp" />
<%
   }
%>

<%
   DefaultContext dctx = null;
   dctx = Oracle.getConnection(connStr, "scott", "tiger");
%>

<HTML> 
<HEAD> <TITLE> The SqljIterator SQLJSP </TITLE>  </HEAD>
<BODY BGCOLOR="E0FFF0"> 
  <% String user;
      #sql [dctx] {SELECT user INTO :user FROM dual}; 
  %>

 <H1> Hello, <%= user %>!   </H1>
 <HR>
 <B> I will use a SQLJ iterator to get employee data
     from EMP table in schema SCOTT..
 </B> 
 <P>
<%  
    Empiter emps;
    try {
      #sql [dctx] emps = { SELECT ename, sal, hiredate 
                 FROM scott.emp ORDER BY ename};
      if (emps.next()) {
%>
      <TABLE BORDER=1 BGCOLOR="C0C0C0">
      <TH WIDTH=200 BGCOLOR=white> Employee Name </TH>
      <TH WIDTH=100 BGCOLOR=white> Salary </TH>
      <TR> <TD> <%= emps.ename() %> </TD>
           <TD> <%= emps.sal() %> </TD>
      </TR>

<%      while (emps.next()) {
%>
      <TR> <TD> <%= emps.ename() %> </TD>
           <TD> <%= emps.sal() %> </TD>
      </TR>
<% } %>
      </TABLE>
<%  } else { %>
      <P> Sorry, the query returned no rows! </P>
<%    }
      emps.close();
    } catch (SQLException e) {  %>
       <P>There was an error doing the query:<PRE> <%= e %> </PRE> <P>
<%    } %>
</BODY>
</HTML>

Samples Using globals.jsa for Servlet 2.0 Environments

This section has examples of how the Oracle globals.jsa mechanism can be used in servlet 2.0 environments to provide an application framework and application-based and session-based event handling. The following examples are provided:

For information about globals.jsa usage, see "OracleJSP Application and Session Support for Servlet 2.0".

Note:

The examples in this section base some of their functionality on application shutdown. Many servers do not allow an application to be shut down manually. In this case, globals.jsa cannot function as an application marker. However, you can cause the application to be automatically shut down and restarted (presuming developer_mode=true) by updating either the lotto.jsp source or the globals.jsa file. (The OracleJSP container always terminates a running application before retranslating and reloading an active page.)

globals.jsa Example for Application Events--lotto.jsp

This sample illustrates OracleJSP globals.jsa event handling through the application_OnStart and application_OnEnd event handlers. In this sample, numbers are cached on a per-user basis for the duration of the day. As a result, only one set of numbers is ever presented to a user for a given lottery drawing. In this sample, a user is identified by their IP address.

Code has been written for application_OnStart and application_OnEnd to make the cache persistent across application shutdowns. The sample writes the cached data to a file as it is being terminated and reads from the file as it is being restarted (presuming the server is restarted the same day that the cache was written).

globals.jsa File for lotto.jsp

<%@ page import="java.util.*, oracle.jsp.jml.*" %>

<jsp:useBean id = "cachedNumbers" class = "java.util.Hashtable" scope = "application" />

<event:application_OnStart>

<%
        Calendar today = Calendar.getInstance();
        application.setAttribute("today", today);
        try {
                FileInputStream fis = new FileInputStream
                            (application.getRealPath("/")+File.separator+"lotto.che");
                ObjectInputStream ois = new ObjectInputStream(fis);
                Calendar cacheDay = (Calendar) ois.readObject();
                if (cacheDay.get(Calendar.DAY_OF_YEAR) == today.get(Calendar.DAY_OF_YEAR)) {
                        cachedNumbers = (Hashtable) ois.readObject();
                        application.setAttribute("cachedNumbers", cachedNumbers);       
                }
                ois.close();
        } catch (Exception theE) {
                // catch all -- can't use persistent data
        }
%>

</event:application_OnStart>

<event:application_OnEnd>

<%
        Calendar now = Calendar.getInstance();
        Calendar today = (Calendar) application.getAttribute("today");
        if (cachedNumbers.isEmpty() || 
                   now.get(Calendar.DAY_OF_YEAR) > today.get(Calendar.DAY_OF_YEAR)) {
                File f = new File(application.getRealPath("/")+File.separator+"lotto.che");
                if (f.exists()) f.delete();
                return;         
        }

        try {
                FileOutputStream fos = new FileOutputStream
                            (application.getRealPath("/")+File.separator+"lotto.che");
                ObjectOutputStream oos = new ObjectOutputStream(fos);
                oos.writeObject(today);
                oos.writeObject(cachedNumbers);
                oos.close();
        } catch (Exception theE) {
                // catch all -- can't use persistent data
        }
%>

</event:application_OnEnd>

lotto.jsp Source

<%@ page session = "false" %>
<jsp:useBean id = "picker" class = "oracle.jsp.sample.lottery.LottoPicker" scope = "page" />

<HTML>
<HEAD><TITLE>Lotto Number Generator</TITLE></HEAD>
<BODY BACKGROUND="images/cream.jpg" BGCOLOR="#FFFFFF">
<H1 ALIGN="CENTER"></H1>

<BR>

<!-- <H1 ALIGN="CENTER"> IP: <%= request.getRemoteAddr() %> <BR> -->
<H1 ALIGN="CENTER">Your Specially Picked</H1>
<P ALIGN="CENTER"><IMG SRC="images/winningnumbers.gif" WIDTH="450" HEIGHT="69" ALIGN="BOTTOM" 
BORDER="0"></P>
<P>

<P ALIGN="CENTER">
<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0">
<TR>
<%
        int[] picks;
        String identity = request.getRemoteAddr();

        // Make sure its not tomorrow
        Calendar now = Calendar.getInstance();
        Calendar today = (Calendar) application.getAttribute("today");
        if (now.get(Calendar.DAY_OF_YEAR) > today.get(Calendar.DAY_OF_YEAR)) {
                System.out.println("New day....");
                cachedNumbers.clear();
                today = now;
                application.setAttribute("today", today);
        }       

        synchronized (cachedNumbers) {
                if ((picks = (int []) cachedNumbers.get(identity)) == null) {
                        picks = picker.getPicks();
                        cachedNumbers.put(identity, picks);
                }
        }
        for (int i = 0; i < picks.length; i++) { 
%>
     <TD>
     <IMG SRC="images/ball<%= picks[i] %>.gif" WIDTH="68" HEIGHT="76" ALIGN="BOTTOM" BORDER="0">
     </TD>

<%
     }
%>
</TR>
</TABLE>

</P>

<P ALIGN="CENTER"><BR>
<BR>
<IMG SRC="images/playrespon.gif" WIDTH="120" HEIGHT="73" ALIGN="BOTTOM" BORDER="0">

</BODY>
</HTML>

globals.jsa Example for Application and Session Events--index1.jsp

This example uses a globals.jsa file to process applications and session lifecycle events. It counts the number of active sessions, the total number of sessions, and the total number of times the application page has been hit. Each of these values is maintained at the application scope. The application page (index1.jsp) updates the page hit count on each request. The globals.jsa session_OnStart event handler increments the number of active sessions and the total number of sessions. The globals.jsa session_OnEnd handler decrements the number of active sessions by one.

The page output is simple. When a new session starts, the session counters are output. The page counter is output on every request. The final tally of each value is output in the globals.jsa application_OnEnd event handler.

Note the following in this example:

When the counter variables are updated, access must be synchronized, as these values are maintained at application scope.
The count values use the OracleJSP oracle.jsp.jml.JmlNumber extended datatype, which simplifies the use of data values at application scope. (For information about the JML extended datatypes, see "JML Extended Datatypes".)

globals.jsa File for index1.jsp

<%@ taglib uri="oracle.jsp.parse.OpenJspRegisterLib" prefix="jml" %>

<event:application_OnStart>

      <%-- Initializes counts to zero --%>
      <jsp:useBean id="pageCount" class="oracle.jsp.jml.JmlNumber" scope = "application" />
      <jsp:useBean id="sessionCount" class="oracle.jsp.jml.JmlNumber" scope = "application" />
       <jsp:useBean id="activeSessions" class="oracle.jsp.jml.JmlNumber" scope = "application" />

      <%-- Consider storing pageCount persistently -- If you do read it here --%>

</event:application_OnStart>

<event:application_OnEnd>
      <%-- Acquire beans --%>
      <jsp:useBean id="pageCount" class="oracle.jsp.jml.JmlNumber" scope = "application" />
      <jsp:useBean id="sessionCount" class="oracle.jsp.jml.JmlNumber" scope = "application" />

      <% application.log("The number of page hits were: " + pageCount.getValue() ); %>
       <% application.log("The number of client sessions were: " + sessionCount.getValue() ); %>

      <%-- Consider storing pageCount persistently -- If you do write it here --%>

</event:application_OnEnd>

<event:session_OnStart>

      <%-- Acquire beans --%>
      <jsp:useBean id="sessionCount" class="oracle.jsp.jml.JmlNumber" scope = "application" />
      <jsp:useBean id="activeSessions" class="oracle.jsp.jml.JmlNumber" scope = "application" />
      <%
        synchronized (sessionCount) {
                sessionCount.setValue(sessionCount.getValue() + 1);
      %>
                <br>
                Starting session #: <%= sessionCount.getValue() %> <br>
      <%
        }
      %>
      <% 
        synchronized (activeSessions) {
                activeSessions.setValue(activeSessions.getValue() + 1);
      %>
                There are currently <b> <%= activeSessions.getValue() %> </b> active sessions <p>
      <%
        }
      %>

</event:session_OnStart>

<event:session_OnEnd>

      <%-- Acquire beans --%>
      <jsp:useBean id="activeSessions" class="oracle.jsp.jml.JmlNumber" scope = "application" />
      <%
         synchronized (activeSessions) {
                activeSessions.setValue(activeSessions.getValue() - 1);
        }
      %>

</event:session_OnEnd>

index1.jsp Source

<%-- Acquire beans --%>
<jsp:useBean id="pageCount" class="oracle.jsp.jml.JmlNumber" scope = "application" />

<%
   synchronized(pageCount) {
        pageCount.setValue(pageCount.getValue() + 1);
   }
%>

This page has been accessed <b> <%= pageCount.getValue() %> </b>  times.

<p>

globals.jsa Example for Global Declarations--index2.jsp

This example uses a globals.jsa file to declare variables globally. It is based on the event handler sample in "globals.jsa Example for Application and Session Events--index1.jsp", but differs in that the three application counter variables are declared globally. (In the original event-handler sample, by contrast, each event handler and the JSP page itself had to provide jsp:useBean statements to locally declare the beans they were accessing.)

Declaring the beans globally results in implicit declaration in all event handlers and the JSP page.

globals.jsa File for index2.jsp

<%-- globally declares variables and initializes them to zero --%>

<jsp:useBean id="pageCount" class="oracle.jsp.jml.JmlNumber" scope = "application" />
<jsp:useBean id="sessionCount" class="oracle.jsp.jml.JmlNumber" scope = "application" />
<jsp:useBean id="activeSessions" class="oracle.jsp.jml.JmlNumber" scope = "application" />

<event:application_OnStart>

      <%-- Consider storing pageCount persistently -- If you do read it here --%>

</event:application_OnStart>

<event:application_OnEnd>

      <% application.log("The number of page hits were: " + pageCount.getValue() ); %>
       <% application.log("The number of client sessions were: " + sessionCount.getValue() ); %>

      <%-- Consider storing pageCount persistently -- If you do write it here --%>

</event:application_OnEnd>

<event:session_OnStart>

      <%
         synchronized (sessionCount) {
                sessionCount.setValue(sessionCount.getValue() + 1);
      %>

              <br>
              Starting session #: <%= sessionCount.getValue() %> <br>

      <%
        }
      %>

      <% 
        synchronized (activeSessions) {
                activeSessions.setValue(activeSessions.getValue() + 1);
      %>
                There are currently <b> <%= activeSessions.getValue() %> </b> active sessions <p>
      <%
        }
      %>

</event:session_OnStart>

<event:session_OnEnd>

      <%
         synchronized (activeSessions) {
                activeSessions.setValue(activeSessions.getValue() - 1);
        }
      %>

</event:session_OnEnd>

index2.jsp Source

<%-- pageCount declared in globals.jsa so active in all pages --%>

<%
   synchronized(pageCount) {
        pageCount.setValue(pageCount.getValue() + 1);
   }
%>

This page has been accessed <b> <%= pageCount.getValue() %> </b>  times.

<p>

9Sample Applications

Basic Samples

Hello Page--hellouser.jsp

Usebean Page--usebean.jsp

Code for usebean.jsp

Code for NameBean.java

Shopping Cart Page--cart.jsp

Code for cart.jsp

Code for index.jsp

Information Page--info.jsp

JDBC Samples

Simple Query--SimpleQuery.jsp

User-Specified Query--JDBCQuery.jsp

Query Using a Query Bean--UseHtmlQueryBean.jsp

Code for UseHtmlQueryBean.jsp

Code for HtmlQueryBean.java

Code for HtmlTable.java

Connection Caching--ConnCache3.jsp and ConnCache1.jsp

Code for ConnCache3.jsp (with cache setup)

Code for ConnCache1.jsp and setupcache.jsp

ConnCache1.jsp

setupcache.jsp

Data-Access JavaBean Samples

Page Using DBBean--DBBeanDemo.jsp

Page Using ConnBean--ConnBeanDemo.jsp

Page Using CursorBean--CursorBeanDemo.jsp

Page Using ConnCacheBean--ConnCacheBeanDemo.jsp

Custom Tag Samples

JML Tag Sample--hellouser_jml.jsp

Code for hellouser_jml.jsp (using JML tags)

Code for hellouser.jsp (not using JML tags)

Pointers to Additional Custom Tag Samples

Samples for Oracle-Specific Programming Extensions

Page Using JspScopeListener--scope.jsp

Listener Implementation--PageEventDispatcher

scope.jsp Source

XML Query--XMLQuery.jsp

SQLJ Queries--SQLJSelectInto.sqljsp and SQLJIterator.sqljsp

Code for SQLJSelectInto.sqljsp (select single row)

Code for SQLJIterator.sqljsp (select multiple rows)

Samples Using globals.jsa for Servlet 2.0 Environments

globals.jsa Example for Application Events--lotto.jsp

globals.jsa File for lotto.jsp

lotto.jsp Source

globals.jsa Example for Application and Session Events--index1.jsp

globals.jsa File for index1.jsp

index1.jsp Source

globals.jsa Example for Global Declarations--index2.jsp

globals.jsa File for index2.jsp

index2.jsp Source

9
Sample Applications