
<%@ 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.
--------------------------------------------------------------------!>

<%
   String connStr=request.getParameter("connStr");
   if (connStr==null) {
     connStr=(String)session.getValue("connStr");
   } else {
     session.putValue("connStr",connStr);
   }
   if (connStr==null) { %>
<jsp:forward page="../setconn.jsp" />
<%
   }
%>

<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="<%= 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();
    } catch (SQLException e) {
      out.println("<P>" + "There was an error doing the query:");
      out.println ("<PRE>" + e + "</PRE> \n <P>");
    }
%>

 </BODY>
</HTML>
