How to close CallableStatement when using CallableStatementCreator and CallableStatementCallback

Question

How to close CallableStatement in the following code snippet. Sonarcube is complaining me to close the CallableStatement

I added finally block and closed callableStatement in doInCallableStatement method. It is working perfectly and callableStatement is getting closed but still Sonarcube complaining me to close the callableStatement.

If I add finally block and closed callableStatement in createCallableStatement method. Sonarcube is not complaining but CallableStatementCallback is failing since the callableStatement is already closed.

@Override
public MyInfo getMyInfo(String id) throws SecurityDAOException, AuditException {
    MyInfo myInfo = null;
    try {
        myInfo = jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {
                CallableStatement callableStatement = null;
                callableStatement = connection.prepareCall(STORED_PROCEDURE);
                callableStatement.setString("ID", Id);
                callableStatement.registerOutParameter("REQID", OracleTypes.VARCHAR);
                callableStatement.registerOutParameter("TYPE", OracleTypes.VARCHAR);
                return callableStatement;

                // If I add finally block here and close callableStatement, 
                // Sonarcube is not complaining but CallableStatementCallback 
                // is failing since the callableStatement is already closed.
            }
        }, new CallableStatementCallback<MyInfo>() {
            @Override
            public MyInfo doInCallableStatement(CallableStatement callableStatement) throws SQLException {
                try {
                    MyInfo myInfo = new MyInfo();
                    callableStatement.execute();
                    myInfo.setSsn(callableStatement.getString("REQID"));
                    myInfo.setSsnType(String.valueOf(callableStatement.getString("TYPE").charAt(0)));
                    return myInfo;
                } finally {
                    if (null != callableStatement) {
                        try {
                            callableStatement.close();
                        } catch (SQLException e) {
                            LOGGER.error("Not able to close CallableStatement:", e);
                        }
                    }
                }
            }
        });
    } catch (DataAccessException dataAccessException) {
        throw new SecurityDAOException(dataAccessException, INTERNAL_SERVER_ERROR,
                RESPONSE_CODE_INTERNAL_SERVER_ERROR, dataAccessException.getCause().getMessage());
    } catch (Exception e) {
        throw new AuditException(e);
    }
    return myInfo;
}

Update - (updated the code based on comments to make it work)

@Vasan, I tried your approach but For some reason, I was not able to make it work. I getting the following exception "org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call MY_PROCEDURE(?,?)}]; SQL state [99999]; error code [17090]; operation not allowed: Ordinal binding and Named binding cannot be combined!; nested exception is java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!"

Eventhough I am not combining Ordinal binding and Named binding. Any help is appreciated.

@Override
public String getMyInfo(String id) throws SecurityDAOException, AuditException {
    String status = null;
    try {
        CallableStatementCreatorFactory cscFactory = new CallableStatementCreatorFactory(
                MY_PROCEDURE);
        cscFactory.addParameter(new SqlParameter("ID", OracleTypes.VARCHAR));
        cscFactory.addParameter(new SqlOutParameter("MY_OUTPUT", OracleTypes.VARCHAR));

        final Map<String, Object> actualParams = new HashMap<>();
        actualParams.put("ID", id);

        CallableStatementCreator callableStatementCreator = cscFactory.newCallableStatementCreator(actualParams);

        CallableStatementCallback<String> callableStatementCallback = new CallableStatementCallback<String>() {
            @Override
            public String doInCallableStatement(CallableStatement callableStatement) throws SQLException {
                String status = null;
                callableStatement.execute();
                status = callableStatement.getString(2);
                return status;
            }
        };

        status = jdbcTemplate.execute(callableStatementCreator, callableStatementCallback);

    } catch (DataAccessException dataAccessException) {
        throw new SecurityDAOException(dataAccessException, INTERNAL_SERVER_ERROR,
                RESPONSE_CODE_INTERNAL_SERVER_ERROR, dataAccessException.getCause().getMessage());
    } catch (Exception e) {
        throw new AuditException(e);
    }
    return status;
}

Answer 1

With your current approach, it doesn't seem possible to avoid the SONAR issue. In fact, Spring closes the statement by itself, so you shouldn't even be closing the statement in the CallableStatementCallback impl class, let alone in CallableStatementCreator impl class.

Quote from javadocs

Spring will close the Statement object after the callback returned

There is an alternate way to create a CallableStatementCreator, however, instead of creating your own impl class, which might help you avoid SONAR issues. You can use CallableStatementCreatorFactory.

CallableStatementCreatorFactory cscFactory = new CallableStatementCreatorFactory(STORED_PROCEDURE);
cscFactory.addParameter(new SqlParameter("ID", OracleTypes.VARCHAR));
cscFactory.addParameter(new SqlOutParameter("REQID", OracleTypes.VARCHAR));
cscFactory.addParameter(new SqlOutParameter("TYPE", OracleTypes.VARCHAR));

CallableStatementCreator cscCreator  = cscFactory.newCallableStatementCreator(Collections.singletonMap("ID", id));
jdbcTemplate.execute(cscCreator, [...])

I have not used this myself before, and this was written using only the javadocs. So there might be some minor tweaking of this code needed for your needs.