I have a question that's been nagging at me for a while. I'm currently using connection pooling in a servlet/jsp based web application. The pooling is implemented by the
application server, and I get a get a connection via a specific datasource.
When the application needs a particular set of data, a connection is obtained from the pool, statements created and executed, result set processed then all statements are closed and the connection returned to the pool. All this works fine.
However, how does this approach match with the use of PreparedStatements? With this approach I need to create the ps everytime I obtain a connection from the pool, as opposed to reusing the ps and obtaining their benifits. I might as well be using a regular statement.
Anyone seen this addressed anywhere, or am I missing something obvious (wouldn't be the first time)?
Interesting question. The answer is that PreparedStatement is good for security purposes and that you still can use a cache of PreparedStatements, but you should have a cache of opened connections first (and don't forget that different databases deal differently with transactions on opened connections - some of them may be will not like transactions opened for long time) and create your own implementation of pooled datasource to do that. However, some of the appservers/containers (WLS for example) can do this management inside itself, so you don't have to care about that. That depends on particular container's DataSource implementation.
I think that prime task of PreparedStatement is to provide security (in case of type-checking of arguments before they're sent to the database). Also, you can easily reuse it in the vicinity of one connection (I mean that you can have one opened connection and use the same PreparedStatement multiple times with it - but better during only one user request, if otherwise - refer to the passage above).
If you look at JBoss as en example, the datasource pooling also has a setting to cache prepared statements "behind the scenes" so even if you think you're creating a new one each time, it's just re-using an already open one. This is probably available in other servers as well, but this is the only example I know of.
Thanks for the replys.
I wasn't thinking about the security aspect of the prepared
statements (obviously this is still there even if you are
creating new ones every time) but rather the performance
advantage of the database being able to "compile" the statement
once and reuse the execution path.
My apps don't really have a performance bottleneck which is why this has only been nagging me - no real need to fix it by, as you mention, implementing my own connection pooling.
I will look into the app server documentation more to see if it mentions ps caching as JBoss does.
Caching prepared statements definitely improves performance. For example in Weblogic ps cache may give you up to 40-60% of speed increase. Though, implementing ps cache is much more complex task compared to let say connection pooling. So I'd check with the application server vendor. It's quite possible it's implemented already.