Why rollback is not working as expected in JDBC?

Asked Nov 24 '20 at 08:51

Active Nov 24 '20 at 09:28

Viewed 38 times

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
    public static void main(String[] args) throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/python", "root", "vikalp");
        connection.setAutoCommit(false);
        try(Statement cursor = connection.createStatement()){
            cursor.execute("CREATE TABLE sa(id int)");
        }
        connection.rollback();

    }
}

Here python is my database name why the rollback doesn't happen? How to make it rollback?

asked Nov 24 '20 at 08:51

vikalp rusia

1

`CREATE TABLE` is a DDL statement, and DDL statements are not transactional. They happen immediately and are not affected by rollbacks. – marstran Nov 24 '20 at 08:54
But in SQL we can make it. by using BEGIN TRANSACTION – vikalp rusia Nov 24 '20 at 09:14
In real project I am using multiple statemnt therefore I cannot make every statement starting with BEGIN as I asked user whether he wants autoCommit or not – vikalp rusia Nov 24 '20 at 09:15
1

You can write `BEGIN TRANSACTION` before a DDL-statement in SQL, but it won't have any effect. – marstran Nov 24 '20 at 09:18
Got your point but is there any way to make DDL in transaction with multiple statements. Should I use savepoint? – vikalp rusia Nov 24 '20 at 09:22
No. There isn't a way to do that. At least, not with MySQL. – Stephen C Nov 24 '20 at 09:55

Why rollback is not working as expected in JDBC?

0 Answers0