Mybatis [2.3] – must mybatis use commit to successfully modify data?

Time:2021-5-3

The code is placed directly in GitHub warehouse【 https://github.com/Damaer/Myb… 】Mybatis-02 can be run directly, so it doesn’t take up space.

Why do we sometimes modify the database successfully without using commit?

[TOC]

1. From the database level, in fact, it mainly depends on what “storage engine” you use

For example, the following code uses the MySQL engine of automatic submission.

CREATE TABLE `student` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(20) NOT NULL , 
`age` INT NOT NULL , `score` DOUBLE NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM; 

If it’s an engine that doesn’t support transactions, such as MyISAM, it doesn’t work whether to commit or not.
If it is a transaction engine, such as InnoDB, there are system parameters to set whether to automatically commit. Check the parameters as follows:

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql>

If the result is on, the transaction will be submitted automatically. You don’t need to commit manually. Of course, you can set it to off and commit manually.

2. Using MyISAM engine, no commit is needed

For example, the following code:

public class StudentDaoImpl implements IStudentDao {
    private SqlSession sqlSession;
    public void insertStu(Student student) {
        try {
            InputStream inputStream;
            inputStream = Resources.getResourceAsStream("mybatis.xml");
            SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
            sqlSession=sqlSessionFactory.openSession();
            sqlSession.insert("insertStudent",student);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

We can see that the line has been updated and we don’t use commit at all
Mybatis [2.3] - must mybatis use commit to successfully modify data?

3. Create a data table (using InnoDB) and do not submit it. The result is not inserted

CREATE TABLE `student` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(20) NOT NULL , 
`age` INT NOT NULL , `score` DOUBLE NOT NULL , PRIMARY KEY (`id`)) ENGINE = Innodb; 

When we perform the insertion again, we find that the console output is as follows:

Mybatis [2.3] - must mybatis use commit to successfully modify data?

It seems that the input was successful, but I went to the database and found that it was empty

Mybatis [2.3] - must mybatis use commit to successfully modify data?

Let’s change the code to this, add the commit transaction:

public class StudentDaoImpl implements IStudentDao {
    private SqlSession sqlSession;
    public void insertStu(Student student) {
        try {
            InputStream inputStream;
            inputStream = Resources.getResourceAsStream("mybatis.xml");
            SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
            sqlSession=sqlSessionFactory.openSession();
            sqlSession.insert("insertStudent",student);
            //Commit transaction
            sqlSession.commit();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }
}

After executing the code, we will find that the transaction has been submitted successfully. At the same time, we will close the database

Mybatis [2.3] - must mybatis use commit to successfully modify data?

Open the database, we will find that there is no record with ID 1, why jump to 2 directly? Do you remember inserting it once before but not submitting it, so that’s the problem. The last commit has been written to the transaction, but it has not been committed. Therefore, the last commit has occupied the record by default, but it will not be written into the database. You can roll back when you submit, so if you want to use roll back, you can usesqlsession.rollback()

Mybatis [2.3] - must mybatis use commit to successfully modify data?

If we use sqlsession. Close(), we don’t need to use rollback.
The following is the result of removing commit but leaving close. We can see that there is no commit, but it will automatically roll back, so just usesqlsession.close()It will roll back and close automatically.

Mybatis [2.3] - must mybatis use commit to successfully modify data?

[about the author]
Qin Huai, official account.Qinhuai grocery store】The author points out that the road of technology is not for a moment, even though it is slow and continuous. The world hopes that everything will be fast and faster, but I hope I can take every step and write every article well. I look forward to communicating with you.

This article is only on behalf of their own (this rookie) learning accumulation records, or learning notes, if there is infringement, please contact the author to verify the deletion. No one is perfect, so is the article. The style of writing is immature. If you don’t have talent, don’t spray. If you have any mistakes, I hope you can point them out. Thank you very much~