2014-03-13

Make java connection pool auto reconnect

Recently we encountered a problem when using java db connection pool. The problem is that the connection is being closed by the DB server since it is already timeout. To due with this, need to configure two more parameters.

They are:
testOnBorrow  ;  define it as "true"
validationQuery ; define it as "SELECT 1 FROM dual" if you are also using MySQL

Yes, It is easy to config it. But how to test it? (Many sites may not give the answer). 

To verify the configuration is work, we can make a simple Unit Test;

Step 1: Make a failure case

Make an unit test base on the following code;


Properties p = new Properties(); 
prop.put("driver", "org.gjt.mm.mysql.Driver");
prop.put("url", "jdbc://localhost:3306/db");
prop.put("username", "user");
prop.put("password", "123456");
p.setProperties("testOnBorrow", "false");
p.setProperties("validationQuery", "");

BasicDataSource ds = BasicDataSourceFactory.createDataSource(prop);
ds.setMaxActive(1);
ds.setMaxIdle(1);
ds.setMinIdle(0);

Connection conn = ds.getConnection();
System.out.println("First connection");


System.out.println("Sleep start");
Thread.sleep(10000);
System.out.println("Sleep end");

Connection conn = ds.getConnection();
System.out.println("Second connection");


To make the connection fail at the second time, try to kill the connection in DB side while "Thread.sleep";


Step 2: Fix the failure 

Simply change "testOnBorrow=false" and "validationQuery=" to 
 "testOnBorrow=true" and "validationQuery=SELECT 1 FROM dual"

Then, run the test again. we will see "Second connection" show up instead of Exception message.