什么是validationQuery？

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

DataBase validationQuery hsqldb select 1 from INFORMATION_SCHEMA.SYSTEM_USERS Oracle select 1 from dual DB2 select 1 from sysibm.sysdummy1 MySql select 1 Microsoft SqlServer select1 postgresql select version() ingres select 1 derby values 1 H2 select 1 如果你想支持多种数据库，你可以根据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")); } } 123456789101112131415161718192021222324252627282930 123456789101112131415161718192021222324252627282930

创建“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