Shulou(Shulou.com)06/01 Report--

This article is about how mybatis modifies the data in batches. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

There are two main ways to modify in batches: the first one

You can modify the data one by one through the for loop, which affects efficiency, so I don't recommend it, so I won't say much about it here.

The second kind

Modify the mapper.xml file in mybatis as follows:

Update TB_ROLE_MENU FID=# {item.fid} where ROLEID = # {item.roleid} mysql and mybatis batch update data updatemysql batch update update

Using the case when statement, the data table is as follows:

Case1:

Where age is a non-empty field. Sql is as follows

Update test1set age=casewhen id=2 then 1when id= 3 then 2endwhere id in (2pm 3pm 4)

Set the age field for id to 2 and id to 2, and set to 2 for 1 to 1, and the result is as follows:

Column 'age' cannot be null.

The reason is that because id=4 is not matched by the above when, it will be set to empty null by default. That is, records that are not matched will not keep the original value, but will be set to null.

Case2:

If you want to set the default value, you can write:

Update test1set age=casewhen id=2 then 1when id= 3 then 2else 30endwhere id in (2pm 3pm 4)

As a result,

It can be seen that the setting of id=4 is 30, that is, through else default_value, you can set the default value for rows that are not matched.

Case3:

If you want to set an unmatched row to an unoriginal value, you can write:

Update test1set age=casewhen id=2 then 1when id= 3 then 2when id= 4 then test1.ageendwhere id in (2m 3pm 4)

This allows you to keep the rows of id=4 at their original values through test1.age. You can see the usefulness of this writing in mybatis.

Batch updating update with mybatis

Corresponding to the various case above, write xml.

Usually, when writing code, sometimes fields that are not updated will not be set to the object. For example, the Person object has three id,name,age attributes corresponding to three fields in the database. If you do not want to update the age of id=4, you usually do not set the value of age, that is, age=null, so that in case1, it will be mistakenly set to null. For more information, please see the following code.

Case1:

Update test1 when id=# {item.id} then # {item.age} when id=# {item.id} then # {item.name} Where id in # {item.id}

When there are three person objects in the incoming list, corresponding to id=2,3,4, if you do not want to update the age of 4, but only want to update the name of 4, the person object age of id=4 will be set to null, so that when the sql is passed, the data of id=4 will not be matched by when, and the default value will not be set, then the original data will be deleted and set to null.

Case2:

Update test1 when id=# {item.id} then # {item.age} else 30 when id=# {item.id } then # {item.name} where id in # {item.id}

This code sets else 30, so the data of id=4 will be updated to 30 without setting age.

Case3:

Update test1 when id=# {item.id} then # {item.age} when id=# {item.id} then test1.age When id=# {item.id} then # {item.name} where id in # {item.id}

Through the if tag, if the attribute passed in is null, the original value of the database is maintained.

Add:

Update multiple pieces of data with the same field to the same value:

Thank you for your reading, UPDATE test1 SET age=24 WHERE id in (2 and 3). This is the end of this article on "how to modify data in batches with mybatis". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.