Oracle JavaServer Pages Developer's Guide and Reference Release 8.1.7 Part Number A83726-01 |
|
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:
|
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).
<%@ 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:
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|