permaculture playing cards*
The moose likes Java in General and the fly likes Problem Pooling prepared statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Problem Pooling prepared statements" Watch "Problem Pooling prepared statements" New topic
Author

Problem Pooling prepared statements

A Agrawal
Ranch Hand

Joined: Jul 13, 2001
Posts: 41
I am NOT using EJB and have my own Connection pooling.
Now, I am facing problem of Pooling prepared statements.
In my Application, various users (in their classes/beans) borrow connection from connection pool, do there database related operations and return the connection back to pool. So connection is not on hold with the user/class for the entire life of user's bean/class.
The problem is with Prepared statements, What I know is Prepared statements are attached with connection. But now when Connections are borrowed and returned dynamically, there is no point in using prepared statements even at the places where it could have been very effective.
so shall i stop using connection pool or shall i stop using prepared statements ? is there any way around to use both in some reasonable manner.

------------------
Amit Agrawal,
New Delhi, India.


Amit Agrawal,<BR>New Delhi, India.
Ganesh Ram
Ranch Hand

Joined: Jul 20, 2001
Posts: 33
Connection pooling is for pooling JDBC connection. Prepared statements is for saving on query parsing time.
I dont think both are related. You can continue to use your home grown connection pooling and also use prepered statements.
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
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).]


"I'm not back." - Bill Harding, Twister
A Agrawal
Ranch Hand

Joined: Jul 13, 2001
Posts: 41
Thanks Jim.
But Connection class is implemented in Driver. don't you think by extending vendor specific connection class we are restricting ourself ? whenever there is next version from the driver vendor, we will have to relook at our code.
second, most of the methods in those driver specific connection class are private and not protected. so will have to take care of them too. yes, we can get java code using decompilers but than thats not ethical.
Anyway, I was exploring RowSet provided with recently launched JDBC3.0..and it seems it has answer to most of my problems.
------------------
Amit Agrawal,
New Delhi, India.
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
You don't extend the driver's implementation - you just create a wrapper around it, which implements only the public methods required by the Connection interface:
<code><pre>
public class PSConnection implements Connection {

private Connection inner;
private PreparedStatement ps;

public PSConnection (Connection inner) {
this.inner= inner;
}

// implement methods of Connection...

public void clearWarnings() throws SQLException {
innerConnection.clearWarnings();
}

public void close() throws SQLException {
innerConnection.close();
}

// ...

public PreparedStatement getPS() {
return ps;
}

public void setPS(PreparedStatement ps) {
this.ps = ps;
}
}
</pre></code>
Then in your pool manager, when someone needs a connection, you handle it like this:
<code><pre>
public void PSConnection getPSConnection() {
if (pool.hasConnectionAvailable()) {
return pool.getConnection();
}
Connection inner = driver.getConnection();
PSConnection outer = new PSConnection (inner);
pool.add(outer);
pool.checkOut(outer);
return outer;
}
</pre></code>
(This is a crude implementation; there are a number of things you might want to do differently, but this should give the idea.)
Junaid Bhatra
Ranch Hand

Joined: Jun 27, 2000
Posts: 213
Here's some interesting news related to "pooling" PreparedStatements in the new JDBC 3.0 release:
http://www-106.ibm.com/developerworks/java/library/j-jdbcnew/index.html?dwzone=java
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem Pooling prepared statements