JDBC connection leak

Question

I am currently facing connection leak problem in my code (Java , Struts). I have closed all the result sets, prepared statements, callable statements and the connection in the finally blocks of all the methods in my dao. still I face the issue.Additional information is , I am using StructDescriptor.createDescriptor for creating oracle objects. Will it cause any connection leaks? Please advise.

Code below

         public boolean updatedDetails(Distribution distribution, String    appCode, Connection dbConnection) {
    boolean savedFlag = true;
    CallableStatement updateStoredProc = null;
    PreparedStatement pstmt1 = null;
    try {
        logger.debug("In DistributionDAO.updatedDistributionDetails");
        //PreparedStatement pstmt1 = null;
        ARRAY liArray = null;
        ARRAY conArray = null;
        ARRAY payArray = null;
    ArrayDescriptor licenseeArrDesc = ArrayDescriptor.createDescriptor(LICENSEE_TAB, dbConnection);
        ArrayDescriptor contractArrDesc = ArrayDescriptor.createDescriptor(DISTRIBUTION_CONTRACT_TAB, dbConnection);
        ArrayDescriptor paymentArrDesc = ArrayDescriptor.createDescriptor(DISTRIBUTION_PAYMENT_TAB, dbConnection);
        licenseeArray = new ARRAY(licenseeArrDesc, dbConnection, licenseeEleList.toArray());
            contractArray = new ARRAY(contractArrDesc, dbConnection, contractEleList.toArray());
            paymentArray = new ARRAY(paymentArrDesc, dbConnection, paymentEleList.toArray());           
            updateStoredProc = dbConnection.prepareCall("{CALL DIS_UPDATE_PROC(?,?,to_clob(?),?,?,?,?)}");
            updateStoredProc.setLong(1, distribution.getDistributionId());
            updateStoredProc.setString(2, distribution.getId());
            updateStoredProc.setString(3, distribution.getNotes());
            updateStoredProc.setString(4, distribution.getNotesUpdateFlag());
            updateStoredProc.setArray(5, liArray);
            updateStoredProc.setArray(6, conArray);
            updateStoredProc.setArray(7, payArray);
            String sql1="Update STORY set LAST_UPDATE_DATE_TIME= sysdate WHERE STORY_ID = ? ";
            pstmt1=dbConnection.prepareStatement(sql1);
            pstmt1.setLong(1,distribution.getStoryId());
            pstmt1.execute();
            List<Object> removedEleList = new ArrayList<Object>();
            removedEleList.add(createDeleteElementObject(removedEle, dbConnection));
            catch (SQLException sqle) {
        savedFlag = false;

    } catch (Exception e) {
        savedFlag = false;

    } finally {
        try {
            updateStoredProc.close();
            updateStoredProc = null;            
            pstmt1.close();
            pstmt1 = null;      
            dbConnection.close();
        } catch (SQLException e) {

        }
    }
    return savedFlag;
}




// Method createDeleteElementObject
private Object createDeleteElementObject(String removedEle,
        Connection connection) {

    StructDescriptor structDesc;
    STRUCT structObj = null;
    try {
        structDesc = StructDescriptor.createDescriptor(DISTRIBUTION_REMOVED_ELEMENT_OBJ, connection);
        if(removedEle != null) {
            String[] tmpArr = removedEle.split("\\|");
            if(tmpArr.length == 2) {
                Object[] obj = new Object[2];
                String eleType = tmpArr[0];
                long eleId = Integer.parseInt(tmpArr[1]);
                obj[0] = eleType.toUpperCase();
                obj[1] = eleId;
                structObj = new STRUCT(structDesc, connection, obj);
            }
        }
    } catch (ArrayIndexOutOfBoundsException e) {

    } catch (NumberFormatException e) {

    } catch (SQLException e) {

    }

    return structObj;
}

I strongly suggest your switch to try-with-resources. The way you closed resources in your code might leak. Consider what happens if `updateStoredProc.close()` or `pstmt1.close()` throws an exception. — Mark Rotteveel, Apr 07 '16 at 10:12
Thank you for your response. Could you please explain more? I am not clear on what try-with-resources mean. — lal1990, Apr 07 '16 at 10:57
See [The try-with-resources Statement](http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html) — Mark Rotteveel, Apr 08 '16 at 08:06

score 2 · Answer 1 · edited May 23 '17 at 11:45

Some hints on your code:

You pass a Connection variable into your call but close it inside your call - is the caller aware of that fact? It would be cleaner to get the connection inside your code or return it unclosed (calling method is responsible)

Exceptions are meant to be caught, not ignored - you don't log your exception - you'll never know what happens. I bet a simple e.printStackTrace() in your catch blocks will reveal helpful information.

Use try-with-resource (see this post)

//Resources declared in try-with-resource will be closed automatically.
try(Connection con = getConnection();
    PreparedStatement ps = con.prepareStatement(sql)) {

    //Process Statement...

} catch(SQLException e) {
  e.printStackTrace();
}

At the very least put every close inside single try-catch:

} finally {
    try {
        if(updateStoredProc != null) {
          updateStoredProc.close();   
        }         
    } catch (SQLException e) {
        e.printStackTrace();
    }
    try {
        if(pstmt1!= null) {
          pstmt1.close();   
        }         
    } catch (SQLException e) {
        e.printStackTrace();
    }
    try {
        if(dbConnection != null) {
          dbConnection.close();   
        }         
    } catch (SQLException e) {
        e.printStackTrace();
    }
  }

Thanks for your response Jan, I tried closing the statements and connections by the second method. Still I am getting the connection leak issue. After hitting the method the amount of times declared in my standalone.xml maximum pool, I get no managed exceptions error. — lal1990, Apr 08 '16 at 02:52
Oh - and you call many methods from there passing on the connection as parameter. You need to check them as well (share the code?) — Jan, Apr 08 '16 at 07:34
Yeah... I am passing the connection as parameter for other methods as well. The code I have already shared in my original post. Please refer to it. — lal1990, Apr 08 '16 at 09:53

JDBC connection leak

1 Answers1