Monday 21 October 2013

Textual description of firstImageUrl

Validation Query in DBCP : When And Why

Configuring Database Connection Pool is very simple by using Commons DBCP and explained in my previous post . There are some other important properties available which we need to keep in mind while creating connection pool. Like Init sqls and validation queries . Validation queries are used to validate connections in the pool .

Database Connection pool maintains alive connections (already opened and being used ) , idle connections(opened but not in use ) and closed connection. When a database is restarted , all connections in the pool becomes broken connections . So it is very important to check for the validity of connection while borrowing it from pool .

To Check for whether the connection is valid or not , we have validation query , which get fired if we specify the validation query along with other flags . There are three flags available for this :

testOnBorrow : Default value is true for this . If this is enabled , then whenever we get a connection from pool , validation query is fired . If the connection is broken , that connection will be dropped and new connection will be borrowed .

testOnReturn: Default value is false . Validation query is fired while returning the connection to the pool .

testWhileIdle : Default value is false.Validation query is fired while getting the idle connection from the pool .

All these flags first check whether validation query is set or not . If there is no validation query , then these flags will have no impact .

If you do not use these validation query , and if the database is restarted while your application is running , you might get error like this :

java.sql.SQLException: No more data to read from socket
 at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(
 at oracle.jdbc.driver.T4CTTIfun.receive(
 at oracle.jdbc.driver.T4CTTIfun.doRPC(

There are different validation queries defined for various databases . You can check the full list here . For oracle , the validation query is :
select 1 from dual
This may impact your overall performance also , because everytime a connection is made , one more query is fired . So use this wisely .