[Solved] Should pooled JDBC connections using prepared statements be short-lived or long-lived?

Question

Well, then this is back to the original question – how do I share a PreparedStatement between connections if there are many connections? I thought connections create hence own PreparedStatements.

If that is your sole question -honestly, that was not clear from your initial question-, then you don’t need to worry about this at all. The JDBC driver and the DB will cache them when applicable and necessary. That’s not your responsibility. Just acquire and close the DB resources in the shortest possible scope according the normal JDBC idiom.

Or if it’s a Java EE web application, look at JPA to reduce JDBC boilerplate to oneliners and look at EJB to competely delegate the transaction handling to the container so that you don’t need to fiddle with (auto)commits and rollbacks.

Accepted Answer

Well, then this is back to the original question – how do I share a PreparedStatement between connections if there are many connections? I thought connections create hence own PreparedStatements.

If that is your sole question -honestly, that was not clear from your initial question-, then you don’t need to worry about this at all. The JDBC driver and the DB will cache them when applicable and necessary. That’s not your responsibility. Just acquire and close the DB resources in the shortest possible scope according the normal JDBC idiom.

Or if it’s a Java EE web application, look at JPA to reduce JDBC boilerplate to oneliners and look at EJB to competely delegate the transaction handling to the container so that you don’t need to fiddle with (auto)commits and rollbacks.