-5

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?

djechlin
  • 59,258
  • 35
  • 162
  • 290
  • When you say "once per application", is this a single-threaded application? – skaffman May 11 '12 at 18:28
  • Multithreaded. As it stands, there's one raw connection for all threads and that works fine - I'm introducing a CPDS to handle the problem of restoring connectivity when it drops. – djechlin May 11 '12 at 19:11
  • 1
    For desktop or web? If web and Java EE, just use EJB/JPA. Then you never need to worry about connections, pooling, transactions, etc. The container will do it all for you. By the way, a single connection for all threads is recipe for disaster if used by multiple users. – BalusC May 11 '12 at 19:23
  • 1
    @djechlin: Sharing connections between threads is a *really* bad idea. If it's working for you, then that's by lucky coincidence. JDBC is *not* thread-safe. – skaffman May 11 '12 at 19:49
  • @skaffman mutex around the actual queries so should be fine. – djechlin May 11 '12 at 20:41
  • JDBC is *supposed* to be thread-safe (see http://docs.oracle.com/javase/1.3/docs/guide/jdbc/spec/jdbc-spec.frame9.html), but it doesn't make much sense to share a connection between threads: each thread should start a transaction, execute multiple SQL operations in the transaction, and commit the transaction. This is impossible to do with a single connection, unless all threads are blocked waiting for a transaction to complete. – JB Nizet May 11 '12 at 21:19
  • 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. – djechlin May 11 '12 at 21:26

1 Answers1

1

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.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555