Error Unhandled SQLException on conn.rollback()... How to correctly use commit()?

Question

So, I'm trying to learn. I apologize for the slopinnes. This code goes on transactions, so the autocommit must be false, but it throwns an unhandled SQLException on conn.rollback() and I don't understand why if it's inside a catch(exception) already... Shall I wrap everything on another try-catch?

try {
    conn.setAutoCommit(false);

    String sql="SELECT l.toy_id FROM LETTER l WHERE toy_id=?";
    PreparedStatement selectStatement = conn.prepareStatement(sql);

    String sqlD="DELETE FROM TOY WHERE toy_id=?";
    PreparedStatement deleteStatement = conn.prepareStatement(sqlD);

    String sqlU="INSERT INTO TOY (toy_id, toy_name, price,toy_type, manufacturer) VALUES (?,?,?,?,?)";
    PreparedStatement UpdateStatement = conn.prepareStatement(sqlU);

    // TODO Update or delete Toy for every row in file
    for (List<String> row : fileContents) {//!!!!!!!no borrar!!!    
        int toy_id=getToyId(row);   

        //should another try go here??
        selectStatement.setInt(1, toy_id);
        ResultSet rs = selectStatement.executeQuery(sql);
        if (!rs.first()) {//HERE WE DELETE
            deleteStatement.setInt(1, toy_id);
            deleteStatement.executeUpdate();
        }else {
          while (rs.next()) {//HERE WE UPDATE
                UpdateStatement.setInt(1, toy_id);
                UpdateStatement.executeUpdate(); 
          } 
        }       
        rs.close();     
    }
    conn.commit();  
}
catch (SQLException e){
    System.out.println("ERRRROOOOOOORRRRR");
    conn.rollback();
}

*" Shall I wrap everything on another try-catch?"* - Probably, yes. The reason it's giving you an issue is the statement is executed out side of the context of a `try-catch` block (yes, you're inside a "catch" block, but that's not the samething) — MadProgrammer, May 21 '18 at 00:47
On a side note, why not use a database stored procedure to carry out the select, insert, update and delete operations and invoke the stored procedure from Java by passing necessary parameters? — Jacob, May 21 '18 at 06:27

score 0 · Answer 1 · answered May 21 '18 at 08:58

0

First try should referring to the connection (conn.setAutoCommit(false);...) and an inside try should referring to the statements of transaction. See Transaction rollback on SQLException using new try-with-resources block

answered May 21 '18 at 08:58

sorineatza

106
7

score 0 · Answer 2 · answered May 22 '18 at 08:14

add below code in your finaly block:

    try {  
        if (connection != null) {  
        connection.rollback();  
        connection.setAutoCommit(true);  
    }  
    } catch (SQLException e) {  
        LOG.error(e.getMessage(), e);  
    }

Note: You can set auto commit to true again

Error Unhandled SQLException on conn.rollback()... How to correctly use commit()?

2 Answers2