[OFF-TOPIC]Yoav --> RE: Some pretty basic Tomcat Connection Pooling Questions????

["Luke (Terry) Vanderfluit" <lu...@chipcity.com.au>]

Hi Yoav and all,
> - Hold the connection for as little a time as possible.  That is, get it
> from the pool, use it, and return it to the pool as soon as possible.
> - That means you don't get the connection in a servlet init() method and
> return it in a destroy() method.  That's too long a time to hold
> connections usually.
> - Always return connections to the pool.  Typically, this is done in the
> finally clause of a try/catch block to ensure it is executed even if the
> JDBC operation fails.
> - You create your pool once, preferably when the app starts up, and you
> close the pool itself once, preferably when the app shuts down.  A
> ServletContextListener is good for this purpose.  Make sure you close
> the pool.
> - Servlets and other classes use the pool as needed.  You don't have to
> use a one connection per servlet design (in fact those usually don't
> work in the real world).

I've been using connection pooling via an init() method.
Above you say that one should avoid that.
So now I have put the connection code inside the method that actually
does the database work and dispensed with the init() method.

Before I made this change I didn't have a conn.close() statement or a
finally clause anywhere either (I DID have resultset.close and
statement.close()).
Now I have added a conn.close() statement after each database
interaction has been completed. 

Both versions of the code work. 
Would you say the it is optimised after having gotten rid of the init()
method?

this is the code in it's latter version:
~~~~~~~~~~~~~~~~~~~
      try {               //establish the connection
         Context ctx = new InitialContext();
         if(ctx == null) {
            throw new Exception("No Context");
            }
         DataSource ds =
(DataSource)ctx.lookup("java:comp/env/jdbc/mb");
         if(ds != null) {
            conn = ds.getConnection(); //conn is a global variable
            if(conn != null) {
               message = "Got Connection to DB " + conn.toString();
               }
            }
         } // end try block
      catch(Exception e) {
         e.printStackTrace();
         }
      try {              //carry out the database query
         Statement stmt = conn.createStatement();
         ResultSet rst = stmt.executeQuery("select * from category where
categoryname not like '%Timetable%';");
         while(rst.next()) {
            Category c  = new Category();
            c.setCategoryName(rst.getString("categoryname"));
            clBean.addCategory(c);
            } // end while block
         rst.close();
         stmt.close();
         conn.close();
         } // end try block
      catch(Exception e) {
         }
~~~~~~~~~~~~~~~~~~~~~~~~~~

Appreciate your help,
kind regards,
Luke

-- 
========================
Luke (Terry) Vanderfluit 
Mobile: 0421 276 282     
========================


---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org

AW: [OFF-TOPIC]Yoav --> RE: Some pretty basic Tomcat ConnectionPooling Questions????

[SH Solutions <sh...@gmx.net>]

Hi

Sorry, I haven't read the whole treads, but:

> Before I made this change I didn't have a conn.close() statement or a
finally clause anywhere either (I DID have resultset.close and
statement.close()).

Never never do that.
If you do not call connection.close(), the connection is NOT returned to the
pool. So ALWAYS close().
(Maybe you are lucky and there are some special precautions of the pool
driver, that make this work, but most drivers do not.)

Regards,
  Steffen


---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org