0

My application listens to few tables using Oracle UCP. In production environment application works fine, but In test environment, Application runs out of connection s from pool. I am closing and returning used connections in every query as well. Please refer the Connection close method.

public static synchronized void closeConnection(Connection con) throws Exception {
    String print;
    try {
        if (con != null && !con.isClosed()) {
            con.close();
            con =null;
            Config.DB_POOL_COUNT_AVAILABLE = pds.getAvailableConnectionsCount();
            Config.DB_POOL_COUNT_BUSSY = pds.getBorrowedConnectionsCount();
            Config.DB_POOL_COUNT_OPENED = Config.DB_POOL_COUNT_AVAILABLE + Config.DB_POOL_COUNT_BUSSY;      
        } else {
            Config.DB_POOL_COUNT_AVAILABLE = pds.getAvailableConnectionsCount();
            Config.DB_POOL_COUNT_BUSSY = pds.getBorrowedConnectionsCount();
            Config.DB_POOL_COUNT_OPENED = Config.DB_POOL_COUNT_AVAILABLE + Config.DB_POOL_COUNT_BUSSY;              
        }
    } catch (Exception e) {
        Logger.errorLog(e);
    } finally {
        Config.DB_POOL_COUNT_AVAILABLE = pds.getAvailableConnectionsCount();
        Config.DB_POOL_COUNT_BUSSY = pds.getBorrowedConnectionsCount();
        Config.DB_POOL_COUNT_OPENED = Config.DB_POOL_COUNT_AVAILABLE + Config.DB_POOL_COUNT_BUSSY;  
        if(con != null && !con.isClosed()){
            con.close();
            con=null;
        } 
    }       
}
Zoe
  • 27,060
  • 21
  • 118
  • 148
thatGuy
  • 88
  • 7
  • What is your connection pool? Hikari/Apache?what is its configuration? What are the diffs between dev and prod? – Ori Marko Nov 07 '18 at 03:31
  • It's oracle.ucp.jdbc.PoolDataSource Connection. max,min,initial pool sizes are accordingly 30,20,25. Only diff is prod and test environments are separated server. two application servers, with two DB servers. – thatGuy Nov 08 '18 at 03:56

1 Answers1

1

Because your application works fine in production environment, you don't need concern about connection pool too much. In development environment, closing connection after query is not bad.

For good practice with Oracle UCP you can see at https://docs.oracle.com/cd/E18283_01/java.112/e12265/optimize.htm or

https://docs.oracle.com/en/database/oracle/oracle-database/18/jjucp/optimizing-ucp-behavior.html

Vy Do
  • 46,709
  • 59
  • 215
  • 313