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

• XML Query--XMLQuery.jsp

• SQLJ Queries--SQLJSelectInto.sqljsp and SQLJIterator.sqljsp

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, 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 Oracle8i 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 JServer, 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.

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 Oracle8i SQLJ Developer's Guide and Reference.

Code for SQLJSelectInto.sqljsp (select single row)

This example selects a single row from the database, 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, 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>

---