Is it better to wrap a connection tightly around a SQL operation or let a connection persist through the application, if the connection is not a physical connection but an abstraction that comes from a ConnectionPoolDataSource? (I'm using c3p0, for the record.)
Equivalently (I think): If I getConnection() once per application, will c3p0 make that look like a stable connection that doesn't die even if the underlying physical connection(s) die and come back to life?
And if I'm supposed to make the connection ephemeral, how do I program this with persistent prepared statements?
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.