0

I'm using JDBC to connect to oracle and im not explicitly closing JDBC connection, but after running my application for few days, i see that SQL statements are failing with "java.sql.SQLException: Closed Connection".

What could be the possible reasons for that, i'm sure that i'm not closing it by calling java.sql.Connection.close() method.

Uday
  • 136
  • 1
  • 8
  • if you don't use the connection for a specified amount of time, java closes the connection after the idle timeout time is crossed.... if you don't specify this parameter during connection object creation there is a default value which i don't remember properly... therefore when this time is crossed the connection is automatically closed – Abhishek Oct 26 '16 at 08:46
  • @Abhishek Please provide your source for this assertion. – user207421 Oct 26 '16 at 08:46
  • @Abhishek i'm continuously using the connection. I will check my code once again. Thank you – Uday Oct 26 '16 at 08:48
  • Are you using `try-with-resources`? Or a connection pool? – user207421 Oct 26 '16 at 08:50
  • @EJP we are using our own connection pool which is designed by our developers – Uday Oct 26 '16 at 08:51
  • 1
    So your connection pool, 'designed by your developers' is by far the most likely suspect for premature closing. Rather than Java. I can only suggest they test it some more. Better still, throw it away and use one that already works, such as Apache DBCP. – user207421 Oct 26 '16 at 08:54
  • @EJP we had 2 oracle database servers in our LAB, with one database we are not facing such issue even after running for application for months. So i think our connection pool is working fine – Uday Oct 26 '16 at 09:03
  • You don't have any reason to use a homegrown connection pool when proven examples already exist. I've been using Apache DBCP for seven years without problems. It's a no-brainer. Try a configuration with Apache DBCP, or with no pooling at all, before you conclude it's somebody else's fault. – user207421 Oct 26 '16 at 09:34
  • Oracle server can kill your connection either through explicit admin command, process kill in OS or by timeout. http://stackoverflow.com/questions/3363907/setting-oracle-11g-session-timeout – Alexander Anikin Oct 26 '16 at 10:32

1 Answers1

0

but after running my application for few days

Oracle may close the connection after some idle time or a proxy somewhere between your app and Oracle. Best way to keep connections alive is to maintain a heartbeat link. Most databases provide a validation query which is "SELECT 1" or something else.

If you use a pooling mechanism, it is just a configuration. Otherwise you have to do it manually your own.

java.sql.Connection.isValid(timeout)

If not, refresh the connection

sura2k
  • 7,365
  • 13
  • 61
  • 80
  • we used validation query previously but it is causing us performance issue. Can we use closed connection if we refresh it? – Uday Oct 26 '16 at 09:01
  • If the server closed the connection you would get a different error message. This one suggests the connection has been closed at the client end, which brings the handmade connection pool into question. – user207421 Oct 26 '16 at 09:01
  • I'm not 100% saying it is Oracle who closes the connection. Also adding validation query should not impact to the performance. It is just a PING. May be a firewall - https://zhefeng.wordpress.com/2009/06/15/oracle-connection-idle-timeout-with-firewall/ – sura2k Oct 26 '16 at 09:11
  • You are 100% saying it isn't the client, and if it isnt' the client it would be a different error message. In this context 'server' includes 'proxy'. – user207421 Oct 26 '16 at 09:32