MyBatis核心功能二

本文最后更新于：3 年前

引言

MyBatis作为一个强大的持久层框架，提供了丰富的动态SQL功能，可以极大地提升SQL语句的灵活性和代码的可维护性。本文详细介绍了如何使用MyBatis提供的动态SQL标签来构建条件丰富且灵活的SQL查询，以及如何通过高级映射技术处理复杂的数据结构关系，使开发者能够更有效地管理和操作数据库。

动态SQL

MyBatis允许在运行时根据一系列XML标签以及传入的参数值构建灵活的 SQL 语句，使得 SQL 语句的编写更加灵活和强大。

定义获取参数方法如下

public Map<String, Object> getParams() {
    return new HashMap<String, Object>() {{
        put("id", 1L);
        put("name", "zhangsan");
    }};
}

if

<if>标签允许根据提供的表达式的结果（true or false）来决定是否包含一段特定的SQL。这样可以在执行SQL之前根据业务逻辑的需要动态构建SQL语句。

标签中test属性接受一个返回布尔值的表达式。MyBatis会评估这个表达式，并根据其返回结果决定是否包含 <if> 标签内的SQL片段。

在Mapper中定义SQL如下

<select id="testIf" resultType="space.yangtao.domain.po.StudentPO">
    select * from tt_student where
    <if test="param.id != null and param.id != ''">
        and id = #{param.id}
    </if>
</select>
<select id="testIf2" resultType="space.yangtao.domain.po.StudentPO">
    select * from tt_student where
    <if test="id != null and id != ''">
        and id = #{id}
    </if>
</select>

定义对应接口方法

1
2
3

StudentPO testIf(@Param("param") Map<String, Object> params);

StudentPO testIf2(StudentPO studentPO);

运行测试程序

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("if标签测试：{}", studentMapper.testIf(getParams()));
log.info("if标签测试2：{}", studentMapper.testIf2(new StudentPO().setId(1L)));

控制台输出日志如下

...
23:04:42.881 [main] DEBUG space.yangtao.mapper.StudentMapper.testIf - ==>  Preparing: select * from tt_student where id = ?
23:04:42.898 [main] DEBUG space.yangtao.mapper.StudentMapper.testIf - ==> Parameters: 1(Long)
23:04:42.933 [main] TRACE space.yangtao.mapper.StudentMapper.testIf - <==    Columns: id, class_id, name, height, gender, birthday, create_time
23:04:42.934 [main] TRACE space.yangtao.mapper.StudentMapper.testIf - <==        Row: 1, 1, zhangsan, 176.1, 男, 1990-01-01, 2022-07-01 22:10:22
23:04:42.937 [main] DEBUG space.yangtao.mapper.StudentMapper.testIf - <==      Total: 1
23:04:42.938 [main] INFO  space.yangtao.client.StudentMapperTest - if标签测试：StudentPO(id=1, name=zhangsan, height=176.1, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022)
23:04:42.939 [main] DEBUG space.yangtao.mapper.StudentMapper - Cache Hit Ratio [space.yangtao.mapper.StudentMapper]: 0.0
23:04:42.940 [main] DEBUG space.yangtao.mapper.StudentMapper.testIf2 - ==>  Preparing: select * from tt_student where id = ?
23:04:42.940 [main] DEBUG space.yangtao.mapper.StudentMapper.testIf2 - ==> Parameters: 1(Long)
23:04:42.941 [main] TRACE space.yangtao.mapper.StudentMapper.testIf2 - <==    Columns: id, class_id, name, height, gender, birthday, create_time
23:04:42.941 [main] TRACE space.yangtao.mapper.StudentMapper.testIf2 - <==        Row: 1, 1, zhangsan, 176.1, 男, 1990-01-01, 2022-07-01 22:10:22
23:04:42.942 [main] DEBUG space.yangtao.mapper.StudentMapper.testIf2 - <==      Total: 1
23:04:42.942 [main] INFO  space.yangtao.client.StudentMapperTest - if标签测试2：StudentPO(id=1, name=zhangsan, height=176.1, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022)
...

由日志可知，当<if>标签中test表达式判断为true时，会自动拼接标签内的SQL语句。

where

在上述<if>标签的例子中，如果test表达式判断为false，则不会拼接SQl语句and ...，但SQL语句中又以where结尾、存在语法错误，如

1
2
3

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("if标签测试：{}", studentMapper.testIf(new HashMap<>()));

参数为空，运行结果如下

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
### The error may exist in space/yangtao/mapper/StudentMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select * from tt_student where
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

MyBatis中提供了一个<where>标签，用于拼接where关键字，同时，这个标签会智能地处理<if>标签语句中的and关键字，当where标签中所有if标签判断都为false时，则不会拼接where，反之则会拼接。

在Mapper中定义SQL如下

<select id="testWhere" resultType="space.yangtao.domain.po.StudentPO">
    select * from tt_student
    <where>
        <if test="id != null and id != ''">
            and id = #{id}
        </if>
    </where>
</select>

对应的接口方法为

1	`List<StudentPO> testWhere(Map<String, Object> params);`

运行测试程序

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("where标签测试1：{}", studentMapper.testWhere(new HashMap<>()));
log.info("where标签测试2：{}", studentMapper.testWhere(null));
log.info("where标签测试3：{}", studentMapper.testWhere(getParams()));

控制台输出日志如下

...
23:12:37.735 [main] DEBUG space.yangtao.mapper.StudentMapper.testWhere - ==>  Preparing: select * from tt_student
23:12:37.753 [main] DEBUG space.yangtao.mapper.StudentMapper.testWhere - ==> Parameters: 
23:12:37.769 [main] TRACE space.yangtao.mapper.StudentMapper.testWhere - <==    Columns: id, class_id, name, height, gender, birthday, create_time
23:12:37.770 [main] TRACE space.yangtao.mapper.StudentMapper.testWhere - <==        Row: 1, 1, zhangsan, 176.1, 男, 1990-01-01, 2022-07-01 22:10:22
23:12:37.772 [main] TRACE space.yangtao.mapper.StudentMapper.testWhere - <==        Row: 2, 2, zhangsan1, 180.0, 男, 1990-01-01, 2022-07-01 21:10:22
23:12:37.773 [main] TRACE space.yangtao.mapper.StudentMapper.testWhere - <==        Row: 3, 2, zhangsan2, 180.0, 男, 1990-01-01, 2022-07-01 21:10:22
23:12:37.773 [main] DEBUG space.yangtao.mapper.StudentMapper.testWhere - <==      Total: 3
23:12:37.774 [main] INFO  space.yangtao.client.StudentMapperTest - where标签测试1：[StudentPO(id=1, name=zhangsan, height=176.1, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022), StudentPO(id=2, name=zhangsan1, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022), StudentPO(id=3, name=zhangsan2, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022)]
23:12:37.775 [main] DEBUG space.yangtao.mapper.StudentMapper - Cache Hit Ratio [space.yangtao.mapper.StudentMapper]: 0.0
23:12:37.775 [main] INFO  space.yangtao.client.StudentMapperTest - where标签测试2：[StudentPO(id=1, name=zhangsan, height=176.1, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022), StudentPO(id=2, name=zhangsan1, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022), StudentPO(id=3, name=zhangsan2, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022)]
23:12:37.776 [main] DEBUG space.yangtao.mapper.StudentMapper - Cache Hit Ratio [space.yangtao.mapper.StudentMapper]: 0.0
23:12:37.776 [main] DEBUG space.yangtao.mapper.StudentMapper.testWhere - ==>  Preparing: select * from tt_student WHERE id = ?
23:12:37.776 [main] DEBUG space.yangtao.mapper.StudentMapper.testWhere - ==> Parameters: 1(Long)
23:12:37.777 [main] TRACE space.yangtao.mapper.StudentMapper.testWhere - <==    Columns: id, class_id, name, height, gender, birthday, create_time
23:12:37.777 [main] TRACE space.yangtao.mapper.StudentMapper.testWhere - <==        Row: 1, 1, zhangsan, 176.1, 男, 1990-01-01, 2022-07-01 22:10:22
23:12:37.778 [main] DEBUG space.yangtao.mapper.StudentMapper.testWhere - <==      Total: 1
23:12:37.778 [main] INFO  space.yangtao.client.StudentMapperTest - where标签测试3：[StudentPO(id=1, name=zhangsan, height=176.1, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022)]
...

由日志可知，<where>标签会智能地处理标签内的SQL语句。

补充：<where>标签还能智能处理<if>标签中的条件连接符and、or。

trim

<trim>标签用于对生成的SQL片段进行更细致的控制。它可以添加或删除特定的前缀、后缀以及前缀或后缀中的某些字符，特别适用于自定义和优化SQL语句的输出。

这个标签含有4个属性：

prefix：在生成的SQL片段前添加指定的前缀。
prefixOverrides：移除生成的SQL片段前多余的指定前缀（如AND、OR）。
suffix：在生成的SQL片段后添加指定的后缀。
suffixOverrides：移除生成的SQL片段后多余的指定后缀。

在Mapper中定义SQL如下

<select id="testTrim" resultType="space.yangtao.domain.po.StudentPO">
    select * from tt_student
    <!-- |表示或 -->
    <trim prefix="" prefixOverrides="where" suffix="" suffixOverrides="and|or">
        <if test="id != null and id != ''">
            id = #{id} and
        </if>
        <if test="other != null and other != ''">
            name = #{other} and
        </if>
    </trim>
</select>

对应的接口方法为

1	`List<StudentPO> testTrim(Map<String, Object> params);`

运行测试程序

1
2
3

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("trim标签测试：{}", studentMapper.testTrim(getParams()));

运行结果如下

...
23:16:21.582 [main] DEBUG space.yangtao.mapper.StudentMapper.testTrim - ==>  Preparing: select * from tt_student where id = ?
23:16:21.601 [main] DEBUG space.yangtao.mapper.StudentMapper.testTrim - ==> Parameters: 1(Long)
23:16:21.615 [main] TRACE space.yangtao.mapper.StudentMapper.testTrim - <==    Columns: id, class_id, name, height, gender, birthday, create_time
23:16:21.617 [main] TRACE space.yangtao.mapper.StudentMapper.testTrim - <==        Row: 1, 1, zhangsan, 176.1, 男, 1990-01-01, 2022-07-01 22:10:22
23:16:21.618 [main] DEBUG space.yangtao.mapper.StudentMapper.testTrim - <==      Total: 1
23:16:21.620 [main] INFO  space.yangtao.client.StudentMapperTest - trim标签测试：[StudentPO(id=1, name=zhangsan, height=176.1, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022)]
...

由日志可知，<trim>标签已正确拼接以及删除前缀后缀<or>。

set

<set> 标签是一个专门用于动态生成update语句中的set部分的动态SQL标签。使用<set>标签可以便捷地构建含有条件性更新字段的SQL语句，同时自动处理列表前后的逗号，确保语法正确。

在Mapper中定义SQL如下

<update id="testSet" parameterType="space.yangtao.domain.po.StudentPO">
    update tt_student
    <set>
        <if test="id != null and id != ''">
            id = #{id},
        </if>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="height != null">
            height = #{height},
        </if>
    </set>
    where id = #{id}
</update>

对应的接口方法为

1	`void testSet(StudentPO studentPO);`

运行测试

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
studentMapper.testSet(new StudentPO().setId(1L).setHeight(180.0));
session.commit();

控制台输出日志如下

...
23:18:12.706 [main] DEBUG space.yangtao.mapper.StudentMapper.testSet - ==>  Preparing: update tt_student SET id = ?, height = ? where id = ?
23:18:12.723 [main] DEBUG space.yangtao.mapper.StudentMapper.testSet - ==> Parameters: 1(Long), 180.0(Double), 1(Long)
23:18:12.730 [main] DEBUG space.yangtao.mapper.StudentMapper.testSet - <==    Updates: 1
...

由日志可知，<set>标签对不为空的参数进行了智能拼接，并且智能地处理了逗号。

choose

<choose>标签提供了类似于Java中的switch关键字的功能，它用于在SQL映射文件中执行条件逻辑选择。这个标签允许在多个条件中选择一个执行。

choose标签包括三个部分：

when：这相当于switch语句中的case，用于指定一个条件。
otherwise：这相当于switch语句中的default，用于指定当没有任何when条件匹配时执行的操作。
choose：这是包裹when和otherwise的容器标签。

在Mapper中定义SQL如下

<select id="testChoose" resultType="space.yangtao.domain.po.StudentPO">
    select * from tt_student
    <choose>
        <when test="id != null and id != ''">
            where id = #{id}
        </when>
        <when test="name != null and name != ''">
            where name = #{name}
        </when>
        <otherwise>
            where 1 = 1
        </otherwise>
    </choose>
</select>

对应的接口方法为

1	`List<StudentPO> testChoose(Map<String, Object> params);`

运行测试程序

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("choose标签测试1：{}", studentMapper.testChoose(getParams()));
log.info("choose标签测试2：{}", studentMapper.testChoose(null));

控制台输出日志如下

...
23:20:50.736 [main] DEBUG space.yangtao.mapper.StudentMapper.testChoose - ==>  Preparing: select * from tt_student where id = ?
23:20:50.755 [main] DEBUG space.yangtao.mapper.StudentMapper.testChoose - ==> Parameters: 1(Long)
23:20:50.768 [main] TRACE space.yangtao.mapper.StudentMapper.testChoose - <==    Columns: id, class_id, name, height, gender, birthday, create_time
23:20:50.769 [main] TRACE space.yangtao.mapper.StudentMapper.testChoose - <==        Row: 1, 1, zhangsan, 180.0, 男, 1990-01-01, 2022-07-01 22:10:22
23:20:50.771 [main] DEBUG space.yangtao.mapper.StudentMapper.testChoose - <==      Total: 1
23:20:50.772 [main] INFO  space.yangtao.client.StudentMapperTest - choose标签测试1：[StudentPO(id=1, name=zhangsan, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022)]
23:20:50.773 [main] DEBUG space.yangtao.mapper.StudentMapper - Cache Hit Ratio [space.yangtao.mapper.StudentMapper]: 0.0
23:20:50.773 [main] DEBUG space.yangtao.mapper.StudentMapper.testChoose - ==>  Preparing: select * from tt_student where 1 = 1
23:20:50.773 [main] DEBUG space.yangtao.mapper.StudentMapper.testChoose - ==> Parameters: 
23:20:50.774 [main] TRACE space.yangtao.mapper.StudentMapper.testChoose - <==    Columns: id, class_id, name, height, gender, birthday, create_time
23:20:50.774 [main] TRACE space.yangtao.mapper.StudentMapper.testChoose - <==        Row: 1, 1, zhangsan, 180.0, 男, 1990-01-01, 2022-07-01 22:10:22
23:20:50.774 [main] TRACE space.yangtao.mapper.StudentMapper.testChoose - <==        Row: 2, 2, zhangsan1, 180.0, 男, 1990-01-01, 2022-07-01 21:10:22
23:20:50.774 [main] TRACE space.yangtao.mapper.StudentMapper.testChoose - <==        Row: 3, 2, zhangsan2, 180.0, 男, 1990-01-01, 2022-07-01 21:10:22
23:20:50.775 [main] DEBUG space.yangtao.mapper.StudentMapper.testChoose - <==      Total: 3
23:20:50.775 [main] INFO  space.yangtao.client.StudentMapperTest - choose标签测试2：[StudentPO(id=1, name=zhangsan, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022), StudentPO(id=2, name=zhangsan1, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022), StudentPO(id=3, name=zhangsan2, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022)]
...

由日志可知，<choose>标签会根据条件正确选择where条件。

foreach

<foreach>标签用于在SQL语句中处理集合和数组。这个标签允许你迭代一个集合，并为每个元素生成相应的SQL片段，如列表、IN查询条件或批量插入语句等。

在Mapper中定义SQL如下

<select id="testForeach" resultType="space.yangtao.domain.po.StudentPO">
    select * from tt_student where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

对应的接口方法为

1	`List<StudentPO> testForeach(@Param("ids") List<Long> ids);`

运行测试程序

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("foreach标签测试1：{}", studentMapper.testForeach(Arrays.asList(1L, 2L)));
log.info("foreach标签测试2：{}", studentMapper.testForeach(null));

控制台输出日志如下

...
23:25:44.657 [main] DEBUG space.yangtao.mapper.StudentMapper.testForeach - ==>  Preparing: select * from tt_student where id in ( ? , ? )
23:25:44.675 [main] DEBUG space.yangtao.mapper.StudentMapper.testForeach - ==> Parameters: 1(Long), 2(Long)
23:25:44.690 [main] TRACE space.yangtao.mapper.StudentMapper.testForeach - <==    Columns: id, class_id, name, height, gender, birthday, create_time
23:25:44.691 [main] TRACE space.yangtao.mapper.StudentMapper.testForeach - <==        Row: 1, 1, zhangsan, 180.0, 男, 1990-01-01, 2022-07-01 22:10:22
23:25:44.693 [main] TRACE space.yangtao.mapper.StudentMapper.testForeach - <==        Row: 2, 2, zhangsan1, 180.0, 男, 1990-01-01, 2022-07-01 21:10:22
23:25:44.694 [main] DEBUG space.yangtao.mapper.StudentMapper.testForeach - <==      Total: 2
23:25:44.695 [main] INFO  space.yangtao.client.StudentMapperTest - foreach标签测试1：[StudentPO(id=1, name=zhangsan, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022), StudentPO(id=2, name=zhangsan1, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022)]

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.builder.BuilderException: The expression 'ids' evaluated to a null value.
### Cause: org.apache.ibatis.builder.BuilderException: The expression 'ids' evaluated to a null value.

由日志可知，当传入集合不为空时，<foreach>标签会正确地为SQL拼接参数，当传入参数为null时，SQL处理则会出现异常，可以结合<if>标签处理集合为空的情况，如

1
2
3

<if test="ids != null and ids.size() > 0">
    <!-- foreach 标签 -->
</if>

sql和include

<sql>和<include>标签是用于复用SQL代码片段的强大工具。它们可以显著提高SQL语句的可维护性和可读性，尤其是在处理大量重复或结构相似的语句时。

在Mapper中定义SQL如下

<sql id="columnsSql">
    id,
    name,
    height,
    gender,
    birthday,
    create_time
</sql>
<select id="testSqlInclude" resultType="space.yangtao.domain.po.StudentPO">
    select
    <include refid="columnsSql"/>
    from tt_student
</select>

对应的接口方法

1	`List<StudentPO> testSqlInclude();`

运行测试程序

1
2
3

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("sqlInclude标签测试：{}", studentMapper.testSqlInclude());

控制台输出日志如下

...
23:28:35.159 [main] DEBUG space.yangtao.mapper.StudentMapper.testSqlInclude - ==>  Preparing: select id, name, height, gender, birthday, create_time from tt_student
23:28:35.178 [main] DEBUG space.yangtao.mapper.StudentMapper.testSqlInclude - ==> Parameters: 
23:28:35.192 [main] TRACE space.yangtao.mapper.StudentMapper.testSqlInclude - <==    Columns: id, name, height, gender, birthday, create_time
23:28:35.194 [main] TRACE space.yangtao.mapper.StudentMapper.testSqlInclude - <==        Row: 1, zhangsan, 180.0, 男, 1990-01-01, 2022-07-01 22:10:22
23:28:35.196 [main] TRACE space.yangtao.mapper.StudentMapper.testSqlInclude - <==        Row: 2, zhangsan1, 180.0, 男, 1990-01-01, 2022-07-01 21:10:22
23:28:35.197 [main] TRACE space.yangtao.mapper.StudentMapper.testSqlInclude - <==        Row: 3, zhangsan2, 180.0, 男, 1990-01-01, 2022-07-01 21:10:22
23:28:35.197 [main] DEBUG space.yangtao.mapper.StudentMapper.testSqlInclude - <==      Total: 3
23:28:35.198 [main] INFO  space.yangtao.client.StudentMapperTest - sqlInclude标签测试：[StudentPO(id=1, name=zhangsan, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 22:10:22 CST 2022), StudentPO(id=2, name=zhangsan1, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022), StudentPO(id=3, name=zhangsan2, height=180.0, gender=男, birthday=Mon Jan 01 00:00:00 CST 1990, createTime=Fri Jul 01 21:10:22 CST 2022)]
...

由日志可知，<include>标签已正确地将指定<sql>标签中的语句拼接到原SQL语句中。

高级映射

MyBatis提供了强大的映射功能，允许开发者在数据库操作中实现复杂的对象关系映射，包括一对多、多对一的关系。这些高级映射主要通过<resultMap>标签来配置，能够处理复杂的 SQL 查询结果并将它们正确地映射到Java对象和对象集合中。

创建一个班级表tt_class

CREATE TABLE `tt_class`
(
    `id`          bigint      NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `grade`       varchar(20) NOT NULL COMMENT '年级',
    `no`          int         NOT NULL COMMENT '班级号',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4 COMMENT ='班级信息表';

为学生表添加字段class_id

1	`alter table tt_student add column class_id bigint not null comment '班级ID' after id;`

新增数据

INSERT INTO mybatis_db.tt_class (id, grade, no) VALUES (1, '一年级', 1);
INSERT INTO mybatis_db.tt_class (id, grade, no) VALUES (2, '二年级', 1);

INSERT INTO mybatis_db.tt_student (id, class_id, name, height, gender, birthday, create_time) VALUES (1, 1, 'zhangsan', 180, '男', '1990-01-01', '2024-07-01 22:10:22');
INSERT INTO mybatis_db.tt_student (id, class_id, name, height, gender, birthday, create_time) VALUES (2, 2, 'lisi', 178.1, '男', '1991-02-12', '2024-07-02 22:33:24');
INSERT INTO mybatis_db.tt_student (id, class_id, name, height, gender, birthday, create_time) VALUES (3, 2, 'wangwu', 172, '女', '1991-04-21', '2024-07-03 22:40:43');

整体数据如下

+----+--------+----+
| id | grade  | no |
+----+--------+----+
|  1 | 一年级 |  1 |
|  2 | 二年级 |  1 |
+----+--------+----+

+----+----------+----------+--------+--------+------------+---------------------+
| id | class_id | name     | height | gender | birthday   | create_time         |
+----+----------+----------+--------+--------+------------+---------------------+
|  1 |        1 | zhangsan |    180 | 男     | 1990-01-01 | 2024-07-01 22:10:22 |
|  2 |        2 | lisi     |  178.1 | 男     | 1991-02-12 | 2024-07-02 22:33:24 |
|  3 |        2 | wangwu   |    172 | 女     | 1991-04-21 | 2024-07-03 22:40:43 |
+----+----------+----------+--------+--------+------------+---------------------+

多对一

多对一映射用于处理一个对象属于另一个对象的情况，例如每个学生属于一个班级。

实体类结构

现在要构造StudentVO，其中包含属性ClassVO，两个实体类结构如下

public class ClassVO {

    private String grade;

    private int no;

}

public class StudentVO {
    
    private Long id;

    private String name;

    private ClassVO classVO;

    private double height;

}

级联属性映射

在Mapper中定义查询方法，同时定义一个resultMap，标签中含有对应的classVO的属性名与数据库字段名的映射

<resultMap id="scMap1" type="space.yangtao.domain.vo.StudentVO">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="classVO.grade" column="grade"/>
    <result property="classVO.no" column="no"/>
    <result property="height" column="height"/>
</resultMap>
<select id="getScVO1" resultMap="scMap1">
    select s.id,
           s.name,
           c.grade,
           c.no,
           s.height
    from tt_student s
    left join tt_class c on s.class_id = c.id
    where s.id = #{id}
</select>

对应的接口方法为

1	`StudentVO getScVO1(Long id);`

运行测试程序

1
2
3

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("getScVO1：{}", studentMapper.getScVO1(1L));

控制台打印日志如下

...
08:57:49.636 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO1 - ==>  Preparing: select s.id, s.name, c.grade, c.no, s.height from tt_student s left join tt_class c on s.class_id = c.id where s.id = ?
08:57:49.654 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO1 - ==> Parameters: 1(Long)
08:57:49.685 [main] TRACE space.yangtao.mapper.StudentMapper.getScVO1 - <==    Columns: id, name, grade, no, height
08:57:49.686 [main] TRACE space.yangtao.mapper.StudentMapper.getScVO1 - <==        Row: 1, zhangsan, 一年级, 1, 180.0
08:57:49.687 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO1 - <==      Total: 1
08:57:49.688 [main] INFO  space.yangtao.client.StudentMapperTest - getScVO1：StudentVO(id=1, name=zhangsan, classVO=ClassVO(grade=一年级, no=1), height=180.0)
...

由日志可知，StudentVO对象中已包含ClassVO对象，resultMap已完成多对一属性映射。

association

<association>标签用于处理一对一的关系。它相当于Java中对象的嵌套，允许将查询结果的一部分映射到另一个对象的属性中。

property：Java对象中关联对象的属性名。
javaType：关联对象的类型（全限定类名或别名）。
column：用于关联查询的列名。
select：指定分步查询的方法，全限定名。
fetchType：加载方式，lazy（延迟加载）或eager（立即加载）。

在Mapper文件中，同样定义resultMap，同时使用association标签，将学生对象中的classVO与数据库字段进行关联

<resultMap id="scMap2" type="space.yangtao.domain.vo.StudentVO">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="height" column="height"/>
    <association property="classVO" javaType="space.yangtao.domain.vo.ClassVO" fetchType="lazy">
        <result property="grade" column="grade"/>
        <result property="no" column="no"/>
    </association>
</resultMap>
<select id="getScVO2" resultMap="scMap2">
    select s.id,
           s.name,
           c.grade,
           c.no,
           s.height
    from tt_student s
    left join tt_class c on s.class_id = c.id
    where s.id = #{id}
</select>

对应的接口方法

1	`StudentVO getScVO2(Long id);`

运行测试程序

1
2
3

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("getScVO2：{}", studentMapper.getScVO2(1L));

控制台打印日志如下

...
08:58:56.713 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO2 - ==>  Preparing: select s.id, s.name, c.grade, c.no, s.height from tt_student s left join tt_class c on s.class_id = c.id where s.id = ?
08:58:56.730 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO2 - ==> Parameters: 1(Long)
08:58:56.744 [main] TRACE space.yangtao.mapper.StudentMapper.getScVO2 - <==    Columns: id, name, grade, no, height
08:58:56.744 [main] TRACE space.yangtao.mapper.StudentMapper.getScVO2 - <==        Row: 1, zhangsan, 一年级, 1, 180.0
08:58:56.745 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO2 - <==      Total: 1
08:58:56.747 [main] INFO  space.yangtao.client.StudentMapperTest - getScVO2：StudentVO(id=1, name=zhangsan, classVO=ClassVO(grade=一年级, no=1), height=180.0)
...

由日志可知，StudentVO对象中已包含ClassVO对象，resultMap已完成多对一属性映射。

分步查询

与前面两种方法不同，分布查询需要书写两条SQL语句，这种方法的优点是可复用、支持懒加载。

在StudentMapper中定义查询语句

<select id="getVoById" resultMap="scMap3">
    select s.id,
    s.class_id,
    s.name,
    s.height
    from tt_student s
    where s.id = #{id}
</select>

定义对应的resultMap

<resultMap id="scMap3" type="space.yangtao.domain.vo.StudentVO">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="height" column="height"/>
    <!--
    association：关联，一个学生对应一个班级（需放在所有result之后）
    property：关联的属性
    javaType：关联的实体类
    column：主表与子表的关联字段
    select：子查询对应的接口方法（全类名）
    -->
    <association property="classVO"
                 javaType="space.yangtao.domain.vo.ClassVO"
                 column="class_id"
                 select="space.yangtao.mapper.ClassMapper.getVoById">
    </association>
</resultMap>

在ClassMapper中定义刚才声明的getVoById方法

1
2
3

<select id="getVoById" resultType="space.yangtao.domain.vo.ClassVO">
    select * from tt_class where id = ${id}
</select>

两个查询对应的接口方法分别为

1	`StudentVO getScVO3(Long id);`

1	`ClassVO getVoById(Long id);`

运行测试程序

1
2
3

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("getScVO3：{}", studentMapper.getScVO3(1L));

控制台输出日志如下

...
09:01:34.577 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO3 - ==>  Preparing: select s.id, s.class_id, s.name, s.height from tt_student s where s.id = 1
09:01:34.593 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO3 - ==> Parameters: 
09:01:34.605 [main] TRACE space.yangtao.mapper.StudentMapper.getScVO3 - <==    Columns: id, class_id, name, height
09:01:34.606 [main] TRACE space.yangtao.mapper.StudentMapper.getScVO3 - <==        Row: 1, 1, zhangsan, 180.0
09:01:34.607 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById - ====>  Preparing: select * from tt_class where id = ?
09:01:34.607 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById - ====> Parameters: 1(Integer)
09:01:34.608 [main] TRACE space.yangtao.mapper.ClassMapper.getVoById - <====    Columns: id, grade, no
09:01:34.608 [main] TRACE space.yangtao.mapper.ClassMapper.getVoById - <====        Row: 1, 一年级, 1
09:01:34.609 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById - <====      Total: 1
09:01:34.610 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO3 - <==      Total: 1
09:01:34.610 [main] INFO  space.yangtao.client.StudentMapperTest - getScVO3：StudentVO(id=1, name=zhangsan, classVO=ClassVO(grade=一年级, no=1), height=180.0)
...

由日志可知，StudentVO对象中已包含ClassVO对象，resultMap已完成多对一属性映射，同时可以看到进行了两次SQL查询。

拓展：单个Mapper方法开启延迟加载，修改resultMap中的association标签，添加属性fetchType="lazy"

<association property="classVO"
             javaType="space.yangtao.domain.vo.ClassVO"
             column="class_id"
             select="space.yangtao.mapper.ClassMapper.getVoById"
             fetchType="lazy">

或开启全局延迟加载（注意单个Mapper配置会覆盖全局延迟加载配置，推荐项目开启全局延迟加载，特别的不需要延迟加载则单独设置fetchType="eager"即可）

1	`<setting name="lazyLoadingEnabled" value="true"/>`

运行测试程序：获取到学生对象后，只获取其名称，对于班级对象classVO不做操作

1
2
3

SqlSession session = SqlSessionUtil.getSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
log.info("getScVO3：{}", studentMapper.getScVO3(1L).getName());

控制台输出日志如下

...
09:03:01.035 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO3 - ==>  Preparing: select s.id, s.class_id, s.name, s.height from tt_student s where s.id = 1
09:03:01.055 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO3 - ==> Parameters: 
09:03:01.070 [main] TRACE space.yangtao.mapper.StudentMapper.getScVO3 - <==    Columns: id, class_id, name, height
09:03:01.070 [main] TRACE space.yangtao.mapper.StudentMapper.getScVO3 - <==        Row: 1, 1, zhangsan, 180.0
09:03:01.103 [main] DEBUG space.yangtao.mapper.StudentMapper.getScVO3 - <==      Total: 1
09:03:01.104 [main] INFO  space.yangtao.client.StudentMapperTest - getScVO3：zhangsan
...

由日志可知，MyBatis只对tt_student表进行了查询，而不再对tt_class进行查询，即只进行了一次查询，这是分布查询的一个优化点。

一对多

一对多映射用于处理一个对象关联多个其他对象的情况，例如一个班级有多个学生。

实体类结构

重新设计学生和班级的实体类：ClassVO中含有一个StudentVO的集合

public class StudentVO {
    
    private Long id;

    private String name;
    
}

public class ClassVO {

    private String grade;

    private int no;

    private List<StudentVO> students;

}

collection

<collection>标签用于处理一对多的关系。这种标签允许你将查询结果的一部分映射到Java集合属性中。

property：Java对象中集合属性的名称。
ofType：集合中元素的类型。
select：指定分步查询的方法。
column：用于关联查询的列名。

在Mapper文件中定义SQL如下

<select id="getVoById2" resultMap="csMap1">
    select c.grade,
           c.no,
           s.id,
           s.name
    from tt_class c
    left join tt_student s on s.class_id = c.id
    where c.id = #{id}
</select>

对应的接口方法为

1	`space.yangtao.domain.vo2.ClassVO getVoById2(Long id);`

定义对应的resultMap，使用collection标签指定集合

<resultMap id="csMap1" type="space.yangtao.domain.vo2.ClassVO">
    <result column="grade" property="grade"/>
    <result column="no" property="no"/>
    <!-- 指定集合，property为属性名，ofType为属性的类型 -->
    <collection property="students" ofType="space.yangtao.domain.vo2.StudentVO">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
    </collection>
</resultMap>

运行测试程序

1
2
3

SqlSession session = SqlSessionUtil.getSession();
ClassMapper classMapper = session.getMapper(ClassMapper.class);
log.info("getCsVO1：{}", classMapper.getVoById2(2L));

控制台输出日志如下

...
09:04:52.321 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById2 - ==>  Preparing: select c.grade, c.no, s.id, s.name from tt_class c left join tt_student s on s.class_id = c.id where c.id = ?
09:04:52.339 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById2 - ==> Parameters: 2(Long)
09:04:52.353 [main] TRACE space.yangtao.mapper.ClassMapper.getVoById2 - <==    Columns: grade, no, id, name
09:04:52.353 [main] TRACE space.yangtao.mapper.ClassMapper.getVoById2 - <==        Row: 二年级, 1, 2, zhangsan1
09:04:52.354 [main] TRACE space.yangtao.mapper.ClassMapper.getVoById2 - <==        Row: 二年级, 1, 3, zhangsan2
09:04:52.354 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById2 - <==      Total: 2
09:04:52.355 [main] INFO  space.yangtao.client.StudentMapperTest - getCsVO1：ClassVO(grade=二年级, no=1, students=[StudentVO(id=2, name=zhangsan1), StudentVO(id=3, name=zhangsan2)])
...

由日志可知，ClassVO对象中已包含多个StudentVO对象，resultMap已完成一对多属性映射。

分步查询

在Mapper文件中定义SQL如下

<select id="getVoById3" resultMap="csMap2">
    select c.grade,
    c.no,
    c.id as class_id
    from tt_class c
    where c.id = #{id}
</select>

对应的接口方法为

1	`space.yangtao.domain.vo2.ClassVO getVoById3(Long id);`

定义resultMap，使用collection标签指定集合

<resultMap id="csMap2" type="space.yangtao.domain.vo2.ClassVO">
    <result column="grade" property="grade"/>
    <result column="no" property="no"/>
    <collection property="students"
                ofType="space.yangtao.domain.vo2.StudentVO"
                select="space.yangtao.mapper.StudentMapper.getVoByClassId"
                column="class_id">
    </collection>
</resultMap>

在StudentMapper中定义刚才在collection中声明的getVoByClassId方法

1
2
3

<select id="getVoByClassId" resultType="space.yangtao.domain.vo2.StudentVO">
    select id, name from tt_student where class_id = #{class_id}
</select>

运行测试程序如下

1
2
3

SqlSession session = SqlSessionUtil.getSession();
ClassMapper classMapper = session.getMapper(ClassMapper.class);
log.info("getCsVO2：{}", classMapper.getVoById3(2L));

控制台输出日志如下

...
09:06:14.124 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById3 - ==>  Preparing: select c.grade, c.no, c.id as class_id from tt_class c where c.id = ?
09:06:14.142 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById3 - ==> Parameters: 2(Long)
09:06:14.153 [main] TRACE space.yangtao.mapper.ClassMapper.getVoById3 - <==    Columns: grade, no, class_id
09:06:14.154 [main] TRACE space.yangtao.mapper.ClassMapper.getVoById3 - <==        Row: 二年级, 1, 2
09:06:14.156 [main] DEBUG space.yangtao.mapper.StudentMapper - Cache Hit Ratio [space.yangtao.mapper.StudentMapper]: 0.0
09:06:14.156 [main] DEBUG space.yangtao.mapper.StudentMapper.getVoByClassId - ====>  Preparing: select id, name from tt_student where class_id = ?
09:06:14.156 [main] DEBUG space.yangtao.mapper.StudentMapper.getVoByClassId - ====> Parameters: 2(Long)
09:06:14.157 [main] TRACE space.yangtao.mapper.StudentMapper.getVoByClassId - <====    Columns: id, name
09:06:14.158 [main] TRACE space.yangtao.mapper.StudentMapper.getVoByClassId - <====        Row: 2, zhangsan1
09:06:14.158 [main] TRACE space.yangtao.mapper.StudentMapper.getVoByClassId - <====        Row: 3, zhangsan2
09:06:14.158 [main] DEBUG space.yangtao.mapper.StudentMapper.getVoByClassId - <====      Total: 2
09:06:14.159 [main] DEBUG space.yangtao.mapper.ClassMapper.getVoById3 - <==      Total: 1
09:06:14.160 [main] INFO  space.yangtao.client.StudentMapperTest - getCsVO2：ClassVO(grade=二年级, no=1, students=[StudentVO(id=2, name=zhangsan1), StudentVO(id=3, name=zhangsan2)])
...

由日志可知，ClassVO对象中已包含多个StudentVO对象，resultMap已完成一对多属性映射。

补充：跟多对一一样，一对多的分步查询页也支持延迟加载，且开启方式一致。

总结

通过学习本文，开发者不仅能够掌握MyBatis的动态SQL构建技巧，还能了解到如何通过高级映射技术实现一对多和多对一的关系映射。这些技术不仅优化了代码结构，也提高了开发效率和数据操作的灵活性。随着对MyBatis功能的深入理解，开发者将能更加自信地应用这些高级功能来设计和实现更复杂的数据库交互操作。

MyBatis

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

MyBatis配置上一篇

MyBatis核心功能一下一篇