I was addicted to looping the Update statement on MyBatis

Introduction

The author is still a beginner engineer who joined the company in 2018.

So the level of the article is not very high,

I want to leave the points I was addicted to when writing code in business for future juniors! !!

→ If you want to leave it anyway, I want to write it in Qiita! !! I thought and posted it.

The background I was addicted to this time

This is a story when I was doing backend development with Spring + MyBatis.

N objects sent from the request by the function in charge,

After passing through the logic, I used MyBatis to update multiple items.

Before that, what is MyBatis?

Before I talk about this time, let's talk about MyBatis lightly.

MyBatis is an OR mapper framework that describes SQL in XML.

Even if the developer does not bother to write SQL in the OR mapper Some are guaranteed normal CRUD processing,

MyBatis dares to do complicated joins by handwriting SQL, You can map special SQL such as UPSERT statements to Java objects, If statements, foreach, etc. can be written in the SQL part, and dynamic SQL can be generated.
Example) The INSERT statement can be written as follows.

Java file

public interface UserRepository{
	public int insertUser(List<User> userList);
}

XML file

<insert id="insertUser" parameterType="java.util.List">
   INSERT INTO users( 
   		  user_id
   		, user_name
   		, user_mail
   )
   VALUES
   <foreach item="user" collection="list" open="" separator="," close="">
     (
          #{userId, jdbcType=BIGINT}
        , #{userName, jdbcType=VARCHAR}
        , #{userEmail, jdbcType=VARCHAR}
     )
   </foreach>
 </insert>

In the above case, fill the User object with the value you want to register and By making them into a List and passing them to the OR mapper, multiple INSERTs are possible with one query issuance. This one-time issuance is good, and you can reduce the number of accesses to the DB without degrading performance.

Addicted to Update

Let's turn Update in a loop with the above glue! I'm addicted to it.

Event

Specifically, when writing the following SQL. (Java is omitted)

XML file

<update id ="updateUser" parameterType= "java.util.List" >
     <foreach collection ="itemList" item="item" separator= ";">
          update users
           <set>
                user_name = #{userName, jdbcType=VARCHAR}
              , user_email = #{userEmail, jdbcType=VARCHAR}
           </set >
          WHERE user_id = #{userId, jdbcType=VARCHAR}
     </foreach>
</update >

For example, suppose you want to update "Ah" and "Ii" with the above SQL. The query issued by this is as follows.

SQL issued

update users
  set
    user_name = 'Ah ah',
    user_email = '[email protected]'
where user_id = '001';
update users
  set
    user_name = 'Good',
    user_email = '[email protected]'
where user_id = '002';

At first glance, it looks good, but when you actually try to process it, an error occurs. However, if this issued SQL is sent directly to the table, the process will end normally.

Cause

This makes sense in terms of Mybatis, or Java methods (at least for me).

The behavior of Java is to call a method called updateUser. Issue n queries in it to access the DB.
At this time, the number of cases processed by one query issuance is returned as a return value to the calling class. In the case of the above Update statement, multiple queries are issued with one method call, so An error will occur if you try to return multiple return values.

Why Insert can foreach

This is the case with the Insert statement, as I wrote in the section where I talked about MyBatis. ** Because multiple processes are performed by issuing a single query ** Even if you register two cases with one method call Only the return value becomes "2", and the number of return values is one, so the process ends normally.

How to loop with Update

If the DB can use UPSERT statements such as mySQL, describe DUPLICATE KEY and write it. It can be solved by using UPSERT.

UPSERT processing example

<insert id="upsertUser" parameterType="java.util.List">
    INSERT INTO users　( 
          user_id
        , user_name
        , user_mail
   )
   VALUES
   <foreach item="user" collection="list" open="" separator="," close="">
     (
        #{userId, jdbcType=BIGINT},
        #{userName, jdbcType=VARCHAR},
        #{userEmail, jdbcType=VARCHAR},
     )
   </foreach>
   ON DUPLICATE KEY UPDATE
     user_name = VALUES(userName),
     user_email = VALUES(userEmail)
 </insert>

With the above SQL, an INSERT statement will be issued once even from the object to be updated. For INSERT statements that would result in a unique contract violation at run time The process switches to the Update statement described in ON DUPLICATE KEY UPDATE. Therefore, if n update objects are passed to this process, ** As a result, multiple updates can be made by issuing a single query **, and the process is performed normally.

in conclusion

Thank you for reading to the end. If you find something strange, please point it out. Also, I will continue to post articles, so any advice on writing is welcome!