When accessing a database over JDBC/ODBC should you create and close the connection for each request?

Question

If you have a class that services requests from other classes for database data when should you hold onto the databse connection and when should you close it and reopen it on the next request?

What if it's a service that responds to connections from external applications? (Web service, Ajax, rpc)

Is it a good idea to hold a singleton connection to the databse which is always open, and just reopen it on failure? Or should you open a new database connection for every request?

If maintaining a singleton database object that has an always open connection to the databse is a bad idea then are there any circumstances where it's a good idea? I've often seen it referenced as a justification for the Singleton pattern?

I'm not talking about a new connection per databse query, that would be silly.

Answer 1

You probably want to take a look at connection pooling.

In this scenario, N connections are opened and made available to clients. When you 'close' the connection, the connection itself is not closed, but returned to the pool for use by another client.

Apache DBCP is a useful library for managing this.