Basics

JSP Starter Sample for Database Access

Chapter 1, "General Overview", provides a couple of simple JSP examples; however, if you are using OracleJSP, you presumably want to access an Oracle database. This section offers a more interesting sample that uses standard JDBC code in a JSP page to perform a query.

Because the JDBC API is simply a set of Java interfaces, JavaServer Pages technology directly supports its use within JSP scriptlets.

Notes:

Oracle JDBC provides several driver alternatives: 1) JDBC OCI drivers for use with an Oracle client installation; 2) a 100%-Java JDBC Thin driver that can be used in essentially any client situation (including applets); 3) a JDBC server-side Thin driver to access one Oracle database from within another Oracle database; and 4) a JDBC server-side internal driver to access the database within which the Java code is running (such as from a Java stored procedure or Enterprise JavaBean). For more information about Oracle JDBC, see the Oracle8i JDBC Developer's Guide and Reference.

OracleJSP also supports SQLJ (embedded SQL in Java) for static SQL operations and provides custom JavaBeans and custom SQL tags for database access. These features are discussed in Chapter 5, "OracleJSP Extensions".

The following example creates a query dynamically, from search conditions that the user enters through an HTML form (typed into a box and entered with an Ask Oracle button). To perform the specified query, it uses JDBC code in a method called runQuery() that is defined in a JSP declaration. It also defines a method formatResult() within the JSP declaration to produce the output. The runQuery() method uses the scott schema with password tiger. (JDBC is used instead of SQLJ because the query is formed dynamically. SQLJ is for static SQL.)

The HTML INPUT tag specifies that the string entered in the form be named cond. Therefore, cond is also the input parameter to the getParameter() method of the implicit request object for this HTTP request, and the input parameter to the runQuery() method (which puts the cond string into the WHERE clause of the query).

Notes:

Another approach to this example would be to define the runQuery() method in <%...%> scriptlet syntax instead of <%!...%> declaration syntax.

This example uses the JDBC OCI driver, which requires an Oracle client installation. If you want to run this sample, use an appropriate JDBC driver and connection string.

<%@ page language="java" 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>
      <B> <%= runQuery(searchCondition) %> </B> <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>

<%-- Declare and define the runQuery() method. --%>
<%! 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("jdbc:oracle:oci8:@",
                                           "scott", "tiger");
        stmt = conn.createStatement();
        // dynamic query
        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>"); 
            do {  sb.append("<LI>" + rset.getString(1) + 
                            " earns $ " + rset.getInt(2) + ".</LI>\n");
            } while (rset.next());
           sb.append("</UL>"); 
    }
    return sb.toString();
  }
%>

The following graphics illustrate sample input and output: