connection pooling

[sde]

i'm trying to optimize this web app i'm making.

i'm using native jdbc drivers to access our iseries db2 database.

is there any good links to examples of how to enable a connection pool?

i've googled for a while today, but i keep finding very random information and i'm not sure which is the best way to go.

[Belisarius]

Documentation is sparse, but this is the class Tomcat uses to setup a connection pool, and I've successfully set it up in a standalone context (no pun intended).

http://jakarta.apache.org/commons/dbcp/

If you're connecting via a Tomcat app, use the built in connection pool, otherwise try DBCP. I have some code examples if you'd like them.

[sde]

thanks b, .. that is good info .. i found a ConnectionPool class in the ibm package for the iseries.

i just need to figure out how to declare the datbase and all the parameters in a datasource object the way i do in the DriverManager object.

thanks for the suggestions .. i'm gonna try this ibm class first. i can't find documentation on it though .. if i can't figure it out, i think i'll try the built in tomcat pool you mentioned.

thanks.

[sde]

got it working now. for the sake of anyone searching the forums in the future, here's the solution i found.

import com.ibm.as400.access.*;
import java.sql.*;

public class test{
  // declare data source and con
  private static AS400JDBCConnectionPoolDataSource ds;
  private Connection con;

  // constructor
  test(){
    ds = new AS400JDBCConnectionPoolDataSource("myAS400","user","pass");
    ds.setLibraries("library");
    ds.setDateFormat("iso");
  }

  // get connection from the pool
  private Connection getConnection(){
    Connection c = null;
    
    try{      
      javax.sql.PooledConnection pc = ds.getPooledConnection();
      c = pc.getConnection();

    }catch(Exception e){
      //
    }
    
    return c;
  }
}

this was done by experimenting, trial and error .. so if someothing looks bad, someone please let me know =)

[Belisarius]

It looks about right, but I might suggest some tips I've learned from doing JDBC stuff.

I'll post two code segments, first my DBConnection class. This contains a little bit of debugging code to help you find out if you have a connection leak, that is where you open a connection but never close it.

Second, it allows you declare the connection without having to worry about catching an SQLException. This is important later on.

Third, it eliminates the need for the creation of Statement objects. Depending on how much code queaking you do, this can be a help or a hinderance. For my purposes, this works fine.

Fourth, it makes sure everything gets closed. Otherwise you need to keep track of everything, and it can be a pain.

import java.sql.*;
import javax.sql.*;
import java.util.*;

public class DBConnection{
  
  private static int count = 0;
  private static int max = 0;
  private Connection conn;
  private Statement stmt;
  private ResultSet rset;
  
  public DBConnection(){}
  
  public void init() throws SQLException{
    if(conn == null || conn.isClosed()){
      count++;
      // Useful for finding connection leaks.
      if(count > max){
        max = count;
        System.out.println("Max connections: " + max); 
      }
      // Create Connection here.
  }
  
  public ResultSet executeQuery(String query) throws SQLException{
    init();
    stmt = conn.createStatement();
    rset = stmt.executeQuery(query);
    return rset;
  }
  
  public void executeUpdate(String query) throws SQLException{
    init();
    stmt = conn.createStatement();
    stmt.executeUpdate(query);
  }
  
  //
  // ONLY CLOSE WHEN YOU ARE DONE WITH A RESULT SET! 
  //
  
  public void close(){
    if(rset != null){
      try{
        rset.close();
      }catch(SQLException sqle){}
      rset = null;
    }
    if(stmt != null){
      try{
        stmt.close();
      }catch(SQLException sqle){}
      stmt = null;
    }
    if(conn != null){
      try{
        conn.close();
      }catch(SQLException sqle){}
      conn = null;
      count--;
    }
  }
}

[Belisarius]

Here's how I apply that DBConnection class.

QueryFactory is a class where I put all my SQL queries for easy-access. If someone comes in off the street and needs to update a query, it's easier for them to find if they're in a central location. You might want to create several specific factories (they aren't really factories, I just called them that), as in my case my QueryFactory has gotten a little too big to be easy to work with.

Now, be sure to notice the "finally" at the end. That tells Java to make sure to put forth every possible effort to execute what's inside that block, in this case the closing of the connection. If you didn't use the DBConnection wrapper class, all this would need to take place inside a larger "try-catch" to catch SQLExceptions, which might pre-empt the closing of the connection. By making sure that the contructor and the close method don't throw exceptions (the close method does everything possible to ensure that the connection is closed), you can put it outside the try-catch for the SQLExceptions that could occur when actually querying/processing.

    public void myMethod(String arg){
      String query = QueryFactory.getQuery(arg);
      DBConnection conn = new DBConnection();
      try{
        ResultSet rset = conn.executeQuery(query);
        while(rset.next()){
          // do stuff
        }
      }catch(SQLException sqle){
        System.out.println(query);
        sqle.printStackTrace();
        // You might choose to re-throw the exception so you can 
        // do higher-level error handling for the user.
      }finally{
        conn.close();
      }              
    }

[sde]

hi bel and thanks for the code , ..

question: is there something wrong with using javax.sql.PooledConnection ?

correct me if i'm wrong, but it looks like your code would pool a connection when the web page is executing, .. and the connection would probably close before the page was requested again.

i need the connection to stay open for let's say 60 seconds. any other pages requested by the same user ( or different users ) will use this one open connection.

does your example do that? i've been working with the PooledConnection object and it seems to enhance performance a bit, but i'm not sure if it keeps the connection open between pages.

if my database manager object goes out of scope ( when the web page is completed ) then i'm not sure how the next request knows that a connection already exists.

i'm not being very clear because i'm not sure what to ask, .. but do you have any thoughts on this?

[sde]

ok, i have worked with this throughout the day.

i want the Pool to be an Application setting so to speak.

currently, everytime the page reloads, the pool and connection is created all over again. this takes about 2 seconds.

after the page is loaded, all my other queries use the same open connection so that is fine.

is there a way to maintain the state of the connection pool between the web server and database? ( it resides across the network )

[Belisarius]

PooledConnection is an interface, and can't actually be used (unless you get it from some other object). The only implementing class listed in the API is a bit daunting, and I'm not really sure what it's for.

What I posted wasn't a pool, but a wrapper around a Connection. If you were using a pool, you'd get the connection from the pool at "// Create Connection here." The pool would handle the bookeeping for the connections, such as keeping them open to increase efficency.

You wouldn't want to keep a single connection open for a page, as it wouldn't be thread-safe, and a second query might go through before you've finish processing the results of the first. You either need to queue the requests, or create seperate connections (I'd suggest the latter).

Basically, the ideal situation is that you have this abstract pool of database connections. They're established, so there isn't any overhead of making a new connection for each request. When a page or object needs to query the DB, it requests one of these connections from the pool. When it's done with the query, it hands it back. You've achieved the same goal as keeping an open connection in each page, but you've simplified your life and written better code.

[sde]

ok thanks, that helps. i do have an object which i'm using which returns a PooledConnection.

my pages are taking 2-4 seconds to load, and 2 of those seconds are the connection opening.

your words have me definately thinking of alternative ways .. i'll keep testing times through tomorrow.

in case you were curious, here is a snippet of my class:

private static AS400JDBCConnectionPoolDataSource ds;
private static javax.sql.PooledConnection pool;
private Connection con;

public as400(){    
  try{      
    ds = new AS400JDBCConnectionPoolDataSource("10.10.10.1","USER","PASS");
    ds.setLibraries("RC1380BFR1");
    ds.setDateFormat("iso");
          
    pool = ds.getPooledConnection();

    }catch(Exception e){
      System.out.println("as400() " + e.toString());
    }
  }

  private Connection getConnection(){
  
  Connection c = null;
  
  try{ 
    c = pool.getConnection();
    
  }catch(Exception e){
    System.out.println("getConnection() " + e.toString());
  }
  
  return c;
}

and here is how i get the connection

if(con == null || con.isClosed())
  con = pool.getConnection();

[Belisarius]

Are you sure it's the connection opening that's taking so long, as opposed to the running of the query? Even when I create independant connections it doesn't take me that long. However, sometimes when I have a complex query it takes a while.

[Belisarius]

Hmmm, looking this over, I think I see a problem. You have the right idea when declaring "ds" and "pool" static; you only want one pool. But, look at how you're getting it. According to your code, you need to say

  as400 pool = new as400();
  Connection conn = pool.getConnection();

Now, ignoring for the moment the fact that should say AS400 instead of as400, what happens is because you're calling the constructor you're creating a new pool each time. Quite frankly, I'm amazed you're only having overhead issues, I thought that would have caused some concurrency issues.

Here's how you need to rewrite your pool:

private static AS400JDBCConnectionPoolDataSource ds;
private static javax.sql.PooledConnection pool;
// private Connection con; - You don't need this here.

//  public AS400(){} You don't need a constructor for this class.

  static private void init() throws SQLException {
    if(pool == null){
      ds = new AS400JDBCConnectionPoolDataSource("10.10.10.1","USER", "PASS");
      ds.setLibraries("RC1380BFR1");
      ds.setDateFormat("iso");
          
      pool = ds.getPooledConnection();
    }
  }

  static public Connection getConnection() throws SQLException{
    init();
    return pool.getConnection();
  }

I decided to throw the SQLExceptions, you can catch them if you'd like. Do you have an online API for this pool? I find it odd that they call the method to get the pool, getPooledConnection(), as opposed to getPool() or something like that.

[sde]

yes, it was the constructor that was taking all the time.

this is excellent now! with the javax connection pool object, the first connection was the same ( 2 seconds ) then each new page was 1/2 second. this is more like the results i was expecting.

the problem with that is that it only lasted for about 3 to 4 pages, then wouldn't work anymore.

well the api provides an as400connectionpool object, but it was too slow before. now that i know how to use the object efficiently, it works great!

the initial connection is slower, but the pool continues to work. the as400 pool has a lot of other options such as pool.fill to set how many connections to make available, and i can set a timeout limit too.

if you are still interested in looking, here are the ibm access classes i'm using:
http://publib.boulder.ibm.com/iserie...zahh/page1.htm

last question, i have seen methods named init() before .. is there anything special about naming a method init() ? or just a common practice.

thanks SO much for that help! i was getting nervous that the performance of my apps would be slow. i'm sure my users will appreciate it too!

[Belisarius]

Just common practice (as far as I'm concerned). It's just a nice, short, discriptive method name.

And the JSP will take a couple seconds to load up regardless of the pool, as the JSP needs to be compiled into a servlet.

[sde]

next i need to learn how to make my jsp correct .. heh, it's messy. i'm coding inline a lot.

all my data access stuff is in classes, ..but i don't know how to write plain scripting logic with tags yet .. i have so much going on, i'm not sure i can.