DBCP - validationQuery for various Databases

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.