Dose Tomcat 5.5.9 support Database Connection Caching over application scope?

If you really want to use an application wide connection pool then I suggest you do it via a JNDI lookup such as here http://technology.amis.nl/blog/index.php?p=264

Hi,
Thanks for your reply. I got a question about " application wide" and "session wide" database connection.

what dose "application wide" exactly mean? suppose I have an online bookstore project. All the files related to this project are located in a directory called bookStoreProject. This project can be called an "Application", right?
Does this "Application" here have the same meaning as the "application" in "application wide" ?

When we say "session wide" database connection, does this mean the "bookstore Application"
has to use session?

will have some question about JNDI lookup in the link http://technology.amis.nl/blog/index.php?p=264 in my next posting.

Thanks so much for your reply,
Emily

>>All the files related to this project are located in a directory called bookStoreProject. This project can be called an "Application", right?

yes

>>Does this "Application" here have the same meaning as the "application" in "application wide" ?

yes

If you have some object that has application wide scope, it means that it can be used across the entire application. An example of this would be a database connection pool (dbcp). This dbcp could be a collection of 10 existing db connections created at the start-up of an application that can be used by any user as they view the jsps/servlets. If a user needs one, it is assigned to them and then returned back to the connection pool when a query is completed.

>>When we say "session wide" database connection, does this mean the "bookstore Application"
has to use session?

Yes. A object stored in session would be availalble to a single user who 'owns' that session. Other users would not be able to use it. If we were using db connections as an example, we would create a single db connection and save it in the session. This would then be re-used by the user while the remain on the site. Other users would require their own connections however as sessions are not shared.

You can look at it like this for the 4 scopes/contexts of JSP/servlets (application, sessions, request, page):

There is only ONE application object per web application
    |
There can be many sessions but only one per user
    |
A single user may make multiple request
    |
A single request may cover multiple pages (because of includes)

So depending on where you store your objects, you can restrict access or make more efficient use of them.

The best option for DBCP is in the application as you are already doing. The JNDI look up is just the way in which you acess the Connections of a DBCP.

Hi,
Thanks so much for your reply. Really helpful!

"This dbcp could be a collection of 10 existing db connections created at the start-up of an application that can be used by any user as they view the jsps/servlets."

Question1: Dose this mean there will be only 10 user can access the application at the same time? certainly the connection number 10 can be changed to a greater number.

Question2: "be used by any user as they view the jsps/servlets". If the db connection is "session wide", it can also be used by any user, as long as they can get their own session, is this right?

Hi,
I'm actually practicing some JSP examples in the link:

http://www.oracle.com/technology/sample_code/tech/java/jsps/index.html,
in directory: jsp\src\web\ojspdemos-web\jsp\sql\jdbc, there is an example called :ConnCache1.jsp

there are three file realted to the application:

file1: setupcache.jsp
-------------------------------------------------------------------------------------------------------
<%
   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="cods" class="oracle.jdbc.pool.OracleConnectionCacheImpl"
  scope="application">
<%
    cods.setURL(connStr);
    cods.setUser("scott");
    cods.setPassword("tiger");
    cods.setStmtCacheSize(5);
%>
</jsp:useBean>
-----------------------------------------------------------------------------------------------------

file2: setconn.jsp
---------------------------------------------------------------------------------------------------
<body bgcolor="#FFFFFF">

<%
   String connStr;
   connStr=request.getParameter("connStr");
   if (connStr==null) {
     connStr=(String)session.getValue("connStr");
   }
   if (connStr==null || connStr.equals(null)) {
     connStr="jdbc:oracle:thin:@localhost:1521:orcl";  // default connection str
%>
<font size=+0>
<B>Please enter a suitable JDBC connection string, before you try the demos below</B>
<pre>
     To use the thin driver insert your host, port and database id.
     Once you have set the connection string it will remain in effect until
     the session times out for most demos.  For Connection Cache demos
     which use application scope on most servlet engines the connection
     string will remain in effect for the life of the application.

</pre>
<FORM METHOD=get>
<INPUT TYPE="text" NAME="connStr" SIZE=40 value="<%=connStr%>" >
<INPUT TYPE="submit" VALUE="Set Connection String" >
</FORM>
</font>
<%   }

   session.putValue("connStr",connStr);
%>
---------------------------------------------------------------------------------------------------------

file3: ConnCache1.jsp
--------------------------------------------------------------------------------------------------------
<%@ 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 ontained 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>
--------------------------------------------------------------------------------------------------------

From setconn.jsp, we can see that the db connection sting is put in a session"connStr"
but in file setupcache.jsp,  it's using a " application" scope.

Is this something wrong about this, since the db connection string is already stored in one session varable?

Thanks so much for your reply,
Emily

Question1: Dose this mean there will be only 10 user can access the application at the same time? certainly the connection number 10 can be changed to a greater number.

That's 10 simultaneous users accessing a database. That translates into about 1000 concurrent users

Question2: "be used by any user as they view the jsps/servlets". If the db connection is "session wide", it can also be used by any user, as long as they can get their own session, is this right?

Yes

"That's 10 simultaneous users accessing a database. That translates into about 1000 concurrent users."

Sorry, I had to ask. How 10 simultaneous users translates into about 1000 concurrent users?

Each time you use a connection you only use it for a short period, say 0.1 seconds. With each page having a viewing time of 10 seconds, you can see that 10 connections will deal with that number of users.

It's a little simplistic but a good enough example. Part of testing a web app is simulating user numbers and concurrent requests. This is where you tune the number of connections in your pool.

Hi,
Thanks for your rely.

Q1
Still need to ask. No matter the db connection is application wide or session wide, first of all, I need to asign the maxium number is 10(for example) in one file ? If so, in which file I should put this number?

Q2
"10 connections will deal with that number of users."
Suppose in one online bookstore, after user obtain a db connection, this user used 30 minutes to buy a book, then log out. Only if this user log out from the application, he can return the connection? So I still think 10 connection can not be translated into 100, right?

Q3
Somehow I think the example I posted in my previous posting is misleading, because it used a session variable to store the connection string, but in file setupcache.jsp,  it's using a " application" scope. Would you please read that posting?

Thanks so much for your reply,
Emily