JDBC fundamental concepts, Pooling and Threading

Question

I was always using JDBC in JavaSE on single-threaded environment. But now I need to use a connection pool and let many threads to have interaction with the database (MSSQL and Oracle) and I am having a hard time trying to make it as it seems that I am lacking some fundamental undestanding of the api.

AFAIK after connect and logging a Connection represents a phisical tcp/ip connection to the database. It creates Statement(s) that can be seen as SQL interaction(s) with the database over the Connection.

• Where does the transaction and rollback comes in ? Is it at the Connection or Statement level.
• Is it safe that 'one' Connection create N statements and give it to diferent threads so to let each one own the use of that Statement ?

If not, and after configuring the pool something like this:

OracleDataSource ods = new OracleDataSource(); 
ods.setURL("jdbc:oracle:thin:@tnsentryname");
ods.setUser("u");
ods.setPassword("p");

• BTW, where do I set the connection pool size ?

• Is this what I would be doing in each thread in order to correctly use the connection ?

//thead run method

Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("the sql");
// do what I need to do with rs
rs.close();
int updateStatus = stmt.executeUpdate("the update");
stmt.close();
conn.close();

// end of thread run method

• If any physical Connection of the Pool somehow crashes or disconects, will the pool automaticaly try to reconnect and inject the new connection in the pool so that subsequent pool.getConnection() will just get a health connection ?

Thanks a lot and forgive my bad english please.

Answer 1

1. Transactions happen at the connection level.

2. No. Usually, the JDBC driver will make sure that you can't execute a second statement over the same connection while another one is active.

If you need connection pooling, try the DBCP framework. It offers pretty decent failure handling (like noticing stale connections and connections that haven't been returned by client code).

As for your code: Always wrap the code in try{...}finally{...}:

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
     conn = ds.getConnection ();
     stmt = ...
     rs = ...
}
finally {
     rs = close (rs);
     stmt = close (stmt);
     conn = close (conn);
}

public static Connection close (Connection conn) {
    if (conn != null) {
        try {
            conn.close ();
        }
        catch (SQLException e) {
            e.printStackTrace(); // Log, don't rethrow!!
        }
    }
    return null;
}

This code will make sure that all connections, etc, are always correctly closed and that any exception during close won't hide a previous error.

Answer 2

I think you should start with the Sun tutorial on connection pooling. Besides this, there are many implementations of connection pooling, some open source, including one from Apache. You should really start there rather than reinvent the wheel here.

Answer 3

Connection pools decorate Connection and Statement instances with their own wrapper implementations. When you call close on a connection you are actually just releasing it back to the pool. When you call close on a prepared statement you are actually just releasing it back to the connection's statement cache. When you prepare a statement you might just be fetching a cached statement instance from the connection. All this is hidden from view so that you don't have to worry about it.

When a connection is given to a client it is no longer available for any other client to use until the connection is released back to the pool. You generally just fetch connections when you need them and then return them as soon as you are finished with them. Because the connections are being held open in the pool there is little overhead in fetching and releasing connections.

You should use a connection from the pool just as you would a single JBDC connection and follow best-practices regarding the closing of resources so that you do not leak any connections or statements. See the try/catch/finally examples in some of the other answers.

Pools can manage the connection resources and test them before handing them out to clients to ensure that they aren't stale. Also a pool will create and destroy connections as needed.

Answer 4

You can only keep one Statement open on any given Connection. Creating more than one Connection using a connection pool isn't that difficult, although the way to go is to use one of the greater used ones out there.

Also, if you're going to go the way of standard JDBC, I recommend using PreparedStatement over Statement.

I've been using iBatis and out of the box is pretty nice. Brings a few other things to the table as well.

Answer 5

Take a gander at this (+:

Answer 6

Extra bits:

1. Application server tend to provide connection pooling, and it can get quite clever. If you are using an app server investigate carefully what you get out of the box before adding anything of your own.

2. Transactions: if you have

   Begin transaction

   get connnection work close connection // meaning return to pool

   get connection (with same isolation level etc.)
   // you will get SAME connection, the pool reserves it for your transaction

   work // happens in same transacction close connection

   Commit transaction // commits all the work

3. connections and errors

Pool implementations can be clever. If any one connection from the pool experiences certain errors which indicate that the DB server has bounced then the pool can choose to to discard all pool members.

Answer 7

If you've mastered JDBC with single-threading, going to multi-threading and connection pools shouldn't be a big deal. All you need to do differently is: 1. When you need a connection, get it from the pool instead of directly. 2. Each thread should get its own connections.

To clarify point 2: If you get a connection and then pass it to multiple threads, you could have two threads trying to execute queries against the same connection at the same time. Java will throw exceptions on this. You can only have one active Statement per Connection and one active query (i.e. ResultSet) per Statement. If two threads are both holding the same Connection object, they are likely to promptly violate this rule.

One other caveat: With Connection pooling, be very very careful to always close your connections when you're done. The pool manager has no definitive way to know when you're done with a connection, so if you fail to close one, it's going to sit out there dangling for a long time, possibly forever depending on the pool manager. I always always always follow every "getConnection" with a try block, and close the connection in the finally block. Then I KNOW that I've closed it before the function exits.

Besides that, everything should be the same as what you're used to.