MyBatis Mapper 映射详解

XML 映射、注解映射、动态 SQL、SQL 构建

XML Mapper 结构

xml

<?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.mapper.UserMapper">
    
    <!-- 结果映射 -->
    <resultMap id="BaseResultMap" type="User">
        <id column="id" property="id"/>
        <result column="user_name" property="name"/>
        <result column="create_time" property="createTime"/>
    </resultMap>
    
    <!-- SQL 片段 -->
    <sql id="Base_Column_List">
        id, user_name, email, create_time
    </sql>
    
    <!-- 查询 -->
    <select id="selectById" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM users
        WHERE id = #{id}
    </select>
    
    <!-- 插入 -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO users (user_name, email)
        VALUES (#{name}, #{email})
    </insert>
    
    <!-- 更新 -->
    <update id="update">
        UPDATE users
        SET user_name = #{name},
            email = #{email}
        WHERE id = #{id}
    </update>
    
    <!-- 删除 -->
    <delete id="deleteById">
        DELETE FROM users WHERE id = #{id}
    </delete>
    
</mapper>

CRUD 操作

查询 (select)

xml

<!-- 简单查询 -->
<select id="selectById" resultType="User">
    SELECT * FROM users WHERE id = #{id}
</select>

<!-- 多参数查询（推荐：@Param） -->
<select id="selectByNameAndEmail" resultType="User">
    SELECT * FROM users 
    WHERE user_name = #{name} AND email = #{email}
</select>

<!-- 使用 resultMap -->
<select id="selectAll" resultMap="BaseResultMap">
    SELECT id, user_name, email, create_time
    FROM users
    ORDER BY create_time DESC
</select>

<!-- 返回单个简单类型 -->
<select id="count" resultType="int">
    SELECT COUNT(*) FROM users
</select>

<!-- 返回 Map -->
<select id="selectAsMap" resultType="map">
    SELECT id, user_name as name FROM users WHERE id = #{id}
</select>

<!-- 分页查询 -->
<select id="selectPage" resultMap="BaseResultMap">
    SELECT * FROM users 
    ORDER BY id
    LIMIT #{offset}, #{limit}
</select>

插入 (insert)

xml

<!-- 简单插入 -->
<insert id="insert" parameterType="User">
    INSERT INTO users (user_name, email, age)
    VALUES (#{name}, #{email}, #{age})
</insert>

<!-- 获取自增主键 -->
<insert id="insertAndGetId" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO users (user_name, email)
    VALUES (#{name}, #{email})
</insert>

<!-- 使用 selectKey 获取序列 -->
<insert id="insertWithSequence">
    <selectKey keyProperty="id" resultType="long" order="BEFORE">
        SELECT SEQ_USERS.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO users (id, user_name, email)
    VALUES (#{id}, #{name}, #{email})
</insert>

<!-- 批量插入 -->
<insert id="batchInsert" parameterType="java.util.List">
    INSERT INTO users (user_name, email)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.email})
    </foreach>
</insert>

<!-- Oracle 批量插入 -->
<insert id="batchInsertOracle" parameterType="java.util.List">
    INSERT ALL
    <foreach collection="list" item="item">
        INTO users (id, user_name, email)
        VALUES (#{item.id}, #{item.name}, #{item.email})
    </foreach>
    SELECT 1 FROM DUAL
</insert>

更新 (update)

xml

<update id="update">
    UPDATE users
    SET user_name = #{name},
        email = #{email},
        age = #{age}
    WHERE id = #{id}
</update>

<!-- 动态更新 -->
<update id="dynamicUpdate">
    UPDATE users
    <set>
        <if test="name != null">user_name = #{name},</if>
        <if test="email != null">email = #{email},</if>
    </set>
    WHERE id = #{id}
</update>

删除 (delete)

xml

<delete id="deleteById">
    DELETE FROM users WHERE id = #{id}
</delete>

<delete id="batchDelete" parameterType="java.util.List">
    DELETE FROM users WHERE id IN
    <foreach collection="list" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

动态 SQL

if 条件

xml

<select id="search" resultType="User">
    SELECT * FROM users
    WHERE 1=1
    <if test="name != null">
        AND user_name LIKE CONCAT('%', #{name}, '%')
    </if>
    <if test="age != null and age > 0">
        AND age = #{age}
    </if>
    <if test="email != null and email.trim() != ''">
        AND email = #{email}
    </if>
</select>

choose / when / otherwise

xml

<select id="searchByCondition" resultType="User">
    SELECT * FROM users
    WHERE 1=1
    <choose>
        <when test="name != null">
            AND user_name = #{name}
        </when>
        <when test="email != null">
            AND email = #{email}
        </when>
        <otherwise>
            AND status = 'ACTIVE'
        </otherwise>
    </choose>
</select>

where 标签

xml

<!-- 自动处理 AND/OR 前缀 -->
<select id="searchAuto" resultType="User">
    SELECT * FROM users
    <where>
        <if test="name != null">
            AND user_name = #{name}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</select>

set 标签

xml

<!-- 自动去除末尾逗号 -->
<update id="updateSelective">
    UPDATE users
    <set>
        <if test="name != null">user_name = #{name},</if>
        <if test="email != null">email = #{email},</if>
        <if test="age != null">age = #{age},</if>
    </set>
    WHERE id = #{id}
</update>

foreach 循环

xml

<!-- IN 查询 -->
<select id="selectByIds" resultType="User">
    SELECT * FROM users
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 批量插入 -->
<insert id="batchInsert">
    INSERT INTO users (user_name, email) VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.name}, #{user.email})
    </foreach>
</insert>

<!-- 批量更新 -->
<update id="batchUpdate">
    <foreach collection="list" item="user" separator=";">
        UPDATE users 
        SET user_name = #{user.name}
        WHERE id = #{user.id}
    </foreach>
</update>

<!-- 使用 @Param 指定参数名 -->
<select id="selectByIds">
    SELECT * FROM users
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

bind 绑定

xml

<!-- 绑定变量，避免 SQL 注入 -->
<select id="searchLike" resultType="User">
    <bind name="pattern" value="'%' + name + '%'"/>
    SELECT * FROM users
    WHERE user_name LIKE #{pattern}
</select>

trim 自定义

xml

<!-- 等同于 where -->
<trim prefix="WHERE" prefixOverrides="AND|OR">
    <if test="name != null">AND user_name = #{name}</if>
</trim>

<!-- 等同于 set -->
<trim suffixOverrides=",">
    <if test="name != null">user_name = #{name},</if>
    <if test="email != null">email = #{email},</if>
</trim>

resultMap 结果映射

基础映射

xml

<resultMap id="BaseResultMap" type="User">
    <!-- 主键映射 -->
    <id column="id" property="id"/>
    
    <!-- 普通列映射 -->
    <result column="user_name" property="name"/>
    <result column="email" property="email"/>
    <result column="create_time" property="createTime"/>
</resultMap>

自动映射

xml

<!-- 未匹配列使用自动映射（列名=属性名） -->
<resultMap id="AutoMap" type="User" autoMapping="true">
    <id column="id" property="id"/>
    <!-- 其他列自动映射 -->
</resultMap>

构造方法映射

xml

<resultMap id="ConstructorMap" type="User">
    <constructor>
        <idArg column="id" javaType="long"/>
        <arg column="user_name" javaType="string"/>
        <arg column="email" javaType="string"/>
    </constructor>
</resultMap>

注解映射

简单注解

java

public interface UserMapper {
    
    @Select("SELECT * FROM users WHERE id = #{id}")
    User selectById(Long id);
    
    @Insert("INSERT INTO users (user_name, email) VALUES (#{name}, #{email})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    
    @Update("UPDATE users SET user_name = #{name} WHERE id = #{id}")
    int update(User user);
    
    @Delete("DELETE FROM users WHERE id = #{id}")
    int deleteById(Long id);
}

@Results 定义结果映射

java

@Results(id = "UserResult", value = {
    @Result(column = "id", property = "id"),
    @Result(column = "user_name", property = "name"),
    @Result(column = "email", property = "email"),
    @Result(column = "create_time", property = "createTime")
})
@Select("SELECT * FROM users WHERE id = #{id}")
User selectById(Long id);

@One 一对一关联

java

@Results({
    @Result(column = "user_id", property = "userId"),
    @Result(column = "user_id", property = "user",
            one = @One(select = "com.example.mapper.UserMapper.selectById"))
})
@Select("SELECT * FROM orders WHERE id = #{id}")
Order selectOrderWithUser(Long id);

@Many 一对多关联

java

@Results({
    @Result(column = "id", property = "id"),
    @Result(column = "user_name", property = "name"),
    @Result(property = "orders",
            many = @Many(select = "com.example.mapper.OrderMapper.selectByUserId"))
})
@Select("SELECT * FROM users WHERE id = #{id}")
User selectUserWithOrders(Long id);

动态 SQL 注解

java

public interface UserMapper {
    
    @SelectProvider(type = UserSqlProvider.class, method = "search")
    List<User> search(UserQuery query);
}

public class UserSqlProvider {
    
    public String search(UserQuery query) {
        return new SQL()
            .SELECT("*")
            .FROM("users")
            .WHERE("1=1")
            .ifWhere(query.getName() != null, 
                "user_name LIKE CONCAT('%', #{name}, '%')")
            .ifWhere(query.getAge() != null, 
                "age = #{age}")
            .toString();
    }
}

SQL 片段

xml

<mapper>
    <!-- 可复用 SQL 片段 -->
    <sql id="Base_Column_List">
        id, user_name, email, age, create_time
    </sql>
    
    <select id="selectAll" resultType="User">
        SELECT <include refid="Base_Column_List"/>
        FROM users
    </select>
    
    <select id="selectById" resultType="User">
        SELECT <include refid="Base_Column_List"/>
        FROM users
        WHERE id = #{id}
    </select>
    
    <!-- 引用外部 SQL 片段 -->
    <select id="search" resultType="User">
        SELECT <include refid="com.example.mapper.CommonMapper.Base_Column_List"/>
        FROM users
        <where>
            <if test="name != null">
                AND user_name = #{name}
            </if>
        </where>
    </select>
</mapper>

关联查询

关联类型	实现方式
一对一	`<association>` 或 `@One`
一对多	`<collection>` 或 `@Many`
多对多	中间表 + 两个一对多

详细见：**Java\MyBatis\MyBatis-关联查询**

Java语言基础

面向对象核心

集合框架

IO与NIO

Lambda与Stream

多线程与并发

SpringBoot与Web开发

MyBatis Mapper 映射详解

XML Mapper 结构

CRUD 操作

查询 (select)

插入 (insert)

更新 (update)

删除 (delete)

动态 SQL

if 条件

choose / when / otherwise

where 标签

set 标签

foreach 循环

bind 绑定

trim 自定义

resultMap 结果映射

基础映射

自动映射

构造方法映射

注解映射

简单注解

@Results 定义结果映射

@One 一对一关联

@Many 一对多关联

动态 SQL 注解

SQL 片段

关联查询

相关页面

MyBatis Mapper 映射详解 ​

XML Mapper 结构 ​

CRUD 操作 ​

查询 (select) ​

插入 (insert) ​

更新 (update) ​

删除 (delete) ​

动态 SQL ​

if 条件 ​

choose / when / otherwise ​

where 标签 ​

set 标签 ​

foreach 循环 ​

bind 绑定 ​

trim 自定义 ​

resultMap 结果映射 ​

基础映射 ​

自动映射 ​

构造方法映射 ​

注解映射 ​

简单注解 ​

@Results 定义结果映射 ​

@One 一对一关联 ​

@Many 一对多关联 ​

动态 SQL 注解 ​

SQL 片段 ​

关联查询 ​

相关页面 ​

MyBatis Mapper 映射详解

XML Mapper 结构

CRUD 操作

查询 (select)

插入 (insert)

更新 (update)

删除 (delete)

动态 SQL

if 条件

choose / when / otherwise

where 标签

set 标签

foreach 循环

bind 绑定

trim 自定义

resultMap 结果映射

基础映射

自动映射

构造方法映射

注解映射

简单注解

@Results 定义结果映射

@One 一对一关联

@Many 一对多关联

动态 SQL 注解

SQL 片段

关联查询

相关页面