SQL exception occurs when trying to close the connection in finally clause

Question

I have some OAF code inside that i am initiating sql connection but when i am trying to close it , it throws sql exception . here is the code.

    try
    {
        conn = 
        (OracleConnection)oadbtransactionimpl.getJdbcConnection();
        String queryForEmpty = 
            "select ... query here"; 
        projectDetailsStatment = 
                conn.prepareStatement(queryForEmpty);
        projectDetailsStatment.setString(1,sprojectid);
        ResultSet rs = projectDetailsStatment.executeQuery();

    }
    catch(SQLException e)
   {
       String sqlErrMsg = e.getMessage();
       throw new OAException((new StringBuilder()).append("handle exception here:").append(sqlErrMsg).toString(), (byte)0);
   }
   finally
   {
      conn.close(); // throws exception here
   }

Answer 1

Try enclose the closing code with try and catch clause

    try
    {
        conn = 
        (OracleConnection)oadbtransactionimpl.getJdbcConnection();
        String queryForEmpty = 
            "select ... query here"; 
        projectDetailsStatment = 
                conn.prepareStatement(queryForEmpty);
        projectDetailsStatment.setString(1,sprojectid);
        ResultSet rs = projectDetailsStatment.executeQuery();

    }
    catch(SQLException e)
   {
       String sqlErrMsg = e.getMessage();
       throw new OAException((new StringBuilder()).append("Exception in Finding Project Type:").append(sqlErrMsg).toString(), (byte)0);
   }
   finally
   {
      try{
      conn.close(); // throws exception here
      }
        catch(SQLException e)
      {
        //your code here
      }
   }

Answer 2

The basic scheme of any JDBC operation is pretty straightforward:

1. Get a connection.
2. Create a statement.

3. Execute the statement and get a ResultSet.

4. Process the ResultSet.

5. Close everything: the ResultSet, the Statement, and the Connection.

BAD SOLUTION

public void handleJDBC()  {
    try {
        DataSource dataSource = getDataSource();
        Connection connection = dataSource.getConnection();
        operation(connection)
        connection.close();
    } catch (SQLException e) {
        //do something here
    }
}

GOOD PRACTISE

finally {
        if (connection != null) {
            try {
            connection.close();
        } catch (SQLException e) {
            if (exception != null) {
                exception = new DoubleException(exception, e);
            } else {
                exception = e;
            }
        }
        }
    }
    if (exception != null) {
        throw exception;
    }