Maintain DB connection

[Ken]

Right now for testing I am opening up a DB connection and closing it
for every query. This is because I was just doing quick testing.
What I am going to do is make the connection a singleton, open the
connection the first query (or perhaps even before) and then only
reopen the connection if the DB has closed it (an exception is thrown
by the SQL statement), I'll put a Boolean condition in to test if I've
tried to recover once and if it fails twice bail.

I also think this is such a simple thing there might be some well
known object to do this for me and handle further db issues that I
would end up hand rolling too?

Any advice?

 

[Arne Vajhøj]

Right now for testing I am opening up a DB connection and closing it
for every query. This is because I was just doing quick testing.
What I am going to do is make the connection a singleton, open the
connection the first query (or perhaps even before) and then only
reopen the connection if the DB has closed it (an exception is thrown
by the SQL statement), I'll put a Boolean condition in to test if I've
tried to recover once and if it fails twice bail.

I also think this is such a simple thing there might be some well
known object to do this for me and handle further db issues that I
would end up hand rolling too?

Any advice?

Drop the described approach, use a connection pool and use a
pattern of:
open connection
use connection
close connection

Arne

 

[Lew]

Singletons are often very bad.

Are the connection objects thread-safe? How do you know?

connection the first query (or perhaps even before) and then only
reopen the connection if the DB has closed it (an exception is thrown
by the SQL statement), I'll put a Boolean [sic] condition in to test if I've
tried to recover once and if it fails twice bail.

I also think this is such a simple thing there might be some well
known object to do this for me and handle further db issues that I
would end up hand rolling too?

Any advice?

Drop the described approach, use a connection pool and use a
pattern of:
open connection
use connection
close connection

+1

You will not outperform a pooling driver with a hand-wheel-reinventing-coded
version.

The Apache project offers DBCP (DataBase Connection Pool)
<http://commons.apache.org/dbcp/>
(which in turn requires <http://commons.apache.org/pool/>)
as one possible solution. Often there are RDBMS-specific solutions as well,
such as the javax.sql.ConnectionPoolDataSource JDBC driver for Postgres.
<http://jdbc.postgresql.org/documentation/84/datasource.html>
though they suggest that it's better to the application server's connection
pooling if available.

 

[Wojtek]

Ken wrote :

I also think this is such a simple thing there might be some well
known object to do this for me and handle further db issues that I
would end up hand rolling too?

Google proxool

 

[Roedy Green]

I also think this is such a simple thing there might be some well
known object to do this for me and handle further db issues that I
would end up hand rolling too?

see http://mindprod.com/jgloss/jdbcconnectionpooling.html
--
Roedy Green Canadian Mind Products
http://mindprod.com

Smart data structures and dumb code works a lot better than the other way around.
~ Eric S. Raymond The Cathedral and the Bazaar

 

[John B. Matthews]

I also think this is such a simple thing there might be some well
known object to do this for me and handle further db issues that I
would end up hand rolling too?

Google proxool[/QUOTE]

Also have a look at this lightweight standalone JDBC connection pool
manager. I use it with H2 Database, but it should work with any
ConnectionPoolDataSource:

<http://www.source-code.biz/snippets/java/8.htm>
<http://www.h2database.com/javadoc/org/h2/jdbcx/JdbcConnectionPool.html>