Is there a safe way to tell if a JDBC connection is still ok?

Question

We have a web server handling requests from clients. One component of this web server holds a connection to a database.

I need to be able to recognise if the connection has been closed or is in some way no longer functional before I begin to use it. Currently I do something like:

// Decide connection details on alias.
private String alias = null;
// I must have my own because I prepare statements.
private Connection connection = null;

public Connection getConnection() {
  try {
    if ( connection.isClosed() ) {
      // Start afresh.
      connection = null;
    }
    // ** More tests here to check connection is ok.
    if (connection == null) {
      // Make a new connection.
      connection = Connections.getConnection(alias);
    }
  } catch (SQLException ex) {
    // Cause a NPE further down the line.
    connection = null;
  }
  return connection;
}

Sadly this sometimes returns such a stale connection that I get one of various errors. One such looks like:

java.sql.SQLException: Io exception: Software caused connection abort: socket write error

Note that this is just one of the errors recorded and this one happens after about 72 hours idle.

What I am looking for is a minimal database-generic tester of a connection that should consistently tell if the connection is up, running and stable. Is this possible?

I don't mind running a very small query against it but it must be both database agnostic and take little to no time/resources.

BTW: I am running under Java 5 so Connection.isValid is not a solution for me.

---

Added

For those of you visiting this question later - I eventually took the advice offered and moved to a real connection pool and not only was is astonishingly easy to do but all of my issues disappeared.

The only strange part was the realisation that with a connection pool you must close your connection when you are finished with it - the pool intercepts the close and returns it to the pool behind the scenes.

Answer 1

The best way is to do a simple SQL statement like SELECT 1 or SELECT 1 FROM DUAL for Oracle. (Please see your database for vendor-specific syntax.)

If it fails, refresh the connection. That's what Java EE app servers like WebLogic do to test them if you configure them to do so.