sql2o can't rolback ?

[mailtous]

The "connection.rollback()" has execute,But data still updated.
EG :

    public void write() {
        try (Connection connection = sql2o.beginTransaction()) {
            Query q = connection.createQuery(" UPDATE `users` set name = 'alice' where id = 1");
            q.setAutoDeriveColumnNames(true);
            q.executeUpdate();
            // DO SAVE
            connection.commit();
            // TEST DATA ROLLBACK, The "connection.rollback()" has execute but data still updated.
            connection.rollback();
        }
    }

[mbraunerDE]

Hey @mailtous ,

the connection.commit(); writes all data to database, only all changes until last commit can be rollbacked.

e.g.

    public void write() {
        try (Connection connection = sql2o.beginTransaction()) {
            Query q = connection.createQuery(" UPDATE `users` set name = 'alice' where id = 1");
            q.setAutoDeriveColumnNames(true);
            q.executeUpdate();
            // DO SAVE
            connection.commit();
            // TEST DATA  name is alice
            Query q = connection.createQuery(" UPDATE `users` set name = 'dirk' where id = 1");
            q.executeUpdate();
            // TEST DATA ROLLBACK, The "connection.rollback()" is executed, the name is alice, not dirk.
            connection.rollback();

        }
    }

[mailtous]

@mbraunerDE
Thanks your answer~! BUT...

 public void write() {
        try (Connection connection = sql2o.beginTransaction()) {
            Query q1 = connection.createQuery(" UPDATE `users` set name = 'alice' where id = 1");
            q1.setAutoDeriveColumnNames(true);
            q1.executeUpdate();
            // DO SAVE
            connection.commit();
            // TEST DATA  name is alice

            Query q2 = connection.createQuery(" UPDATE `users` set name = 'dirk' where id = 1");
            q2.executeUpdate();

            // next row need add "connection.commit()", to real save "dirk"  to DB 
           connection.commit();

            // TEST DATA ROLLBACK, The "connection.rollback()" is executed, the name is alice, not dirk.
            connection.rollback();
        }
    }

** TEST TWO: **

 public void write() {
        try (Connection connection = sql2o.beginTransaction()) {
            Query q1 = connection.createQuery(" UPDATE `users` set name = 'alice' where id = 1");
            q1.setAutoDeriveColumnNames(true);
            q1.executeUpdate();
            // DO SAVE
            connection.commit();
            // TEST DATA  name is alice

            Query q2 = connection.createQuery(" UPDATE `users` set name = 'dirk' where id = 1");
            q2.executeUpdate();

            // q2 is executed but not commit, so  name of dirk is not save to DB. 
        }
    }

[mbraunerDE]

Hey,

you added a second commit(), so there the data will be saved and cannot be rollbacked ...

I recommend you this blog entry https://www.journaldev.com/23932/sql-commit-sql-rollback