When operating the database, you often encounter batch insertion and batch deletion. It is easy to do it directly by executing SQL statements. There will be some problems when using Mybatis for batch insertion and batch deletion. The following is an introduction to batch insertion and batch deletion using Mybatis.
1. Batch insertion
Java code:
// Model: Test.java@Datapublic class Test {private String x;private String y;private String z;}// Mapper: TestMapper.javapublic void insertTestList(List<Test> testList);XML code
<!-- XML: TestMapper.XML -->...<!-- Ignore duplicate data--><insert id="insertTestList" parameterType="Test">INSERT IGNORE INTO test_table(test_x, test_y, test_z)VALUES<foreach item="item" index="index" collection="list" open="(" close=")" separator=",">#{item}.x, #{item.y}, #{item}.z</foreach></insert><!-- Update duplicate data--><insert id="insertTestList" parameterType="Test">INSERT INTO test_table(test_x, test_y, test_z)VALUES<foreach item="item" index="index" collection="list" open="(" close=")" separator=",">#{item}.x, #{item.y}, #{item}.z</foreach>ON DUPLICATE KEY UPDATEtest_x = VALUES(test_x),test_y = VALUES(test_y),test_z = VALUES(test_z)</insert>...Batch insert SQL statements
insert into test_table(x, y, z) values (1, 1, 1), (2, 2, 2), (3, 3, 3)
Note: VALUE() is a function of Mysql. For specific explanation, please check the document function_values.
The main function is to get the value to be updated when the data is repeated.
2. Batch deletion
Java code:
// Model: Test.java@Datapublic class Test {private String x;private String y;private String z;}// Mapper: TestMapper.javapublic void deleteTestList(List<Test> testList);XML code
<!-- XML: TestMapper.XML -->...<delete id="deleteTestList" parameterType="Test">DELETE FROM test_tableWHERE<foreach item="item" index="index" collection="list" open="(" close=")" separator="OR">test_x = #{item.x} AND test_y = #{item.y} AND test_z = #{item.z}</foreach></delete>...SQL statements
delete from test_table where (test_x = 1 AND test_y = 1 AND test_z = 1) or (test_x = 2 AND test_y = 2 AND test_z = 2) or (test_x = 3 AND test_y = 3 AND test_z = 3)
Note: The above code is the case where x, y, and z are the joint primary keys, and the general situation uses where id in.
The above is the Mybatis insertion and deletion batch operation introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to everyone in time. Thank you very much for your support to Wulin.com website!