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 :
Source
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(T4CMAREngine.java:1157) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)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 dualThis may impact your overall performance also , because everytime a connection is made , one more query is fired . So use this wisely .
Source