MyBatis execute multiple SQL statements at once

MyBatis execute multiple SQL statements at once

There is a common scenario: delete users need to first delete the user's foreign key association data, otherwise it will trigger the rule error.

There are three solutions: 1, multiple SQL batch execution, 2, stored procedure or function call, 3, SQL batch execution.

Today I'm going to talk about how to execute multiple statements at once (using MySQL database) in MyBatis.

1, modify the database connection parameters plus allowmultiqueries=true, such as:

Hikariconfig.security.jdbcurl=jdbc:mysql://xx.xx.xx:3306/xxxxx?characterencoding=utf-8&autoreconnect=true &failoverreadonly=false&allowmultiqueries=true

2, write more than one statement, using ";" to separate

<delete id= "Deleteuserbyid" parametertype= "String" >
    Delete from sec_user_role where userid=#{id};
    Delete from Sec_user where id=#{id};
</delete>

MyBatis execute multiple SQL statements at once