Re: keeping Connection alive

Hi Jovan,

thanks for your reply!

> On Dec 12, 2009, at 6:43 PM, Andreas Brandl wrote:
> > I'm using a PostgreSQL as backend for a Java application, which may
> idle for quite a while (i.e. hours) and now and then issue a query on
> the backend.
>
> Is this a standard Java application or is it running in a Java EE or
> servlet container?

This is a standard Java application.

> If you're not using an application server, and/or don't need pooling,
> perhaps it would be better to use the regular jdbc api something like
> this:
> Class.forName("org.postgresql.Driver");
> Connection conn = DriverManager.getConnection(jdbcConnUrl, username,
> password);
> // then conn.createStatement , etc.
>
> Then you can call conn.close() and create a new connection when
> necessary.

I did work with a pure Connection before, until I realized things go wrong when application is idle for long time.

In either case, the problem is how to detect whether a Connection is valid or not. As far as I know there is no native
mechanismfor checking the state. So one approach may be to implement a DataSource which does check (i.e. "SELECT 1")
validitybefore returning the Connection (and reconnect if its not valid, i.e. throwing a SQLException?). 

Is there any better way to achieve this? After all this might mean a lot of overhead (checking every time
DataSource.getConnection()is called)... 

Thanks,
Andreas