Connection to the oracle database failing after 20 minutes

Question

I have a Java application which is connecting to an Oracle database, but my application is losing its connection after 20 minutes. It will works after closing and logging:

My poolingDatasourceExample.java is given below:

Properties props = new Properties();
props.put("user", uname);
props.put("password", pass);
props.put("password", pass);
props.put("testWhileIdle", "true");
props.put("testOnBorrow", "true");
props.put("testOnReturn", "false");
props.put("validationQuery", "SELECT 1");
props.put("validationInterval", "30000");
props.put("timeBetweenEvictionRunsMillis", "5000");
props.put("maxActive", "100");
props.put("minIdle", "10");
props.put("maxWait", "10000");
props.put("initialSize", "10");
props.put("removeAbandonedTimeout", "60");
props.put("removeAbandoned", "true");
props.put("logAbandoned", "true");
props.put("minEvictableIdleTimeMillis", "30000");
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI, props);

Answer 1

Are you storing the java.sql.Connection object, and seeing it stop working after 20 minutes? That's quite normal, particularly if it's inactive in between (see, for example, this post). Inactive connections will be closed by the database and quite possibly by the network, to protect against zombie processes hogging all the resources. They're also bad at error recovery - once the Connection breaks, you need to recreate it. They're not designed for sharing either, although they are technically thread-safe.

Rather than keeping the Connection, you should keep the factory and request a new Connection when needed. Better still, use a connection pool - these typically test connections for disconnects and recreate them when needed. They also allow your application to share connections efficiently. Connection pooling is available in most app servers, but if your deployment doesn't allow that it's possible to create one of your own.