validationQuery驗證資料庫連接

標籤：目錄   擷取   ade   main   gre   ida   word   hsqldb   data   

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

建立“db.validation.properties”檔案，並放置在classpath目錄下

#hsqldborg.hsqldb.jdbcDriver=select 1 from INFORMATION_SCHEMA.SYSTEM_USERS#Oracleoracle.jdbc.driver.OracleDriver=select 1 from dual#DB2com.ibm.db2.jcc.DB2Driver=select 1 from sysibm.sysdummy1#mysqlcom.mysql.jdbc.Driver=select 1org.gjt.mm.mysql.Driver=select 1#microsoft sqlcom.microsoft.sqlserver.jdbc.SQLServerDriver=select 1#postgresqlorg.postgresql.Driver=select version();#ingrescom.ingres.jdbc.IngresDriver=select 1#derbyorg.apache.derby.jdbc.ClientDriver=values 1#H2org.h2.Driver=select 1

validationQuery驗證資料庫連接