用于MS SQL Server的JBoss AS 5数据库连接池重新连接例程 [英] JBoss AS 5 database connection pool re-connect routine for MS SQL Server

问题描述

当JBoss AS 5到DB的连接暂时丢失时，我想提出重新连接到MS SQL Server的最佳方法。

I'd like to come up with the best approach for re-connecting to MS SQL Server when connection from JBoss AS 5 to DB is lost temporarily.

For甲骨文，我发现了这个问题：当连接变坏时，有没有办法让JBoss连接池重新连接到Oracle？表示它使用Oracle特定的ping例程并利用 valid-connection-checker -class-名称的属性中所描述 JBoss的配置数据源维基

For Oracle, I found this SO question: "Is there any way to have the JBoss connection pool reconnect to Oracle when connections go bad?" which says it uses an Oracle specific ping routine and utilizes the valid-connection-checker-class-name property described in JBoss' Configuring Datasources Wiki.

我想避免的是每次从池中提取连接时都运行另一个SQL，这是另一个属性 check-valid-connection-sql 基本上可以。

What I'd like to avoid is to have another SQL run every time a connection is pulled from the pool which is what the other property check-valid-connection-sql basically does.

所以现在，我倾向于使用 exception-sorter-class-name 的方法，但我不确定这是否对于MS SQL Server，这是最好的方法。

So for now, I'm leaning towards an approach which uses exception-sorter-class-name but I'm not sure whether this is the best approach in the case of MS SQL Server.

希望听到您对该主题的建议。谢谢！

Hoping to hear your suggestions on the topic. Thanks!

推荐答案

我不确定它是否会像你描述的那样工作（透明）。

I am not sure it will work the way you describe it (transparently).

有效的连接检查程序（可以是* ds.xml文件中的sql语句或执行提升的类）是在从池中获取连接时调用的，如数据库可能已在池中关闭它。如果连接不再有效，则关闭
并从数据库请求新的连接 - 这对应用程序完全透明并且只在连接时发生（如您所说）被带出游泳池。然后，您可以在应用程序中长时间使用它。

The valid connection checker (this can be either a sql statement in the *ds.xml file or a class that does the lifting) is meant to be called when a connection is taken from the pool, as the db could have closed it while it is in the pool. If the connection is no longer valid, it is closed and a new one is requested from the DB - this is completely transparent to the application and only happens (as you say) when the connection is taken out of the pool. You can then use that in your application for a long time.

异常分拣机用于向应用程序报告，例如： ORA-0815是SQL语句的无害或错误的返回代码。如果它是一个无害的，它基本上被吞下，而对于一个坏的，它被报告给应用程序作为例外。

The exception sorter is meant to report to the application if e.g. ORA-0815 is a harmless or bad return code for a SQL statement. If it is a harmless one it is basically swallowed, while for a bad one it is reported to the application as an Exception.

因此，如果您想使用异常分类器来查找池中的错误连接，您需要做好准备，基本上每个您触发的语句都可能抛出过时的连接例外，您需要关闭连接并尝试获取新连接。这意味着代码中的适当更改，您当然可以这样做。

So if you want to use the exception sorter to find bad connections in the pool, you need to be prepared that basically every statement that you fire could throw a stale-connection Exception and you would need to close the connection and try to obtain a new one. This means appropriate changes in your code, which you can of course do.

我认为不时会在数据库中触发一个廉价的sql语句来检查来自游泳池仍然有效比手动进行所有这些检查要便宜得多。

I think firing a cheap sql statement at the DB every now and then to check if a connection from the pool is still valid is a lot less expensive than doing all this checking 'by hand'.

Btw：虽然有通用连接检查程序sql适用于所有数据库，一些数据库提供了另一种测试连接是否良好的方法; Oracle为此提供了一个特殊的ping命令，该命令在您引用的特殊OracleConnectionChecker类中使用。所以可能是MS-SQL有类似的东西，它比简单的SQL语句便宜。

Btw: while there is the generic connection checker sql that works with all databases, some databases provide another way of testing if the connection is good; Oracle has a special ping command for this, which is used in the special OracleConnectionChecker class you refer to. So it may be that there is something similar for MS-SQL, which is less expensive than a simple SQL statement.

这篇关于用于MS SQL Server的JBoss AS 5数据库连接池重新连接例程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！