MyBatis のアノテーション記法を使った、ネストされたオブジェクトのマッピング

1
2
3
4
5

<select id="findById" resultType="User">
SELECT id, username, password
FROM user
WHERE id = #{id}
</select>

1
2

@Select("SELECT id, username, password FROM user WHERE id = #{id}")
fun findById(@Param("id") long id): User

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE prefecture (
    `prefecture_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `prefecture_name` varchar(100) DEFAULT NULL COMMENT '県名',
    PRIMARY KEY (`prefecture_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '都道府県';

CREATE TABLE person (
    `person_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    `prefecture_id` int UNSIGNED NOT NULL COMMENT '都道府県ID',
    `person_name` varchar(200) DEFAULT NULL COMMENT '人名',
    `person_age` int DEFAULT 0 COMMENT '年齢',
    PRIMARY KEY (`person_id`),
    CONSTRAINT fk_person__prefecture FOREIGN KEY (`prefecture_id`) REFERENCES prefecture (`prefecture_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '人';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

package com.example.booking.model

// 著名な都道府県民
data class PrefectureCelebrities(
    var prefectureId: Int? = null,
    var personList: List<Person> = mutableListOf()
)

// 人
data class Person(
    var name: String? = null,
    var age: Int? = null
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

package com.example.booking.mapper

import com.example.booking.model.PrefectureCelebrities
import com.example.booking.model.Person
import org.apache.ibatis.annotations.*

@Mapper
interface PersonMapper {

    // XMLファイル・アノテーション併用のMapper
    @Select(
        """
            <script>
                SELECT
                    prefecture_id,
                    person_name,
                    person_age
                FROM
                    person
                WHERE
                    person_id IN
                    <foreach item="id" collection="idList" separator="," open="(" close=")">
                        #{id}
                    </foreach>
                ORDER BY prefecture_id, person_id
            </script>               
        """
    )
    @ResultMap("com.example.booking.mapper.PersonMapper.prefectureCelebrities")
    fun selectPrefectureCelebritiesWithXml(
        @Param("idList") idList: List<Int>
    ): List<PrefectureCelebrities>

    // アノテーションだけで実装したMapper
    @Select(
        """
            <script>
                SELECT
                    prefecture_id
                FROM
                    person
                WHERE
                    person_id IN
                    <foreach item="id" collection="idList" separator="," open="(" close=")">
                        #{id}
                    </foreach>
                GROUP BY prefecture_id
                ORDER BY prefecture_id
            </script>
        """
    )
    @Results(
        Result(
            property="prefectureId",
            column = "prefecture_id"
        ),
        Result(
            property="personList",
            column="prefecture_id",
            javaType= List::class,
            many=Many(select="selectPerson")
        )
    )
    fun selectPrefectureCelebritiesWithMany(
        @Param("idList") idList: List<Int>
    ): List<PrefectureCelebrities>

    // @Manyで必要になるSELECT文
    @Select(
        """
            SELECT
                person_name,
                person_age
            FROM
               person
            WHERE
                prefecture_id = #{prefectureId}
            ORDER BY person_id
        """
    )
    @Results(
        Result(
            property = "name",
            column = "person_name"),
        Result(
            property = "age",
            column = "person_age"
        )
    )
    fun selectPerson(
        @Param("prefectureId") prefectureId: Long
    ): Person
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

<?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.booking.mapper.PersonMapper">
    <resultMap id="prefectureCelebrities" type="com.example.booking.model.PrefectureCelebrities">
        <id property="prefectureId" column="prefecture_id" />
        <collection property="personList" ofType="com.example.booking.model.Person">
            <result property="name" column="person_name" />
            <result property="age" column="person_age" />
        </collection>
    </resultMap>
</mapper>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

-- 都道府県
INSERT INTO prefecture
SET prefecture_id = 1,
prefecture_name = '兵庫県';

INSERT INTO prefecture
SET prefecture_id = 2,
prefecture_name = '大阪府';

-- 著名人
INSERT INTO person
SET person_id = 1,
prefecture_id = 1,
person_name = '兵庫太郎',
person_age = 58 ;

INSERT INTO person
SET person_id = 2,
prefecture_id = 1,
person_name = '兵庫次郎',
person_age = 58;

INSERT INTO person
SET person_id = 3,
prefecture_id = 1,
person_name = '兵庫三郎',
person_age = 54;

INSERT INTO person
SET person_id = 4,
prefecture_id = 2,
person_name = '大阪四郎',
person_age = 50;

INSERT INTO person
SET person_id = 5,
prefecture_id = 2,
person_name = '大阪五郎',
person_age = 50;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

package com.example.booking.mapper

import com.example.booking.config.MyBatisTestConfig
import org.assertj.core.api.BDDAssertions
import org.assertj.core.api.junit.jupiter.SoftAssertionsExtension
import org.junit.jupiter.api.Test
import org.junit.jupiter.api.extension.ExtendWith
import org.mybatis.spring.annotation.MapperScan
import org.mybatis.spring.boot.test.autoconfigure.MybatisTest
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase
import org.springframework.context.annotation.Import
import org.springframework.test.context.ContextConfiguration
import org.springframework.test.context.jdbc.Sql
import kotlin.test.assertNotNull

@MybatisTest
@MapperScan
@ContextConfiguration(classes = [MyBatisTestConfig::class])
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ExtendWith(SoftAssertionsExtension::class)
@Import(MyBatisTestConfig::class)
class PersonMapperTest {

    @Autowired
    lateinit var personMapper: PersonMapper

    @Test
    @Sql(scripts = ["classpath:/sql/prefecture_celebrities.sql"])
    fun selectPrefectureCelebritiesWithXml() {
        val idList = listOf(1, 2, 3, 4, 5)
        val result = personMapper.selectPrefectureCelebritiesWithXml(idList)
        assertNotNull(result)
        // XMLで記述したresultMapによって、prefectureIdで集約される
        BDDAssertions.then(result).hasSize(2)
        BDDAssertions.then(result[0].personList).hasSize(3)
        BDDAssertions.then(result[1].personList).hasSize(2)
    }

    @Test
    @Sql(scripts = ["classpath:/sql/prefecture_celebrities.sql"])
    fun selectPrefectureCelebritiesWithMany() {
        val idList = listOf(1, 2, 3, 4, 5)
        val result = personMapper.selectPrefectureCelebritiesWithMany(idList)
        assertNotNull(result)
        // GROUP BY を使うことで prefectureId で集約できる
        BDDAssertions.then(result).hasSize(2)
        BDDAssertions.then(result[0].personList).hasSize(3)
        BDDAssertions.then(result[1].personList).hasSize(2)
    }
}