Oracle JavaServer Pages Developer's Guide and Reference Release 8.1.7 Part Number A83726-01 |
|
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.
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>
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(); } %>
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
.
<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>
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(); } }
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(); } }
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 |
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>
This sample page statically includes another page, setupcache.jsp
, for its connection cache setup. Code is provided for both pages.
<%@ 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>
<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. |
|