Auto Reconnect of Database Connection

Question

I have a DBCP connection pool in Tomcat. The problem is that when the connection is lost briefly the appliction is broken because DBCP won't try to reconnect again later when there is a connection. Can I get DBCP to reconnect automatically?

Answer 1

Would autoReconnect=true do the job when added to the connection string?

jdbc:mysql://localhost:3306/appfuse?autoReconnect=true -> jdbc:db2:appfuse

Answer 2

There are 2 ways to "solve" this, though both have some issues:

1. You can use a "validationQuery" (see below) to have a test query run before you go (generally something like 'select 1 from dual' which will be used to test connections before/after you get/give them to the pool. This adds an extra call per connection request from the pool. See: http://wiki.apache.org/commons/DBCP

2. Instead of doing this per query, you can have the idleEvictorThread do it by setting testWhileIdle, though in some versions that thread can cause deadlocking under high-load. See: http://commons.apache.org/dbcp/configuration.html for more details on that and other options

Answer 3

Don't think DBCP does that, but BoneCP (http://jolbox.com) can be configured to automatically replay any transactions when the DB or network goes down. It's completely transparent to your application.