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.

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[Aaron Digulla]

"should" != "does" != "does well". You can set a timeout in DBCP which says "If the connection wasn't returned after an hour, there's a bug in the client, so let's reuse it". As for "stale", you need to supply custom SQL to check whether a connection is dead. It would be nice if JDBC would define that but it doesn't and most DB vendors don't even bother to support a lot of the JDBC spec. For example, in Oracle, TIMESTAMP is not a java.sql.Timestamp.

[David Hofmann]

Em, sorry, I didn't meant to re-invent something. The tutorial suggest a connection wrapper JDCConnectionManager. But the drivers I use have already their pooled datasource implementation.

[David Hofmann]

wow !, thanks teabot one more thing, once you have a connection. Why the api split the operations to be made in the database in a Statement object instead of just using the connection object ? I undertand this for prepared statements. But for normal statements, is it just to let the developer have lets say N operations that migth get executed or not, or may be executed many times, it is just for this ?

[Yishai]

What drivers are you using? Also, did you look at Apache's implementation? If you need more than the basic JDBC, that may solve your problem for you.

[David Hofmann]

ojdbc14.jar and jtds-1.2.2.jar. Why would I try apache's pool implementation if the driver already provides it ?

[Jay]

Just guessing, but: As you apparently realize, PreparedStatements have to be separate from Connections because there could be more than one PreparedStatement per Connection. So I suppose the Java people made Statements separate to keep them parallel with PreparedStatements. Otherwise you'd have a whole bunch of "statement functions" in Connection, and then another whole bunch in PreparedStatement. That would mess up the inheritance tree of Statement and PreparedStatement. As I say, just speculating. If anyone has an authoritative source on this, I'd be amused to hear it.

[Yishai]

Well, right there you have two different databases you are connecting to, which the Apache implementations (or potentially others) will give you the ability to have one pool that interacts with either. But I'm no longer clear on your question. At first you seemed to be writing your own pool implementation (like the Sun Tutorial). Now you seem to want to use an existing one. I don't know what Oracle has, but jTDS just gives you a connection appropriate for a pool, it doesn't give you the actual pool implementation.

[yuxh]

I don't sure whether it's the version problem, "Java will throw exceptions on this" is not true in recent JDBC.

[Jay]

@yuxh Sorry, I guess my statement was ambiguous. I didn't mean, If you try to do this, Java will immediately and automatically throw an exception. I meant, You are liable to create a situation that will cause an exception. You MIGHT get a way with it in any given run of any given application.