Background description: Usually, if you need to update multiple data at a time, there are two ways to update it one by one. (1) Looping through the business code to update one by one. (2) Update all data at once (to be more precise, it is an SQL statement to update all data, put the update operations one by one on the database side, and the business code side is to update all data at once). The two methods have their pros and cons. The following will briefly analyze the pros and cons of the two methods, and mainly introduce the implementation of the second method in mybatis.
Update one by one
This method is obviously the simplest and least prone to errors. Even if an error occurs, it only affects the error data. It can be controlled by each piece of data. If the update fails or succeeds, it can be obtained from what content is updated to what content. The code might look like this:
updateBatch(List<MyData> datas){ for(MyData data: datas){ try{ myDataDao.update(data);//Update a data, update of the xml file below in mybatis } catch(Exception e){ ...//If the update fails, you can do some other operations, such as printing an error log, etc.} }}//Implementation of update operation in mybatis <update> update mydata set ... where ...</update>The biggest problem with this method is the efficiency problem. It updates one by one, and connects to the database each time, then updates, and then releases the connection resources (although the efficiency of frequently connected data can be greatly improved through the connection pool, which cannot withstand the large amount of data). This loss will reflect the efficiency problem when the amount of data is large. This is also when meeting business needs, the second batch update implementation mentioned above is usually used (of course, this method also has data scale limitations, which will be mentioned later).
SQL batch update
A SQL statement is used to update all data in batches. Let’s take a look at how it is usually written in mybatis (removing the mybatis syntax is the native SQL statement, so I don’t talk about how it is written in SQL).
<update id="updateBatch" parameterType="java.util.List"> update mydata_table set status= <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end"> when #{item.id} then #{item.status} </foreach> where id in <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.id,jdbcType=BIGINT} </foreach> </update>where when...then... is the "switch" syntax in sql. Here, the <foreach> syntax of mybatis is used to piece together the batch update SQL. The above means that the status field of the data passed in the List of id in the updateBatch parameter is batch updated. You can also use <trim> to implement the same function, the code is as follows:
<update id="updateBatch" parameterType="java.util.List"> update mydata_table <trim prefix="set" suffixOverrides=","> <trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> when id=#{item.id} then #{item.status} </foreach> </trim> </trim> where id in <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.id,jdbcType=BIGINT} </foreach> </update><trim>Property Description
1.prefix,suffix means adding content in front or after the part wrapped in the trim tag
2. If there are prefixOverrides at the same time, suffixOverrides means that the content in Overrides will be overwritten with prefix and suffix.
3. If only prefixOverrides, suffixOverrides means to delete the content specified by xxxOverides at the beginning or end.
The above code is converted into SQL as follows:
update mydata_table set status = case when id = #{item.id} then #{item.status}//This should be the <foreach> expand value... end where id in (...);Of course, this is the easiest batch update implementation. Sometimes multiple fields may need to be updated, so you need to
<trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> when id=#{item.id} then #{item.status} </foreach></trim>Copy multiple times, change the contents of prefix and when...then... and if you need to set the default value for a certain field, you can use else
<trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> when id=#{item.id} then #{item.status} </foreach> else default_value</trim>There is also a more common situation where the data to be updated needs to be judged, and only data that meets the conditions can be updated. This situation can be done:
<trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> <if test="item.status !=null and item.status != -1"> when id=#{item.id} then #{item.status} </if> </foreach></trim> In this case, only status != null && status != -1 data in the list to be updated can be updated. Others will be updated with default values, without keeping the original data unchanged. What if you want to keep the original data unchanged? That is, updates that meet the conditions, and those that do not meet the conditions remain unchanged. A simple way to do it is to add another <if>, because there is no if...else... syntax in mybatis, but the same effect can be achieved through multiple <if>, as follows:
<trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> <if test="item.status !=null and item.status != -1"> when id=#{item.id} then #{item.status} </if> <if test="item.status == null or item.status == -1"> when id=#{item.id} then mydata_table.status //This is the original data</if> </foreach></trim>The overall batch update is written as follows:
<update id="updateBatch" parameterType="java.util.List"> update mydata_table <trim prefix="set" suffixOverrides=","> <trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> <if test="item.status !=null and item.status != -1"> when id=#{item.id} then #{item.status} </if> <if test="item.status == null or item.status == -1"> when id=#{item.id} then mydata_table.status//original data</if> </foreach> </trim> </trim> where id in <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.id,jdbcType=BIGINT} </foreach> </update>This batch and core database method can update all data in a database connection, avoiding the overhead of frequent database establishment and disconnection, and greatly improving data update efficiency. However, the problem is that if an update error occurs during this process, it will be difficult to know which data is wrong. If the data's own transaction guarantee is used, then once an error occurs, all updates will be automatically rolled back. And this method is often more prone to errors. Therefore, the usual solution is to make compromises, that is, to update part of it in batches (pagination is done, for example, there are a total of 1,000 pieces of data, and 100 pieces of data are updated at a time). This can share the probability of errors and it is easier to locate the error.
Of course, if the amount of data is really large, this batch update will also lead to inefficient updates (for example, if 100 items are updated at a time, then if 1 billion data is updated, 10 million times will be required to batch update 10 million times, and 10 million databases will be established and disconnected, which is unbearable). At this time, you may only be able to consider other solutions, such as introducing a caching mechanism.
Summarize
The above is the batch updateBatch introduced by the editor to you by using updateBatch in Mybatis. 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!