Connection Pooling samples

[Timothy Sam]

Hi guys! I just read about Tomcat's connectio pooling. I don't have any more questions about configuration right now... What I need to know is how to use the connection pool in my code. In the tomcat docs they had this snippet

Connection conn = null;
  Statement stmt = null;  // Or PreparedStatement if needed
  ResultSet rs = null;
  try {
    conn = ... get connection from connection pool ...
    stmt = conn.createStatement("select ...");
    rs = stmt.executeQuery();
    ... iterate through the result set ...
    rs.close();
    rs = null;
    stmt.close();
    stmt = null;
    conn.close(); // Return to connection pool
    conn = null;  // Make sure we don't close it twice
  } catch (SQLException e) {
    ... deal with errors ...
  } finally {
    // Always make sure result sets and statements are closed,
    // and the connection is returned to the pool
    if (rs != null) {
      try { rs.close(); } catch (SQLException e) { ; }
      rs = null;
    }
    if (stmt != null) {
      try { stmt.close(); } catch (SQLException e) { ; }
      stmt = null;
    }
    if (conn != null) {
      try { conn.close(); } catch (SQLException e) { ; }
      conn = null;
    }
  }

But it doesn't give a clear explanation like how do I get a connection? The line below doesn't tell anything about this.

conn = ... get connection from connection pool ...

Also, would I happen to have a single object that does all database transactions such as insert, edit, delete. I have several methods in it. Would it be a wise idea to close the connection before the method ends or return a value? Or would it be better if I close the connection before the servlet forwards/redirects to a JSP or don't close the connection at all?

[Jaikiran Pai]

conn = ... get connection from connection pool ...

I believe you have created a datasource in Tomcat for connection pooling. If so, then to get the connection from the pool, you will do a lookup of the datasource and invoke the getConnection method on the Datasource object.
Something like:

Datasource ds = ctx.lookup(jndiNameOfDatasource);
ds.getConnection();

[Timothy Sam]

May I ask if you could show me a complete sample? Yes, I am about to create a datasource in Tomcat. About the Context type you used in your code is it similar to the context interface provided by Sun?

http://java.sun.com/j2se/1.4.2/docs/api/javax/naming/Context.html

I happen to be following another example right here...

http://marc.theaimsgroup.com/?l=tomcat-user&m=102225547106556&w=2

but I am really confused with the lines

           Context ctx = new InitialContext();
            if(ctx == null ) throw new Exception("Boom - No Context");
 
            DataSource ds =
(DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");

Hmmm...

[Jaikiran Pai]

<Resource name="jdbc/TestDB" auth="Container"
type="javax.sql.DataSource"/>

Your datasource definition will be something like the above (I copied this from the code that you pointed me to).

Tomcat will then create a Datasource for you and bind it at: java:comp/env/theNameThatYouHaveSpecifiedForYourDatasource. In this case it will be java:comp/env/jdbc/TestDB.

Now whenever you want to access this datasource through your code, you will be using javax.naming.Context and doing a lookup for the datasource.

So your code will look like:

Context ctx = new InitialContext();     
DataSource ds =(DataSource)ctx.lookup("java:comp/env/theNameThatYouHaveSpecifiedForYourDatasource");