Oracle JavaServer Pages Developer's Guide and Reference Release 8.1.7 Part Number A83726-01 |
|
This section provides examples using the Oracle database-access JavaBeans. These beans are provided with OracleJSP but are generally portable to other JSP environments. Note, however, that the connection caching bean relies on the Oracle JDBC implementation of connection caching.
DBBean
is the simplest of these JavaBeans, with its own connection functionality and supporting queries only. For more complicated operations, use appropriate combinations of ConnBean
(for simple connections), ConnCacheBean
(for connection caching), and CursorBean
(for general SQL DML operations).
For more information, see "Oracle Database-Access JavaBeans".
The following examples are included:
Oracle also provides custom tags for SQL functionality that use these JavaBeans behind the scenes. For samples using these tags, see "SQL Tag Examples".
Note:
This page uses a DBBean
object to connect to the database, execute a query, and output the results as an HTML table.
<%@ page import="java.sql.*" %> <!------------------------------------------------------------------ * This is a basic JavaServer Page that uses a DB Access Bean and queries * dept and emp tables in schema scott and outputs the result in an html table. * --------------------------------------------------------------------!> <jsp:useBean id="dbbean" class="oracle.jsp.dbutil.DBBean" scope="session"> <jsp:setProperty name="dbbean" property="User" value="scott"/> <jsp:setProperty name="dbbean" property="Password" value="tiger"/> <jsp:setProperty name="dbbean" property="URL" value= "<%= (String)session.getValue(\"connStr\") %>" /> </jsp:useBean> <HTML> <HEAD> <TITLE> DBBeanDemo JSP </TITLE> </HEAD> <BODY BGCOLOR=EOFFFO> <H1> Hello <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %> ! I am DBBeanDemo JSP. </H1> <HR> <B> I'm using DBBean and querying DEPT & EMP tables in schema SCOTT..... I get all employees who work in the Research department. </B> <P> <% try { String sql_string = " select ENAME from EMP,DEPT " + " where DEPT.DNAME = 'RESEARCH' " + " and DEPT.DEPTNO = EMP.DEPTNO"; // Make the Connection dbbean.connect(); // Execute the SQL and get a HTML table out.println(dbbean.getResultAsHTMLTable(sql_string)); // Close the Bean to close the connection dbbean.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 uses a ConnBean
object (for a simple connection) to retrieve a CursorBean
object, then uses the CursorBean
object to output query results as an HTML table.
<%@ page import="java.sql.* , oracle.jsp.dbutil.*" %> <!------------------------------------------------------------------ * This is a basic JavaServer Page that uses a Connection Bean and queries * emp table in schema scott and outputs the result in an html table. * --------------------------------------------------------------------!> <jsp:useBean id="cbean" class="oracle.jsp.dbutil.ConnBean" scope="session"> <jsp:setProperty name="cbean" property="User" value="scott"/> <jsp:setProperty name="cbean" property="Password" value="tiger"/> <jsp:setProperty name="cbean" property="URL" value= "<%= (String)session.getValue(\"connStr\") %>"/> <jsp:setProperty name="cbean" property="PreFetch" value="5"/> <jsp:setProperty name="cbean" property="StmtCacheSize" value="2"/> </jsp:useBean> <HTML> <HEAD> <TITLE> Connection Bean Demo JSP </TITLE> </HEAD> <BODY BGCOLOR=EOFFFO> <H1> Hello <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %> ! I am Connection Bean Demo JSP. </H1> <HR> <B> I'm using connection and a query bean and querying employee names and salaries from EMP table in schema SCOTT.. </B> <P> <% try { // Make the Connection cbean.connect(); String sql = "SELECT ename, sal FROM scott.emp ORDER BY ename"; // get a Cursor Bean CursorBean cb = cbean.getCursorBean (CursorBean.PREP_STMT, sql); out.println(cb.getResultAsHTMLTable()); // Close the cursor bean cb.close(); // Close the Bean to close the connection cbean.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 uses a ConnBean
object (for a simple connection) and a CursorBean
object to execute a PL/SQL statement, get a REF CURSOR
, and translate the results into an HTML table.
<%@ page import="java.sql.* , oracle.jsp.dbutil.*" %> <!------------------------------------------------------------------ * This is a basic JavaServer Page that uses a Cursor and Conn Beans and queries * dept table in schema scott and outputs the result in an html table. * --------------------------------------------------------------------!> <jsp:useBean id="connbean" class="oracle.jsp.dbutil.ConnBean" scope="session"> <jsp:setProperty name="connbean" property="User" value="scott"/> <jsp:setProperty name="connbean" property="Password" value="tiger"/> <jsp:setProperty name="connbean" property="URL" value= "<%= (String)session.getValue(\"connStr\") %>" /> </jsp:useBean> <jsp:useBean id="cbean" class="oracle.jsp.dbutil.CursorBean" scope="session"> <jsp:setProperty name="cbean" property="PreFetch" value="10"/> <jsp:setProperty name="cbean" property="ExecuteBatch" value="2"/> </jsp:useBean> <HTML> <HEAD> <TITLE> CursorBean Demo JSP </TITLE> </HEAD> <BODY BGCOLOR=EOFFFO> <H1> Hello <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %> ! I am Cursor Bean JSP. </H1> <HR> <B> I`m using cbean and i'm quering department names from DEPT table in schema SCOTT.. </B> <P> <% try { // Make the Connection connbean.connect(); String sql = "BEGIN OPEN ? FOR SELECT DNAME FROM DEPT; END;"; // Create a Callable Statement cbean.create ( connbean, CursorBean.CALL_STMT, sql); cbean.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR); // Execute the PLSQL cbean.executeUpdate (); // Get the Ref Cursor ResultSet rset = cbean.getCursor(1); out.println(oracle.jsp.dbutil.BeanUtil.translateToHTMLTable (rset)); // Close the RefCursor rset.close(); // Close the Bean cbean.close(); // Close the connection connbean.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 uses a ConnCacheBean object to obtain a connection from a connection cache. It then uses standard JDBC functionality to execute a query, formatting the results as an HTML table.
<%@ page import="java.sql.*, javax.sql.*, oracle.jsp.dbutil.ConnCacheBean" %> <!------------------------------------------------------------------ * 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. * Uses Connection Cache Bean. --------------------------------------------------------------------!> <jsp:useBean id="ccbean" class="oracle.jsp.dbutil.ConnCacheBean" scope="session"> <jsp:setProperty name="ccbean" property="user" value="scott"/> <jsp:setProperty name="ccbean" property="password" value="tiger"/> <jsp:setProperty name="ccbean" property="URL" value= "<%= (String)session.getValue(\"connStr\") %>" /> <jsp:setProperty name="ccbean" property="MaxLimit" value="5" /> <jsp:setProperty name="ccbean" property="CacheScheme" value= "<%= ConnCacheBean.FIXED_RETURN_NULL_SCHEME %>" /> </jsp:useBean> <HTML> <HEAD> <TITLE> SimpleQuery JSP </TITLE> </HEAD> <BODY BGCOLOR=EOFFFO> <H1> Hello <%= (request.getRemoteUser() != null? ", " + request.getRemoteUser() : "") %> ! I am Connection Cache Demo Bean </H1> <HR> <B> I will do a basic JDBC query to get employee data from EMP table in schema SCOTT. The connection is obtained from the Connection Cache. </B> <P> <% try { Connection conn = ccbean.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(); ccbean.close(); } catch (SQLException e) { out.println("<P>" + "There was an error doing the query:"); out.println ("<PRE>" + e + "</PRE> \n <P>"); } %> </BODY> </HTML>
|
Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|