pondělí 14. května 2012

DBCP - validationQuery for various Databases


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
Oracle select 1 from dual
DB2 select 1 from sysibm.sysdummy1
mysql 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);
            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"

org.hsqldb.jdbcDriver=select 1 from INFORMATION_SCHEMA.SYSTEM_USERS

oracle.jdbc.driver.OracleDriver=select 1 from dual

com.ibm.db2.jcc.DB2Driver=select 1 from sysibm.sysdummy1

com.mysql.jdbc.Driver=select 1
org.gjt.mm.mysql.Driver=select 1

#microsoft sql
com.microsoft.sqlserver.jdbc.SQLServerDriver=select 1

org.postgresql.Driver=select version();

com.ingres.jdbc.IngresDriver=select 1

org.apache.derby.jdbc.ClientDriver=values 1

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.

4 komentáře:

  1. No better ideas, anyway for H2 db I used this as validation query:

  2. Great, thanks! Just one question. I found here http://svn.apache.org/repos/asf/tomcat/tags/JDBC_POOL_1_0_6/build.properties.default
    ,that for H2 should work validation query "SELECT 1" as well. It could have slightly better performance. If you test, that "SELECT 1" works on H2, I will add it into this article.
    Please let me know - thanks :-)

  3. Why do all of this when it is already in JDBC : Connection.isValid() method. Read the Javadocs of that method and you'll see what I mean. It actually executes a validation query for you. Granted that this wasn't originally part of JDBC but I believe it came in JDBC3 (e.g. Java 6).

    1. True true. But DBCP pool required specify validation query.