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.
