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!

Recommended Posts

I was addicted to looping the Update statement on MyBatis
I was addicted to the roll method
I was addicted to the Spring-Batch test
I was addicted to using RXTX on Sierra
I was addicted to installing Ruby/Tk on MacOS
I was addicted to the NoSuchMethodError in Cloud Endpoints
I was addicted to the record of the associated model
I was addicted to starting sbt
What I was addicted to while using rspec on rails
I was addicted to the setting of laradock + VSCode + xdebug
What I was addicted to with the Redmine REST API
A story I was addicted to when getting a key that was automatically tried on MyBatis
The story I was addicted to when setting up STS
A note when I was addicted to converting Ubuntu on WSL1 to WSL2
About the matter that I was addicted to how to use hashmap
I was addicted to the API version min23 setting of registerTorchCallback
I was addicted to rewriting to @SpringApplicationConfiguration-> @SpringBootTest
Memorandum: What I was addicted to when I hit the accounting freee API
A story I was addicted to when testing the API using MockMVC
Recorded because I was addicted to the standard input of the Scanner class
I was addicted to scrollview because I couldn't tap the variable size UIView
[CircleCI] I was addicted to the automatic test of CircleCI (rails + mysql) [Memo]
I was addicted to unit testing with the buffer operator in RxJava
A story that I was addicted to twice with the automatic startup setting of Tomcat 8 on CentOS 8
I was addicted to doing onActivityResult () with DialogFragment
I was addicted to not being able to connect to AWS-S3 from the Docker container
I was a little addicted to the S3 Checksum comparison, so I made a note.
Things I tend to forget on MyBatis Personal summary
I want to simplify the log output on Android
The CSV file that I was able to download suddenly started to appear on the page.
The part I was addicted to in "Introduction to Ajax in Java Web Applications" of NetBeans
A memorandum because I was addicted to the setting of the Android project of IntelliJ IDEA
What I fixed when updating to Spring Boot 1.5.12 ・ What I was addicted to
I want to use screen sharing on the login screen on Ubuntu 18
I was addicted to setting default_url_options with Rails devise introduction
A story I was addicted to in Rails validation settings
I want to put the JDK on my Mac PC
I was addicted to using Java's Stream API in Scala
I want to simplify the conditional if-else statement in Java
Technical causes and countermeasures for the points I was addicted to with the first Android app & Kotlin
What I was addicted to when updating the PHP version of the development environment (Docker) from 7.2.11 to 7.4.x
[Circle CI] A story I was addicted to at Start Building
I tried to summarize what was asked at the site-java edition-
Special Lecture on Multi-Scale Simulation: I tried to summarize the 5th
What I was addicted to when implementing google authentication with rails
Special Lecture on Multi-Scale Simulation: I tried to summarize the 8th
I want to add the disabled option to f.radio_button depending on the condition
I tried to display the calendar on the Eclipse console using Java.
Special Lecture on Multi-Scale Simulation: I tried to summarize the 7th
I was able to deploy the Docker + laravel + MySQL app to Heroku!
[Java] How to update Java on Windows
I tried to explain the method
The story I wanted to unzip
Android memo-I was addicted to Permission
By checking the operation of Java on linux, I was able to understand compilation and hierarchical understanding.
My.cnf configuration problem that I was addicted to when I was touching MySQL 8.0 like 5.7
I was addicted to a simple test of Jedis (Java-> Redis library)
[Ruby] I tried to diet the if statement code with the ternary operator
I was a little addicted to running old Ruby environment and old Rails
[Active Admin] I want to customize the default create and update processing
It was a life I wanted to reset the thread-safe associative counter