Unable to close JDBC resources!!!

Question

We are running a websphere commerce site with an oracle DB and facing an issue where we are running out of db connections. We are using a JDBCHelper singleton for getting the prepared statements and cosing the connections.

public static JDBCHelper getJDBCHelper() {

                if (theObject == null){           
                    theObject = new JDBCHelper();
                }

        return theObject;
}
public void closeResources(Connection con, PreparedStatement pstmt, ResultSet rs){

        try{
            if(rs!=null){ rs.close();}
        }catch(SQLException e){
            logger.info("Exception closing the resultset");
        }try{
            if(pstmt!=null) { pstmt.close(); }
        }catch(SQLException e){
            logger.info("Exception closing the preparedstatement");
        }try{
            if(con!=null){ con.close(); }

        }catch(SQLException e){
            logger.info("Exception closing the connection");
        }
}

However when we try getting the connection using a prepStmt.getConnection() for passing to the close resources after execution it throws an sql exception. Any idea why? Does the connection get closed immediately after execution? And is there something wrong in our use of the singleton JDBCHelper?

EDIT

Part of the code which makes the prepared statement,executes and closes the connection

PreparedStatement pstmt = jdbcHelper.getPreparedStatement(query);
try{
//rest of the code
int brs = pstmt.executeUpdate();
}

finally{
       try {
        jdbcHelper.closeResources(pstmt.getConnection(),pstmt);
      } catch (SQLException e1) {
       logger.logp(Level.SEVERE,CLASS_NAME,methodName,"In the finally block - Could not close connection", e1);
      }
     }

Answer 1

Your connection will most likely come from a pool, and closing it actually will return the connection to the pool (under the covers). I think posting the code which gets the connection, uses it and closes it via JDBCHelper will be of more use.

Re. your singleton, I'm not sure why you're using this, since it doesn't appear to have anything to warrant it being a singleton. Check out Apache Commons DbUtils which does this sort of stuff and more besides.

Answer 2

This code seems to be written for single threaded operation only, as it's lacking any synchronisation code. The getJdbcHelper() method for instance is likely to create two JdbcHelpers. If I'm not mistaken there's even no guarantee that a second thread will see theObject, long after a primary thread has created it. Although they usually will, by virtue of the architecture the JVM runs on.

If you're running this inside a web server you're likely to be running into race issues, where two threads are modifying your connection at the same time. Unless you rolled your own connection pool or something.

Brian is right, use one of the freely available libraries that solve this (hard) problem for you.