Re: keeping Connection alive

On 13/12/2009 10:52 AM, Craig Ringer wrote:

> int retries = MAX_RETRIES;
> do {
> try {
> try {
> Connection conn = myprovider.getConnection();
 > Statement stmt = conn.createStatement();
> // do work
> break;
> } finally {
> try {
> stmt.close();
> } catch (SQLException e) {
> // log to complain about statement close failure
> }
> }
> } catch (SQLException e) {
> myProvider.invalidateConnection(conn, e);
> retries--;
> }
> } while (retries > 0);

Oh, also: if that seems ghastly and rather undesirable to repeat all
over the place - good.

You should probably wrap your database work up in an implementation of
an interface that you can pass to a database work executor. Your
interface has a single method that does the actual work with the
connection, plus hooks called for success, failure, etc.

You pass a (probably anonymous inner class) instance of the interface to
a database worker that's responsible for the ugly error handling and
connection management, and let it take care of giving it a connection
and running it. The database worker can take care of the nasty bits,
leaving the rest of your code free to just handle real failures.

You don't have to bother to catch SQLExceptions thrown within your work
code, since it should all be running in a single transaction so it'll
get automatically rolled back when the exception is thrown. Instead, you
can let the exception bubble up for the database worker to handle. It'll
decide whether to retry, to give up and call your failure hook, etc.

As a bonus, if you specify that the on success and on failure methods
must be run on the EDT but require the database work code to be safe to
run on any thread, you can trivially move your database work off the EDT
and into a background worker thread.

--
Craig Ringer