有没有一种安全的方法来判断JDBC连接是否还可以? [英] Is there a safe way to tell if a JDBC connection is still ok?

问题描述

我们有一个Web服务器来处理来自客户端的请求.该Web服务器的一个组件拥有与数据库的连接.

在开始使用连接之前，我需要能够识别连接是否已关闭或以某种方式不再起作用.目前，我正在执行以下操作:

// Decide connection details on alias.
private String alias = null;
// I must have my own because I prepare statements.
private Connection connection = null;

public Connection getConnection() {
  try {
    if ( connection.isClosed() ) {
      // Start afresh.
      connection = null;
    }
    // ** More tests here to check connection is ok.
    if (connection == null) {
      // Make a new connection.
      connection = Connections.getConnection(alias);
    }
  } catch (SQLException ex) {
    // Cause a NPE further down the line.
    connection = null;
  }
  return connection;
}

可悲的是，有时这会返回这样一个过时的连接，导致出现各种错误之一.这样的样子如下:

java.sql.SQLException:Io异常:软件导致连接中止:套接字写入错误

请注意，这只是记录的错误之一，此错误在闲置约72小时后发生.

我正在寻找的是连接的最小数据库通用测试程序，该测试程序应始终确定连接是否正常运行，运行和稳定.这可能吗?

我不介意对此进行很小的查询，但是它必须与数据库无关，并且几乎不需要时间或资源.

顺便说一句:我正在Java 5下运行，所以Connection.isValid对我来说不是一个解决方案.

---

已添加

对于那些稍后再问这个问题的人-我最终接受了所提供的建议，并将其转移到一个真正的连接池中，不仅如此之容易，而且我所有的问题都消失了. >

唯一奇怪的部分是，意识到连接池必须在连接完成后close进行连接-池拦截关闭并将其返回到后台的池中.

解决方案

最好的方法是为Oracle执行简单的SQL语句，例如SELECT 1或SELECT 1 FROM DUAL. (请参阅您的数据库以获取特定于供应商的语法.)

如果失败，请刷新连接.如果将Web Java Java EE应用服务器配置为进行测试，那么这就是测试它们的方式.

We have a web server handling requests from clients. One component of this web server holds a connection to a database.

I need to be able to recognise if the connection has been closed or is in some way no longer functional before I begin to use it. Currently I do something like:

// Decide connection details on alias.
private String alias = null;
// I must have my own because I prepare statements.
private Connection connection = null;

public Connection getConnection() {
  try {
    if ( connection.isClosed() ) {
      // Start afresh.
      connection = null;
    }
    // ** More tests here to check connection is ok.
    if (connection == null) {
      // Make a new connection.
      connection = Connections.getConnection(alias);
    }
  } catch (SQLException ex) {
    // Cause a NPE further down the line.
    connection = null;
  }
  return connection;
}

Sadly this sometimes returns such a stale connection that I get one of various errors. One such looks like:

java.sql.SQLException: Io exception: Software caused connection abort: socket write error

Note that this is just one of the errors recorded and this one happens after about 72 hours idle.

What I am looking for is a minimal database-generic tester of a connection that should consistently tell if the connection is up, running and stable. Is this possible?

I don't mind running a very small query against it but it must be both database agnostic and take little to no time/resources.

BTW: I am running under Java 5 so Connection.isValid is not a solution for me.

---

Added

For those of you visiting this question later - I eventually took the advice offered and moved to a real connection pool and not only was is astonishingly easy to do but all of my issues disappeared.

The only strange part was the realisation that with a connection pool you must close your connection when you are finished with it - the pool intercepts the close and returns it to the pool behind the scenes.

解决方案

The best way is to do a simple SQL statement like SELECT 1 or SELECT 1 FROM DUAL for Oracle. (Please see your database for vendor-specific syntax.)

If it fails, refresh the connection. That's what Java EE app servers like WebLogic do to test them if you configure them to do so.

这篇关于有没有一种安全的方法来判断JDBC连接是否还可以?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！