pondělí 14. května 2012

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 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=select 1
org.gjt.mm.mysql.Driver=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.

4 komentáře:

  1. No better ideas, anyway for H2 db I used this as validation query:
    select * from INFORMATION_SCHEMA.CATALOGS;

    OdpovědětVymazat
  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 :-)

    OdpovědětVymazat
  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).

    OdpovědětVymazat
    Odpovědi
    1. True true. But DBCP pool required specify validation query.

      Vymazat