0
votes

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;
        } 
    }       
}
1
What is your connection pool? Hikari/Apache?what is its configuration? What are the diffs between dev and prod?user7294900
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.jayark

1 Answers

1
votes

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