Just wanted to point that PreparedStatements Caches are not that rare. I remember three years ago IBM engineers put one into WebSphere's Component Broker. CMP entities benefitted from that implementation greatly. (Yeah, that was the time when Weblogic was still immature and its EJB specification compliance left much to be desired.) IBMers earned my respect for many things done well. Another one I remember, is their implementation of JSP compiler. Instead of putting numerous "out.print("your static HTML here");" they have been using some file object that allowed to access static HTML by indexing into array of its bytes...

Denis.

Hi Denis,

I've just wet my toes in J2EE, so i offer my opinion with a little hesitation. The first question that came to me while reading the article about "Prepared Statements", was that access to the Prepared Statement objects (present in the cache) would have to be synchronised i.e. it should be thread safe. Would this not affect the performance of the other requests which would be forced to wait? Or would there be a algorithm which would possibly create new Prepared Statements, if there are too many waiting requests.

I would appreciate your opinion on this.

regards,
Pawan

Sure, sure... for me it looks like:

pstmt = ConnectionPool.getInstance().getSelectPrepared(selectQuery); synchronized (pstmt) {
pstmt.setObject(1, getValues().get(getPrimaryKeyName()));
if (null != getForeignKeyName()) {
pstmt.setObject(2, getValues().get(getForeignKeyName()));
}
res = pstmt.executeQuery();
}
isLoaded = true;
if (res.next()) {
loadObject(res);
etc, etc... (is formatting screwed? bummer...)

I have not had to create several instances of a PreparedStatement given same query string, since my SQLs are short, it takes too little to exit the synchronized block. But of course this could become a bottleneck at some point.

Denis.

Sure, sure... for me it looks like:

pstmt = ConnectionPool.getInstance().getSelectPrepared(selectQuery); synchronized (pstmt) {
pstmt.setObject(1, getValues().get(getPrimaryKeyName()));
if (null != getForeignKeyName()) {
pstmt.setObject(2, getValues().get(getForeignKeyName()));
}
res = pstmt.executeQuery();
}
isLoaded = true;
if (res.next()) {
loadObject(res);
etc, etc... (is formatting screwed? bummer...)

I have not had to create several instances of a PreparedStatement given same query string, since my SQLs are short, it takes too little to exit the synchronized block. But of course this could become a bottleneck at some point.

Denis.

I just want to point out that you do not need to systematically synchronize code. It depends on your application server and on your code design.
For instance, Websphere 5 keeps in cache one PreparedStatement per connexion.
Therefore, if you associate one thread to one connexion (from my own experience, it is a good practice) you are automatically
in a threadsafe configuration. On the other hand, If you allow a connection to be accessed by several threads then you must synchronize your code.

Vincent.