MySQL上でMyBatisを利用して複数レコードをまとめて追加／更新してみた

Spring BootとMyBatisを利用して、MySQLに接続するアプリケーション上では、引数に追加・更新対象データのリストを渡し、XMLファイル内のSQL文でforeach構文による繰り返し処理を行うことで、複数のレコードをまとめて追加・更新することができる。

今回は、Spring Bootアプリケーション内でMyBatisフレームワークを利用する状態で、MySQLに接続し、複数のレコードをまとめて追加・更新するサンプルプログラムを作成してみたので、共有する。

前提条件

下記記事の実装が完了していること。

Oracle上でMyBatisを利用して複数レコードをまとめて追加／更新してみたSpring BootとMyBatisを利用して、Oracleに接続するアプリケーション上では、引数に追加・更新対象データのリストを渡し...

また、下記記事の前提条件を満たしていること。

Spring BootでMySQLに接続しMyBatisを利用してみたこれまでは、Spring Bootアプリケーションで接続するデータベースはOracleでサンプルプログラムを作成してきたが、今回はMyS...

サンプルプログラムの作成

作成したサンプルプログラムの構成は以下の通り。

なお、上記の赤枠は、前提条件のプログラムから変更したプログラムである。

build.gradleの内容は以下の通りで、MySQLに接続するためのJDBCライブラリを追加している。

plugins {
    id 'org.springframework.boot' version '2.1.7.RELEASE'
    id 'java'
}

apply plugin: 'io.spring.dependency-management'

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    compileOnly 'org.projectlombok:lombok:1.18.10'
    annotationProcessor 'org.projectlombok:lombok:1.18.10'
    // MySQLに接続するための設定
    compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.21'
    // MyBatisを利用するための設定
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.1'
}

plugins {

id 'org.springframework.boot' version '2.1.7.RELEASE'

id 'java'

}

apply plugin: 'io.spring.dependency-management'

group = 'com.example'

version = '0.0.1-SNAPSHOT'

sourceCompatibility = '1.8'

repositories {

mavenCentral()

}

dependencies {

implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'

implementation 'org.springframework.boot:spring-boot-starter-web'

testImplementation 'org.springframework.boot:spring-boot-starter-test'

compileOnly 'org.projectlombok:lombok:1.18.10'

annotationProcessor 'org.projectlombok:lombok:1.18.10'

// MySQLに接続するための設定

compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.21'

// MyBatisを利用するための設定

implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.1'

}

application.propertiesの内容は以下の通りで、DB接続先をMySQLに変更している。

server.port = 8084
# DB接続先
spring.datasource.url=jdbc:mysql://localhost:3306/user01?serverTimezone=JST
spring.datasource.username=USER01
spring.datasource.password=USER01
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

server.port = 8084

# DB接続先

spring.datasource.url=jdbc:mysql://localhost:3306/user01?serverTimezone=JST

spring.datasource.username=USER01

spring.datasource.password=USER01

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

UserDataMapper.xmlの内容は以下の通りで、複数のレコードをまとめてUSER_DATAテーブルに追加／更新するSQLを、MySQL用に変更している。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.UserDataMapper">
    <resultMap id="userDataResultMap" type="com.example.demo.UserData" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="birthY" property="birthY" jdbcType="VARCHAR" />
        <result column="birthM" property="birthM" jdbcType="VARCHAR" />
        <result column="birthD" property="birthD" jdbcType="VARCHAR" />
        <result column="sex" property="sex" jdbcType="VARCHAR" />
        <result column="memo" property="memo" jdbcType="VARCHAR" />
        <result column="sex_value" property="sex_value" jdbcType="VARCHAR" />
    </resultMap>
    <insert id="insertMulti" parameterType="java.util.List">
        INSERT INTO USER_DATA (
              id
            , name
            , birth_year
            , birth_month
            , birth_day
            , sex
            , memo
        ) VALUES
        <foreach collection="userDataList" item="userData" separator=",">
            (
                  #{userData.id}
                , #{userData.name}
                , #{userData.birthY}
                , #{userData.birthM}
                , #{userData.birthD}
                , #{userData.sex}
                , #{userData.memo,jdbcType=VARCHAR}
            )
        </foreach>
    </insert>
    <update id="updateMulti" parameterType="java.util.List">
        UPDATE USER_DATA
        SET name = ELT(
                FIELD(id,
                    <foreach collection="userDataList" item="userData" separator=",">
                        #{userData.id}
                    </foreach>
                ),
                <foreach collection="userDataList" item="userData" separator=",">
                    #{userData.name}
                </foreach>
            )
        , birth_year = ELT(
                FIELD(id,
                    <foreach collection="userDataList" item="userData" separator=",">
                        #{userData.id}
                    </foreach>
                ),
                <foreach collection="userDataList" item="userData" separator=",">
                    #{userData.birthY}
                </foreach>
            )
        , birth_month = ELT(
                FIELD(id,
                    <foreach collection="userDataList" item="userData" separator=",">
                        #{userData.id}
                    </foreach>
                ),
                <foreach collection="userDataList" item="userData" separator=",">
                    #{userData.birthM}
                </foreach>
            )
        , birth_day = ELT(
                FIELD(id,
                    <foreach collection="userDataList" item="userData" separator=",">
                        #{userData.id}
                    </foreach>
                ),
                <foreach collection="userDataList" item="userData" separator=",">
                     #{userData.birthD}
                </foreach>
            )
        , sex = ELT(
                FIELD(id,
                    <foreach collection="userDataList" item="userData" separator=",">
                         #{userData.id}
                    </foreach>
                ),
                <foreach collection="userDataList" item="userData" separator=",">
                     #{userData.sex}
                </foreach>
            )
        , memo = ELT(
                FIELD(id,
                    <foreach collection="userDataList" item="userData" separator=",">
                         #{userData.id}
                    </foreach>
                ),
                <foreach collection="userDataList" item="userData" separator=",">
                    #{userData.memo,jdbcType=VARCHAR}
                </foreach>
             )
        WHERE id IN (
            <foreach collection="userDataList" item="userData" separator=",">
                #{userData.id}
            </foreach>
        );
    </update>
    <select id="findAll" resultMap="userDataResultMap">
        SELECT
              id
            , name
            , birth_year as birthY
            , birth_month as birthM
            , birth_day as birthD
            , sex
            , memo
            , CASE sex
              WHEN '1' THEN '男'
              WHEN '2' THEN '女'
              ELSE ''
              END AS sex_value
        FROM USER_DATA
    </select>
</mapper>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

</resultMap>

INSERT INTO USER_DATA (

, name

, birth_year

, birth_month

, birth_day

, sex

, memo

) VALUES

(

#{userData.id}

, #{userData.name}

, #{userData.birthY}

, #{userData.birthM}

, #{userData.birthD}

, #{userData.sex}

, #{userData.memo,jdbcType=VARCHAR}

)

</foreach>

</insert>

UPDATE USER_DATA

SET name = ELT(

FIELD(id,

#{userData.id}

</foreach>

#{userData.name}

</foreach>

)

, birth_year = ELT(

FIELD(id,

#{userData.id}

</foreach>

#{userData.birthY}

</foreach>

)

, birth_month = ELT(

FIELD(id,

#{userData.id}

</foreach>

#{userData.birthM}

</foreach>

)

, birth_day = ELT(

FIELD(id,

#{userData.id}

</foreach>

#{userData.birthD}

</foreach>

)

, sex = ELT(

FIELD(id,

#{userData.id}

</foreach>

#{userData.sex}

</foreach>

)

, memo = ELT(

FIELD(id,

#{userData.id}

</foreach>

#{userData.memo,jdbcType=VARCHAR}

</foreach>

)

WHERE id IN (

#{userData.id}

</foreach>

);

</update>

SELECT

, name

, birth_year as birthY

, birth_month as birthM

, birth_day as birthD

, sex

, memo

, CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE ''

END AS sex_value

FROM USER_DATA

</select>

</mapper>

なお更新時のSQL文は、以下のサイトのELTとFIELDのキーワードを利用する方式になっている。
https://qiita.com/yokozawa/items/13f837a27afc813eac32

その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-mysql-multi-insert-update/demo

https://www.purin-it.com/doctor-homenet

サンプルプログラムの実行結果

サンプルプログラムの実行結果は以下の通りで、複数のレコードをまとめて追加・更新する処理が実行できることが確認できる。

1) 以下のように、DemoApplicationクラスで、複数のレコードをまとめて追加する処理(demoServiceクラスのinsertMultiDataメソッド)を有効にする。

2) 更新対象のUSER_DATAテーブルを、以下のように、全件削除した状態にする。

select * from user_data order by id asc

1	select * from user_data order by id asc

3) 1)2)の状態でSpring Bootのメインクラス(DemoApplication.java)を実行した結果、コンソールログに出力される内容は以下の通り。

4) 3)を実行後のUSER_DATAテーブルの中身は以下の通りで、複数のレコードをまとめて追加できたことが確認できる。

select * from user_data order by id asc

1	select * from user_data order by id asc

5) 以下のように、DemoApplicationクラスで、複数のレコードをまとめて更新する処理(demoServiceクラスのupdateMultiDataメソッド)を有効にする。

6) 4)5)の状態でSpring Bootのメインクラス(DemoApplication.java)を実行した結果、コンソールログに出力される内容は以下の通り。

7) 6)を実行後のUSER_DATAテーブルの中身は以下の通りで、複数のレコードのNAME, MEMOの値をまとめて更新できたことが確認できる。

select * from user_data order by id asc

1	select * from user_data order by id asc

要点まとめ

Spring BootとMyBatisを利用して、MySQLに接続するアプリケーション上では、引数に追加・更新対象データのリストを渡し、XMLファイル内のSQL文でforeach構文による繰り返し処理を行うことで、複数のレコードをまとめて追加・更新することができる。
MySQLの場合の複数レコードの更新する際は、ELTとFIELDのキーワードを利用する。