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

## 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:

- Simple Query--SimpleQuery.jsp

- User-Specified Query--JDBCQuery.jsp

- Query Using a Query Bean--UseHtmlQueryBean.jsp

- Connection Caching--ConnCache3.jsp and ConnCache1.jsp

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>

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