What to keep alive and what to recreate in a simple embedded database?

Question

In a desktop application with an embedded Derby database, what should I keep alive (as opposed to recreating each time when talking with the database) for the whole lifetime of the application?

1. Connection and Statement, using the same statement throughout the lifetime of the program?
2. Connection, recreating statement repeatedly?
3. Neither of these. That is, recreating connection and statement repeatedly?

From a database amateur's viewpoint it would seem reasonable to avoid recreating anything that doesn't need to be recreated, but is option 1 (or 2) against standard practices or are there some obvious cons? Is (re)creating connections and statements expensive or not?

Answer 1

Connecting is indeed expensive (may cost a few hundred milliseconds). The connection has however a limited lifetime and the statement and resultset depends on its lifetime. The average DB will timeout and drop the connection whenever it's been released for more than 30 minutes. You can add some timeout checker in your code so that it will re-acquire the connection "automatically", but that's a tedious work and very prone to bugs if you don't know how it ought to work under the hoods. Rather use an existing, thoroughly developed and robust connection pool like C3P0 and write the JDBC code the usual way (acquire and close all the resources in the shortest possible scope). That should be it.

Although in theory (and apparently also in practice) in embedded databases connecting will be less expensive and a connection can survive forever, I would strongly disagree to approach embedded databases differently in JDBC code. It would make your JDBC code semantically flawed and completely unportable. You have to rewrite/reimplement everything whenever you'd like to distribute it and/or change to a real RDBMS server with more powers.

Answer 2

In an embedded Derby application, both Connection and Statement objects are quite cheap and I think you should not worry about creating them as you need them. In the Derby unit test suites, we create tens of thousands of connections and hundreds of thousands of statements, without problems.

It is also fine to keep your Connection and Statement objects around as long as you wish. Embedded Derby has no time limit, and will not drop the connection or statement objects unless you tell it to (by closing them), or unless you leak them away, in which case the Garbage Collector will clean them up (eventually).

Although it is fine to keep the connection around, you should commit() the transaction when it is complete (unless you run in autocommit mode of course).

And, if you are keeping a result set around, be aware that committing the transaction will usually also close the result set, less you specifically construct the special result sets that are held open across commit.