java 通过JDBC“ping”数据库的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/847246/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the best way to 'ping' a database via JDBC?
提问by Leigh
I'm trying to determine the best way to ping a database via JDBC. By 'best' I mean fast and low overhead. For example, I've considered executing this:
我正在尝试确定通过 JDBC ping 数据库的最佳方法。“最好”是指快速和低开销。例如，我已经考虑过执行这个：
"SELECT 1 FROM DUAL"
but I believe the DUAL table is Oracle-specific, and I need something more generic.
但我相信 DUAL 表是特定于 Oracle 的，我需要更通用的东西。
Note that Connectionhas an isClosed()method, but the javadoc states that this cannot be used to test the validity of the connection.
请注意，Connection有一个isClosed()方法，但 javadoc 声明这不能用于测试连接的有效性。
采纳答案by Thilo
Yes, that would be Oracle-only, but there is no generic way to do this in JDBC.
是的，这将仅适用于 Oracle，但在 JDBC 中没有通用的方法来执行此操作。
Most connection pool implementations have a configuration parameter where you can specify the SQL that will be used for ping, thus pushing the responsiblity to figure out how to do it to the user.
大多数连接池实现都有一个配置参数，您可以在其中指定将用于 ping 的 SQL，从而将确定如何执行此操作的责任推给用户。
That seems like the best approach unless someone comes up with a little helper tool for this (of course, it precludes using potentially even faster non-SQL-based methods like Oracle's internal ping function)
这似乎是最好的方法，除非有人为此想出了一个小帮助工具（当然，它排除了使用可能更快的非基于 SQL 的方法，如Oracle 的内部 ping 函数）
回答by eckes
With JDBC 4 you can use isValid(int)(JavaDoc) from the Connection Interface. This basically does the trial statement for you.
使用 JDBC 4，您可以从连接接口使用isValid(int)( JavaDoc)。这基本上为您做试验陈述。
Some driver implement this by sending the correct dummy SQL to the database and some directly uses low level operations which reduces the parsing overhead.
有些驱动程序通过向数据库发送正确的虚拟 SQL 来实现这一点，有些驱动程序直接使用低级操作来减少解析开销。
However beware of the timeout, some drivers (DB/400 and Oracle Thin) do spawn a new time thread for each invocation, which is not really acceptable for most Pool validation scenarios). And Oracle also does not seem to use a prepared statement, so it's kind of relying on the implicit cache.
但是请注意超时，某些驱动程序（DB/400 和 Oracle Thin）确实会为每次调用生成一个新的时间线程，这对于大多数池验证场景来说是不可接受的）。而且 Oracle 似乎也没有使用准备好的语句，因此它有点依赖于隐式缓存。
回答by rudolfson
I'm not aware of a generic solution, either. For IBM's UDB on iSeries (and perhaps other DB2 systems) it would be
我也不知道通用的解决方案。对于 iSeries（可能还有其他 DB2 系统）上的 IBM UDB，它将是
select 1 from SYSIBM.SYSDUMMY1;
回答by Brad Cupit
MySQL has a nice mechanism, documented in this SO answer. From the answer:
MySQL 有一个很好的机制，记录在这个SO answer 中。从答案：
"/* ping */ SELECT 1"This will actually cause the driver send a ping to the server and return a fake, light-weight, result set.
"/* ping */ SELECT 1"这实际上会导致驱动程序向服务器发送一个 ping 并返回一个假的、轻量级的结果集。
Having said that, @eckes answeris the best (using JDBC 4's Connection.isValid(int)).
话虽如此，@eckes 答案是最好的（使用 JDBC 4's Connection.isValid(int)）。
回答by Joern
You could try to get the db name from the connection meta data and execute a matching sql staement. E.g.
您可以尝试从连接元数据中获取数据库名称并执行匹配的 sql 语句。例如
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = dataSource.getConnection();
String dbProductName = con.getMetaData().getDatabaseProductName();
Statement st = con.createStatement();
if ( "PostgreSQL".equalsIgnoreCase(dbProductName) ) {
rs = st.executeQuery("select version();");
} else if ( "Oracle".equalsIgnoreCase(dbProductName) ) {
rs = st.executeQuery("select 1 from dual");
} else {
...
}
} catch ( Exception ex ) {
System.out.prinln("DB not reachable");
} finally {
// close statement, connection etc.
...
}
回答by Adam Paynter
I may be out to lunch on this one, but could you simply execute some non-sense query, such as:
我可能会出去吃午饭，但你能不能简单地执行一些无意义的查询，例如：
SELECT * FROM donkey_giraffe_87
I don't know very much about JDBC's error handling, but perhaps you could check to see if the database is at least telling you that the table does not exist. If JDBC's error codes are vendor-specific, the Spring Framework has some utilities for mapping these codes to more meaningful exceptions.
我不太了解 JDBC 的错误处理，但也许您可以检查一下数据库是否至少告诉您该表不存在。如果 JDBC 的错误代码是特定于供应商的，则 Spring Framework 有一些实用程序可以将这些代码映射到更有意义的异常。
回答by Martin OConnor
Simply issuing the following query should be sufficient
只需发出以下查询就足够了
SELECT 1
回答by Mark
Can you not simply execute
你不能简单地执行
SELECT 1
without a FROM clause against most databases?
没有针对大多数数据库的 FROM 子句？
