Sample Applications

JDBC Samples

Examples in this section use JDBC to query a database. 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 Oracle8i 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 whening running inside Oracle 8i
      // 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 Oracle8i 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>