超时后如何重新建立 JDBC 连接？
我有一个长时间运行的方法，它通过 EntityManager (TopLink Essentials) 执行大量本机 SQL 查询。每个查询只需要几毫秒即可运行，但查询数量却有数千个。这发生在单个 EJB 事务内。 15 分钟后，数据库关闭连接，导致以下错误：
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b02-p04 (04/12/2010))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Closed Connection
Error Code: 17008
Call: select ...
Query: DataReadQuery()
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
.
.
.
RAR5031:System Exception.
javax.resource.ResourceException: This Managed Connection is not valid as the phyiscal connection is not usable
at com.sun.gjc.spi.ManagedConnection.checkIfValid(ManagedConnection.java:612)
在 JDBC 连接池中，我设置了 is-connection-validation-required="true" 和connection-validation-method= “table” 但这没有帮助。
我认为 JDBC 连接验证就是为了处理这种错误。我还查看了 TopLink 扩展 (http://www.oracle.com/technetwork/middleware/ias/toplink-jpa-extensions-094393.html) 的某种超时设置，但什么也没找到。还有 TopLink 会话配置文件（http://download.oracle.com/docs/cd/B14099_19/web.1012/b15901/sessions003.htm），但我认为那里也没有任何有用的东西。
我无权访问 Oracle DBA 表，但我认为 Oracle 根据 CONNECT_TIME 配置文件变量中的设置在 15 分钟后关闭连接。
有没有其他方法可以让 TopLink 或 JDBC 池重新建立关闭的连接？
数据库为Oracle 10g，应用服务器为Sun Glassfish 2.1.1。
I have a long-running method which executes a large number of native SQL queries through the EntityManager (TopLink Essentials). Each query takes only milliseconds to run, but there are many thousands of them. This happens within a single EJB transaction. After 15 minutes, the database closes the connection which results in following error:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b02-p04 (04/12/2010))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Closed Connection
Error Code: 17008
Call: select ...
Query: DataReadQuery()
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
.
.
.
RAR5031:System Exception.
javax.resource.ResourceException: This Managed Connection is not valid as the phyiscal connection is not usable
at com.sun.gjc.spi.ManagedConnection.checkIfValid(ManagedConnection.java:612)
In the JDBC connection pool I set is-connection-validation-required="true" and connection-validation-method="table" but this did not help .
I assumed that JDBC connection validation is there to deal with precisely this kind of errors. I also looked at TopLink extensions (http://www.oracle.com/technetwork/middleware/ias/toplink-jpa-extensions-094393.html) for some kind of timeout settings but found nothing. There is also the TopLink session configuration file (http://download.oracle.com/docs/cd/B14099_19/web.1012/b15901/sessions003.htm) but I don't think there is anything useful there either.
I don't have access to the Oracle DBA tables, but I think that Oracle closes connections after 15 minutes according to the setting in CONNECT_TIME profile variable.
Is there any other way to make TopLink or the JDBC pool to reestablish a closed connection?
The database is Oracle 10g, application server is Sun Glassfish 2.1.1.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
所有 JPA 实现（在 Java EE 容器上运行）都使用具有关联连接池的数据源来管理与数据库的连接。
持久化上下文本身通过 persistence.xml 中的适当条目与数据源关联。如果要更改客户端的连接超时设置，则必须重新配置关联的连接池。
在 Glassfish 中，可以通过编辑池设置来重新配置与连接池关联的超时设置，如以下链接中所列：
在服务器上-side（其设置如果低于客户端设置，将更为重要），Oracle 数据库可以配置为具有与用户帐户关联的数据库配置文件。 配置文件的会话idle_time和connect_time参数将构成客户端-服务器交互的这方面的重要超时设置。如果未设置配置文件，则默认情况下，超时是无限制的。
All JPA implementations (running on a Java EE container) use a datasource with an associated connection pool to manage connectivity with the database.
The persistence context itself is associated with the datasource via an appropriate entry in
persistence.xml. If you wish to change the connection timeout settings on the client-side, then the associated connection pool must be re-configured.In Glassfish, the timeout settings associated with the connection pool can be reconfigured by editing the pool settings, as listed in the following links:
On the server-side (whose settings if lower than the client settings, would be more important), the Oracle database can be configured to have database profiles associated with user accounts. The session idle_time and connect_time parameters of a profile would constitute the timeout settings of importance in this aspect of the client-server interaction. If no profile has been set, then by default, the timeout is unlimited.
除非您有某种 RAC 故障转移，否则当连接终止时，它将结束会话和事务。
管理员可能设置了一些限制，以防止事务失控或单个作业“占用”池中的连接。您通常不希望长时间锁定池中的连接。
如果这些查询不一定是同一事务的一部分，那么您可以尝试终止并重新启动新连接。
您是否能够重组代码，使其在 15 分钟内完成。后台存储过程可能比通过网络拖动数千个操作的结果更快地完成这项工作。
Unless you've got some sort of RAC failover, when the connection is terminated, it will end the session and transaction.
The admins may have set into some limits to prevent runaway transactions or a single job 'hogging' a connection in a pool. You generally don't want to lock a connection in a pool for an extended period.
If these queries aren't necessarily part of the same transaction, then you could try terminating and restarting a new connection.
Are you able to restructure your code so that it completes in under 15 minutes. A stored procedure in the background may be able to do the job a lot quicker than dragging the results of thousands of operations over the network.
我看到您设置了
connection-validation-method="table"和is-connection-validation-required="true"，但您没有提到您指定了您正在验证的表；您是否设置了validation-table-name="any_table_you_know_exists"并提供了任何现有的表名称？validation-table-name="existing_table_name"是必需的。有关连接验证的更多详细信息，请参阅本文。
相关 StackOverflow 文章类似的问题 - 他想刷新整个无效连接池。
I see you set your
connection-validation-method="table"andis-connection-validation-required="true", but you do not mention that you specified the table you were validating on; did you setvalidation-table-name="any_table_you_know_exists"and provide any existing table-name?validation-table-name="existing_table_name"is required.See this article for more details on connection validation.
Related StackOverflow article with similar problem - he wants to flush the entire invalid connection pool.