But Connections and PreparedStatements (henceforth "PS's") are linked - if you check out a particular Connection, then create a PS using that connection, and then check in the Connection, it gets tricky. You shouldn't use the PS while the Connection is checked in; another
thread might check out the same Connection used by the PS, which could get messy. Or the connection manager might decide the Connection needs to be closed for some reason. So, don't use a PS after you've checked in the Connection which you got it from.
But- what if you later check out the same Connection. You don't really want to generate a
new PS for it; that defeats the whole purpose of using a PS, after all. You'd like to be able to use the one that was already generated - so the trick is to find a way to associate each Connection with a PS. One simple way to do this is to maintain a HashMap whose keys are Connections and whose values are PS's. When you get a new Connection, look it up in the Map to see if it has a PS already - if not, make one, and put it in the Map. Eventually most of the Connections will acquire a PS.
There are a lot of ways to approach this problem. Do you need the Connection itself for anything other than getting a PS? If not, you may want to just make a PS pool rather than a Connection pool. Is there just one type of PreparedStatement you need prepeatedly, or are there multiple types (e.g. select, insert, update, delete)? You could have different pools for the different types of PSs, or you could have one connection pool with multiple Maps to the different types of PSs. You may also want to create a new class which encapsulates a Connection along with whatever PS's you have, and pool this new class. We did this in one project I was on - there was a lot of existing code which assumed the pooled object was a Connection, and we didn't want to break that. We just wanted to add PS's to the pooled object. So we made the new class extend Connection, and it acted as a simple wrapper around the "real" Connection - so it could be handed off to existing code without troubles. But new code could make use of the fact that it had additional methods like getInsertPS() and getUpdatePS(), to access the various PS's which were also encapsulated.
Hope this helps...
[This message has been edited by Jim Yingst (edited July 21, 2001).]