• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem Pooling prepared statements

 
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Wanderer
Posts: 18671
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).]
 
A Agrawal
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 18671
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.)
 
Ranch Hand
Posts: 213
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
reply
    Bookmark Topic Watch Topic
  • New Topic