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);
}
}
public static void main (String[] args) {
System.out.println(new ValidationQuery().getValidationQuery("org.hsqldb.jdbcDriver" ));
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
创建“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