Re: keeping Connection alive

Hi Craig,

thanks a lot for your detailed answer! I learned a lot and hopefully are going to do a better design next time. :)

> Is its connection to the server diect via a local Ethernet segment? Or
> is it going through routers - possibly routers doing NAT, such as most
> home user modem/routers?

Neither local nor NAT/home routers.

> > So one approach may be to implement a DataSource which does check
> (i.e. "SELECT 1") validity before returning the Connection (and
> reconnect if its not valid, i.e. throwing a SQLException?).
>
> Nononono....
>
> Have the DataSource _consumer_ deal with it.
>
>
> int retries = MAX_RETRIES;
> do {
> try {
> try {
> Connection conn = myprovider.getConnection();
> // 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);
>
>
> ... where "invalidateConnection(Connection, Throwable)" tells the
> provider/pool that the connection is broken.

I'm not sure how to do this, because AFAIK a DataSource implementation does not have a method for invalidating the Connection. How to do this when implementing against standard interfaces?

Thanks again for your detailed answer!

Best regards,
Andreas