oracle 如何从池中删除无效的数据库连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6030512/
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
How to remove invalid database connection from pool
提问by Dyapa Srikanth
I am using connection pooling of tomcatwith oracledatabase. It is working fine, but when i use my application after a long time it is giving error that "connection reset". I am getting this error because of physical connection at oracle server closed before logical connection closed at tomcat datasource. So before getting the connection from datasource i am checking the connection validity with isValid(0)method of connection object which gives false if the physical connection was closed. But i don't know how to removethat invalid connectionobject from the pool.
我正在将tomcat 的连接池与oracle数据库一起使用。它工作正常，但是当我长时间使用我的应用程序时，它给出了“连接重置”的错误。我收到此错误是因为在 tomcat 数据源关闭逻辑连接之前关闭了 oracle 服务器上的物理连接。因此，在从数据源获取连接之前，我正在使用连接对象的isValid(0)方法检查连接有效性，如果物理连接已关闭，则该方法会给出 false。但我不知道如何从池中删除那个无效的连接对象。
采纳答案by Dyapa Srikanth
I used validatationquerywhile configuring the datasource in server.xmlfile. It is going to check the validity of the connection by executing the query at database before giving to the application.
我在server.xml文件中配置数据源时使用了validationquery。在提供给应用程序之前，它将通过在数据库中执行查询来检查连接的有效性。
for Oracle
对于甲骨文
validationQuery="/* select 1 from dual */"
for MySql
用于MySql
validationQuery="/* ping */"
回答by John Kane
This could be because on the db server, there is a timeout to not allow connections to live beyond a set time, or to die if it does not receive something saying it is still valid. One way to fix this is to turn on keepalives. These basically ping the db server saying that they are still valid connections.
这可能是因为在 db 服务器上，有一个超时，不允许连接超过设定的时间，或者如果它没有收到表明它仍然有效的东西就会死亡。解决此问题的一种方法是打开 keepalive。这些基本上是 ping 数据库服务器，说它们仍然是有效的连接。
Thisis a pretty good link on Tomcats DBCP configurations. Take a look at the section titled "Preventing dB connection pool leaks". That looks like it may be a good place to start.
这是关于 Tomcats DBCP 配置的一个很好的链接。查看标题为“防止 dB 连接池泄漏”的部分。这看起来可能是一个不错的起点。
回答by wenpin
If we want to dispose an ill java.sql.connection from Tomcat jdbc connection pool,
如果我们想从Tomcat jdbc连接池中处理一个有问题的java.sql.connection，
we may do this explicitly in the program. Unwrap it into an org.apache.tomcat.jdbc.pool.PooledConnection, setDiscarded(true) and close the JDBC connection finally. The ConnectionPool will remove the underlying connection once it has been returned.
我们可以在程序中明确地这样做。将其解包为 org.apache.tomcat.jdbc.pool.PooledConnection，setDiscarded(true) 并最终关闭 JDBC 连接。一旦返回，ConnectionPool 将删除底层连接。
(ConnectionPool.returnConnection(....))
(ConnectionPool.returnConnection(....))
e.g. PooledConnection pconn = conn.unwrap(PooledConnection.class); pconn.setDiscarded(true); conn.close();
例如 PooledConnection pconn = conn.unwrap(PooledConnection.class); pconn.setDiscarded(true); 连接。关闭（）；
回答by DaTroop
Try closing it and opening it if it's invalid. I mean u would reinitialize it in this way so u won't need to remove it from the pool and reuse it.
如果无效，请尝试关闭它并打开它。我的意思是你会以这种方式重新初始化它，这样你就不需要从池中删除它并重新使用它。
