如何从池中删除无效的数据库连接

I am using connection pooling of tomcat with oracle database. 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 remove that invalid connection object from the pool.

我正在使用tomcat与oracle数据库的连接池。它工作得很好，但是当我使用我的应用程序很长一段时间后，它会产生“连接重置”错误。由于oracle服务器上的物理连接在tomcat数据源上的逻辑连接关闭之前关闭，所以我得到了这个错误。因此，在从数据源获取连接之前，我正在使用连接对象的isValid(0)方法检查连接有效性，如果物理连接被关闭，该方法将给出false。但是我不知道如何从池中删除无效的连接对象。

4 个解决方案

#1

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服务器上有一个超时，不允许连接超过设置的时间，或者如果它没有接收到表示它仍然有效的东西，就会死。解决这个问题的一种方法是打开keepalives。这些基本是ping db服务器，表示它们仍然是有效的连接。

This is 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连接池泄漏”的部分。看起来这是一个好的开始。

#2

I used validatationquery while configuring the datasource in server.xml file. It is going to check the validity of the connection by executing the query at database before giving to the application.

我在服务器中配置数据源时使用了validatationquery。xml文件。它将在给应用程序之前在数据库中执行查询，以检查连接的有效性。

for Oracle

对甲骨文

validationQuery="/* select 1 from dual */"

for MySql

MySql

validationQuery="/* ping */"

#3

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.

试着关闭它并打开它如果它是无效的。我的意思是u将以这种方式重新初始化它，这样你就不需要从池中删除它并重用它。

#4

If we want to dispose an ill java.sql.connection from Tomcat jdbc connection pool,

如果我们想处理不好的java.sql。来自Tomcat jdbc连接池的连接，

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, set丢弃(true)，最后关闭JDBC连接。ConnectionPool将在返回底层连接后删除它。

(ConnectionPool.returnConnection(....))

e.g. PooledConnection pconn = conn.unwrap(PooledConnection.class); pconn.setDiscarded(true); conn.close();

PooledConnection pconn = conn.unwrap(PooledConnection.class);pconn.setDiscarded(真正的);conn.close();

#1