JDBC fundamental concepts, Pooling and Threading

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.

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.

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.