How to execute RollBack after an error?

Question

I'm building an app with Java that storage the datas into Mysql database.

So I'm building the code that first of save the data, setAutocommit=false, if the datas are correctly save on database setAutocommit=true.

If there is an error execute a rollback of data.

So the code is execute never error but not works because, the data that I try to save have a Primary Key Integer autoincrement and if there is an error at the next save, the system jump the number.

For example in my database the filed CodiceFattura = 100, if I try to save another record CodiceFattura = 101. Now If I have CodiceFattura = 100, I try to save, I have an error, I fixed the error then save CodiceFattura = 102 and not 101.

This is the code:

 public boolean salvaFattura(String tipoFattura){
        fatturaIstanza= istanziaValori(null);
        boolean tuttoOk=false;
        try{
            setCommitManager(false);
            tuttoOk=modelManager.getFatturaManager().inserisciFatturaArticoliSenzaCodice(fatturaIstanza,tipoFattura);
            if(!tuttoOk){
                VisualMessageFattura.getErrore();
                eseguiRollBack();
            }
            Integer.parseInt(null);
            setCommitManager(true);
        }
        catch(Exception e){
            VisualMessage.getShowMessaggioErroreFatturaSalvata("Fattura");
            log.logStackTrace(e);
            eseguiRollBanck();
            return false;
        }
        return tuttoOk;
    }

    public void eseguiRollBanck(){
        try{
            modelManager.getFatturaManager().eseguiRollBack();
        }catch(Exception e){
            log.logStackTrace(e);
        }
        try{
            db.eseguiRollBack();
        }catch(Exception e){
            log.logStackTrace(e);
        }
    }

    public void setCommitManager(Boolean state){
        modelManager.getFatturaManager().setAutoCommit(state);
    }

    public void eseguiRollBack(){
            try {
                db.rollback();
            } catch (SQLException e) {
                log.logStackTrace(e);
                //VisualMessage.getErroreDB();
            }
        }

public void setAutoCommit(boolean autoCommit){
        try {
            db.setAutoCommit(autoCommit);
        } catch (SQLException e) {
            log.logStackTrace(e);
            VisualMessage.getErroreDB();
        }
    }

don't explicitly pass the value for primary key. so DB will take care if that field — KhAn SaAb, Aug 12 '16 at 10:55
You leave the autocommit set to off. You verify your data before you write to the database. You perform a commit after all of the data is written to the database to ensure the consistency of the data. A rollback is for a database error, not data validation. — Gilbert Le Blanc, Aug 12 '16 at 11:06
@GilbertLeBlanc I set autoCommitFalse, if it is all OK, I set autocommit(true). If I have an error during the storege, I want to do rollback but this not works+ — bircastri, Aug 12 '16 at 11:19
I'm not sure if you are just asking becasue of the autoincrement. The autoincrement-value will NOT be reverted if you do a rollback no matter how you do it, mysql just does not support that (you would have to reset it if you need it to be continous). If that is your only indication that your code does not work (I didn't check it), it might be working after all. — Solarflare, Aug 12 '16 at 12:57

score 1 · Answer 1 · edited May 23 '17 at 12:08

1

This is the same issue as the following:

COMMIT OR conn.setAutoCommit(true)

In short, you aren't using setAutoCommit the correct way, so this code behaviour is correct, you should either explicitly call the commit method or leave Auto Commit to get on with automatically committing transactions.

edited May 23 '17 at 12:08

Community

1
1

answered Aug 12 '16 at 10:56

Graham Nicol

163
1
9

mmm thanks for your answer. But in the method salvaFattura, I save the fattura in my database but I do other operation in database, and if all the operation are completed correctly I want to set autocommit true. – bircastri Aug 12 '16 at 11:05
AutoCommit means that every time you execute a SQL statement it will commit that as its own transaction, so it's not what you want to happen. – Graham Nicol Aug 12 '16 at 12:45
What you are saying you want is to do **all** of your SQL statements and then, based on the results of those statements, either rollback the transaction or to commit it. In that scenario you need to have AutoCommit as false, and then explicitly call either `commit()` or `rollback()` to make the save either happen or not. – Graham Nicol Aug 12 '16 at 12:47

How to execute RollBack after an error?

1 Answers1