1

I am using below setup for fetching data from oracle DB and connection pooling.

When I do getConnection on this setup, oracle data source always creates new connection and returns, until max limit is reached. Even if I finished executing the statement. how do I release connection?

I know I can use apache dpcp or oracle upc, but I want to fix below code and I can not use any other frameworks.

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import oracle.jdbc.pool.OracleConnectionCacheManager;
import oracle.jdbc.pool.OracleDataSource;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;


public class DatabaseUtility {
  private static final String CACHE_NAME = "DBCACHE";
  private static OracleDataSource ods = null;
  private static final Logger LOGGER = LogManager.getLogger(DatabaseUtility.class);

  public static synchronized void init(Properties properties) throws SQLException {

    if (ods == null) {
      LOGGER.info("OracleDataSource Initialization");
      ods = new OracleDataSource();
      ods.setURL(PropertiesLoader.getValue(properties, "jdbc.datasource.url"));
      ods.setUser(PropertiesLoader.getValue(properties, "jdbc.datasource.username"));
      ods.setPassword(PropertiesLoader.getValue(properties, "jdbc.datasource.password"));

      ods.setConnectionCachingEnabled(true);
      ods.setConnectionCacheName(CACHE_NAME);
      Properties cacheProps = new Properties();
      cacheProps.setProperty(
          "MinLimit", PropertiesLoader.getValue(properties, "jdbc.datasource.minlimit"));
      cacheProps.setProperty(
          "MaxLimit", PropertiesLoader.getValue(properties, "jdbc.datasource.maxlimit"));
      cacheProps.setProperty(
          "InitialLimit", PropertiesLoader.getValue(properties, "jdbc.datasource.minlimit"));
      cacheProps.setProperty(
          "ConnectionWaitTimeout", PropertiesLoader.getValue(properties, "jdbc.datasource.wait"));
      cacheProps.setProperty(
          "ValidateConnection",
          PropertiesLoader.getValue(properties, "jdbc.datasource.validate"));

      ods.setConnectionCacheProperties(cacheProps);
    }
  }

  private DatabaseUtility() {
    throw new AssertionError("Can not create object of DatabaseUtility!");
  }

  public static synchronized Connection getConnection(Properties properties) throws SQLException {
    LOGGER.info("Request connection!");
    if (ods == null) {
      init(properties);
    }
    return ods.getConnection();
  }

  public static void closePooledConnections() throws SQLException {
    if (ods != null) {
      ods.close();
    }
  }

  public static void listCacheInfos() throws SQLException {
    OracleConnectionCacheManager occm =
        OracleConnectionCacheManager.getConnectionCacheManagerInstance();
    LOGGER.info(
        occm.getNumberOfAvailableConnections(CACHE_NAME)
            + " connections are available in cache "
            + CACHE_NAME);
    LOGGER.info(occm.getNumberOfActiveConnections(CACHE_NAME) + " connections are active");
  }
}

So, how can I release a connection for reuse after I finished executing my query?

Bikas Katwal
  • 1,895
  • 1
  • 21
  • 42
  • 2
    What is your actual code that uses connections? You are using a connection pool, so if you call `close()` on the connection, it is returned to the connection pool for reuse. If that doesn't happen, then likely your code is not correctly closing connections. So based on your problem description and the lack of actual usage code, I think you need to fix connection leaks in your code. – Mark Rotteveel Aug 14 '18 at 10:27
  • In my actual code I am not closing it. I am just returning the result set. I thought closing the connection would kill it and not return to pool. Let me try. – Bikas Katwal Aug 14 '18 at 10:29
  • 2
    No, connections should always be closed, for a connection from a connection pool, that is what signals to the pool that it is available for reuse. In any case _"returning result set"_ sounds like you will have a problem with object lifetimes. Don't return result sets, unless you can keep the connection open for the lifetime of the result set, otherwise materialize the result set into a list or other collection. – Mark Rotteveel Aug 14 '18 at 10:33
  • I just changed my code. instead of returning result set moved result set to same method. It works. thanks :) – Bikas Katwal Aug 14 '18 at 10:39
  • Related, possible duplicate: https://stackoverflow.com/questions/4938517/closing-jdbc-connections-in-pool – Mark Rotteveel Aug 14 '18 at 10:53

0 Answers0