Info
When you are
using DBCP pool, you can use property
testOnBorrow and
testOnReturn to test if connection is still valid. Unfortunately you have to set property
validationQuery to make it work.
Problem is: What value should be in validationQuery?
What is validationQuery
validationQuery is the SQL query that will be used to validate connections. This query
MUST be an SQL SELECT statement that returns at least
one row.
Unfortunately for every database works another validationQuery. After some googling I have collected this:
| Database |
validationQuery |
notes |
| hsqldb |
select 1 from INFORMATION_SCHEMA.SYSTEM_USERS |
| Oracle |
select 1 from dual |
| DB2 |
select 1 from sysibm.sysdummy1 |
| mysql |
/* ping */ select 1 |
not tested |
| microsoft sql |
select 1 |
not tested |
| postgresql |
select version(); |
not tested |
| ingres |
select 1 |
not tested |
| derby |
values 1 |
not tested |
| H2 |
select 1 |
not tested |
Idea: Get validationQuery according to JDBC driver
If you support multiple databases, you can select validationQuery according to JDBC driver.
Here is simple class, which return validationQuery according to JDBC driver class name.
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
public class ValidationQuery {
public String getValidationQuery(String driver) {
Properties properties = loadProperties();
return properties.getProperty(driver, "");
}
private Properties loadProperties() {
String propertyFilename = "db.validation.properties";
try {
Properties props = new Properties();
InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream(propertyFilename);
props.load(resourceAsStream);
resourceAsStream.close();
return props;
} catch (IOException e) {
throw new RuntimeException("Cannot load properties file '" + propertyFilename + "'.", e);
}
}
//Example: Get validationQuery for hsqldb
public static void main(String[] args) {
System.out.println(new ValidationQuery().getValidationQuery("org.hsqldb.jdbcDriver"));
}
}
And here is properties file, that you have to put on classpath and name it as "db.validation.properties"
#hsqldb
org.hsqldb.jdbcDriver=select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
#Oracle
oracle.jdbc.driver.OracleDriver=select 1 from dual
#DB2
com.ibm.db2.jcc.DB2Driver=select 1 from sysibm.sysdummy1
#mysql
com.mysql.jdbc.Driver=/* ping */ select 1
org.gjt.mm.mysql.Driver=/* ping */ select 1
#microsoft sql
com.microsoft.sqlserver.jdbc.SQLServerDriver=select 1
#postgresql
org.postgresql.Driver=select version();
#ingres
com.ingres.jdbc.IngresDriver=select 1
#derby
org.apache.derby.jdbc.ClientDriver=values 1
#H2
org.h2.Driver=select 1
If you have better idea how to set validationQuery or you have an tip for update of "db.validation.properties" file. Do not hesitate and let me know.
More validation queries can be found here StackOverflow - dbcp-validationquery-for-different-databases.
