DBCP针对不同数据库的validationQuery

当你使用DBCP连接池时，你可以通过设置testOnBorrow和testOnReturn属性测试这个连接是否可用。不幸的是你还需要设置validationQuery才能起作用。那么问题来了如何设置validationQuery这个值呢？

什么是validationQuery？

validationQuery是用来验证数据库连接的查询语句，这个查询语句必须是至少返回一条数据的SELECT语句。每种数据库都有各自的验证语句，下表中收集了几种常见数据库的validationQuery。

DataBasevalidationQueryhsqldbselect 1 from INFORMATION_SCHEMA.SYSTEM_USERSOracleselect 1 from dualDB2select 1 from sysibm.sysdummy1MySqlselect 1Microsoft SqlServerselect1postgresqlselect version()ingresselect 1derbyvalues 1H2select 1

根据JDBC驱动获取validationQuery

如果你想支持多种数据库，你可以根据JDBC驱动来获取validationQuery，这里有个简单的类，根据JDBC驱动名称来获取validationQuery

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"));
 }
}

创建“db.validation.properties”文件，并放置在classpath目录下

#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