The relationship between autocommit in Mysql and AutoCommit in JDBC

user_15644705 0
Viewed 31

My MySQL's storage engine is InnoDB. when I set the MySQL system variables autocommit=0, I cannot see any changes in the database after executing Java code like this:

 String sql = "insert into user (userName, userAge, password) " +
                    "values (?, ?, ?);";
 PreparedStatement preparedStatement = conn.prepareStatement(sql);
 preparedStatement.setString(1, "hh");
 preparedStatement.setInt(2, 33);
 preparedStatement.setString(3, "agadf");
 int i = preparedStatement.executeUpdate();

 conn.close();

or use commit implicitly:

 conn.setAutoCommit(false);

 String sql = "insert into user (userName, userAge, password) " +
                    "values (?, ?, ?);";
 PreparedStatement preparedStatement = conn.prepareStatement(sql);
 preparedStatement.setString(1, "hh");
 preparedStatement.setInt(2, 33);
 preparedStatement.setString(3, "agadf");
 int i = preparedStatement.executeUpdate();
 conn.commit();
 conn.setAutoCommit(true);

After this executed, I cannot see any changes when I execute "select * from user;". But after I type commit at MySQL shell, the insert operation works.

So, I really want to know the real function of the system variables autocommit. If I set it to 0, it is no way to use JDBC to modify data without executing commit in MySQL shell?

At the same time, I want to know why setAutomatic(true) or conn.commit() can not to commit the changes to the real table when the system variable autocommit is 0?

jdbc mysql
1 Answers

You're approaching this from the wrong end, the problem is not in JDBC, but in your MySQL shell. MySQL shell also uses a transaction (all operations in a database happen in a transaction), and with your MySQL configured with autocommit=0, then, if you don't commit in the shell, the shell will use its current transaction which cannot see the changes you made, because that transaction started before you made your changes. Committing in MySQL shell causes the shell to start a new transaction before your select, so it can see the changes you made in your Java application.

user_4668620
Related