XML 映射器

XML 映射器是 MyBatis 最核心的部分，用于定义 SQL 语句和映射规则。本章将详细介绍 XML 映射文件的各种元素和使用方法。

映射文件结构

一个完整的 XML 映射文件结构如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mybatis.mapper.UserMapper">
    
    <!-- 缓存配置 -->
    <cache/>
    
    <!-- 结果映射 -->
    <resultMap id="BaseResultMap" type="User">
        <!-- 映射配置 -->
    </resultMap>
    
    <!-- SQL 片段 -->
    <sql id="Base_Column_List">
        id, username, password, email
    </sql>
    
    <!-- 查询语句 -->
    <select id="selectById" resultType="User">
        SELECT * FROM user WHERE id = #{id}
    </select>
    
    <!-- 插入语句 -->
    <insert id="insert" parameterType="User">
        INSERT INTO user (username, password) VALUES (#{username}, #{password})
    </insert>
    
    <!-- 更新语句 -->
    <update id="update" parameterType="User">
        UPDATE user SET username = #{username} WHERE id = #{id}
    </update>
    
    <!-- 删除语句 -->
    <delete id="deleteById">
        DELETE FROM user WHERE id = #{id}
    </delete>
</mapper>

namespace 命名空间

namespace 是映射文件的唯一标识，通常设置为对应 Mapper 接口的全限定名：

<mapper namespace="com.example.mybatis.mapper.UserMapper">

命名空间的作用：

• 绑定 Mapper 接口，实现接口方法与 SQL 语句的映射
• 隔离不同映射文件中的 SQL 语句 ID
• 防止 SQL 语句 ID 冲突

select 查询语句

select 元素用于定义查询语句。

基本用法

<select id="selectById" resultType="User">
    SELECT * FROM user WHERE id = #{id}
</select>

属性说明

属性	说明
id	命名空间中的唯一标识符，对应 Mapper 接口方法名
parameterType	参数类型（可省略，MyBatis 可自动推断）
resultType	返回结果类型（全限定名或别名）
resultMap	引用外部 resultMap（与 resultType 二选一）
flushCache	调用后是否清空本地缓存和二级缓存，默认 false
useCache	是否使用二级缓存，默认 true
timeout	超时时间（秒）
fetchSize	获取数量
statementType	语句类型：STATEMENT、PREPARED、CALLABLE
resultSetType	结果集类型
databaseId	数据库厂商标识

查询示例

<!-- 查询单个对象 -->
<select id="selectById" resultType="User">
    SELECT * FROM user WHERE id = #{id}
</select>

<!-- 查询列表 -->
<select id="selectAll" resultType="User">
    SELECT * FROM user ORDER BY id DESC
</select>

<!-- 查询数量 -->
<select id="count" resultType="long">
    SELECT COUNT(*) FROM user
</select>

<!-- 查询 Map -->
<select id="selectMap" resultType="map">
    SELECT id, username FROM user WHERE id = #{id}
</select>

<!-- 使用 resultMap -->
<select id="selectWithOrders" resultMap="UserWithOrdersMap">
    SELECT u.*, o.id as order_id, o.order_no
    FROM user u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{id}
</select>

<!-- 分页查询 -->
<select id="selectByPage" resultType="User">
    SELECT * FROM user
    LIMIT #{offset}, #{pageSize}
</select>

参数传递

<!-- 单个参数 -->
<select id="selectById" resultType="User">
    SELECT * FROM user WHERE id = #{id}
</select>

<!-- 多个参数（使用 @Param 注解） -->
<select id="selectByUsernameAndEmail" resultType="User">
    SELECT * FROM user 
    WHERE username = #{username} AND email = #{email}
</select>

<!-- 对象参数 -->
<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    WHERE username = #{username}
    AND status = #{status}
</select>

<!-- Map 参数 -->
<select id="selectByMap" resultType="User">
    SELECT * FROM user
    WHERE username = #{username}
    AND email = #{email}
</select>

insert 插入语句

insert 元素用于定义插入语句。

基本用法

<insert id="insert" parameterType="User">
    INSERT INTO user (username, password, email, phone)
    VALUES (#{username}, #{password}, #{email}, #{phone})
</insert>

主键回填

使用 useGeneratedKeys 获取数据库自动生成的主键：

<insert id="insert" parameterType="User" 
        useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user (username, password, email, phone)
    VALUES (#{username}, #{password}, #{email}, #{phone})
</insert>

执行插入后，user.getId() 即可获取生成的主键值。

自定义主键生成

对于不支持自动生成主键的数据库，可以使用 selectKey：

<insert id="insert" parameterType="User">
    <selectKey keyProperty="id" resultType="long" order="BEFORE">
        SELECT NEXT VALUE FOR user_seq
    </selectKey>
    INSERT INTO user (id, username, password)
    VALUES (#{id}, #{username}, #{password})
</insert>

属性	说明
keyProperty	主键属性名
resultType	主键类型
order	执行时机：BEFORE（插入前）、AFTER（插入后）
statementType	语句类型

MySQL 示例

<insert id="insert" parameterType="User" 
        useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user (username, password, email, phone, status)
    VALUES (#{username}, #{password}, #{email}, #{phone}, #{status})
</insert>

Oracle 示例

<insert id="insert" parameterType="User">
    <selectKey keyProperty="id" resultType="long" order="BEFORE">
        SELECT user_seq.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO user (id, username, password)
    VALUES (#{id}, #{username}, #{password})
</insert>

update 更新语句

update 元素用于定义更新语句。

基本用法

<update id="update" parameterType="User">
    UPDATE user
    SET username = #{username},
        email = #{email},
        phone = #{phone}
    WHERE id = #{id}
</update>

动态更新

只更新非空字段：

<update id="updateSelective" parameterType="User">
    UPDATE user
    <set>
        <if test="username != null">username = #{username},</if>
        <if test="email != null">email = #{email},</if>
        <if test="phone != null">phone = #{phone},</if>
        <if test="status != null">status = #{status},</if>
    </set>
    WHERE id = #{id}
</update>

delete 删除语句

delete 元素用于定义删除语句。

基本用法

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

批量删除

<delete id="deleteByIds">
    DELETE FROM user WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

sql SQL 片段

sql 元素用于定义可重用的 SQL 片段。

定义和引用

<!-- 定义 SQL 片段 -->
<sql id="Base_Column_List">
    id, username, password, email, phone, status, create_time, update_time
</sql>

<sql id="Example_Where_Clause">
    <where>
        <if test="username != null">AND username = #{username}</if>
        <if test="email != null">AND email = #{email}</if>
        <if test="status != null">AND status = #{status}</if>
    </where>
</sql>

<!-- 引用 SQL 片段 -->
<select id="selectByCondition" resultType="User">
    SELECT <include refid="Base_Column_List"/>
    FROM user
    <include refid="Example_Where_Clause"/>
</select>

带参数的 SQL 片段

<sql id="table">
    ${prefix}user
</sql>

<select id="selectById" resultType="User">
    SELECT * FROM <include refid="table">
        <property name="prefix" value="t_"/>
    </include>
    WHERE id = #{id}
</select>

resultMap 结果映射

resultMap 是 MyBatis 最强大的特性，用于复杂的结果集映射。

基本映射

<resultMap id="BaseResultMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="password" property="password"/>
    <result column="email" property="email"/>
    <result column="phone" property="phone"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
</resultMap>

属性说明

属性	说明
id	当前命名空间中的唯一标识
type	映射的 Java 类型
autoMapping	是否启用自动映射
extends	继承其他 resultMap

id 和 result 元素

属性	说明
property	Java Bean 属性名
column	数据库列名
javaType	Java 类型
jdbcType	JDBC 类型
typeHandler	类型处理器

id 元素用于标识主键字段，MyBatis 会使用主键来提高性能。

constructor 构造器映射

当类没有无参构造器时，使用构造器映射：

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

association 关联映射（一对一）

<resultMap id="UserWithOrderMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    
    <!-- 一对一关联 -->
    <association property="order" javaType="Order">
        <id column="order_id" property="id"/>
        <result column="order_no" property="orderNo"/>
        <result column="amount" property="amount"/>
    </association>
</resultMap>

嵌套查询

<resultMap id="UserWithOrderMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    
    <!-- 嵌套查询（延迟加载） -->
    <association property="order" 
                 column="id" 
                 select="com.example.mybatis.mapper.OrderMapper.selectByUserId"/>
</resultMap>

collection 集合映射（一对多）

<resultMap id="UserWithOrdersMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    
    <!-- 一对多关联 -->
    <collection property="orders" ofType="Order">
        <id column="order_id" property="id"/>
        <result column="order_no" property="orderNo"/>
        <result column="amount" property="amount"/>
    </collection>
</resultMap>

嵌套查询

<resultMap id="UserWithOrdersMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    
    <!-- 嵌套查询 -->
    <collection property="orders" 
                column="id" 
                ofType="Order"
                select="com.example.mybatis.mapper.OrderMapper.selectListByUserId"/>
</resultMap>

discriminator 鉴别器

根据结果值选择不同的 resultMap：

<resultMap id="VehicleResultMap" type="Vehicle">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <discriminator javaType="int" column="vehicle_type">
        <case value="1" resultMap="CarResultMap"/>
        <case value="2" resultMap="TruckResultMap"/>
    </discriminator>
</resultMap>

<resultMap id="CarResultMap" type="Car" extends="VehicleResultMap">
    <result column="door_count" property="doorCount"/>
</resultMap>

<resultMap id="TruckResultMap" type="Truck" extends="VehicleResultMap">
    <result column="load_capacity" property="loadCapacity"/>
</resultMap>

参数处理

参数占位符

MyBatis 提供两种参数占位符：

占位符	说明
#{}	预编译参数，安全，防止 SQL 注入
${}	字符串替换，不安全，用于动态表名、列名

<!-- 使用 #{}（推荐） -->
<select id="selectById" resultType="User">
    SELECT * FROM user WHERE id = #{id}
</select>

<!-- 使用 ${}（动态表名、列名） -->
<select id="selectByColumn" resultType="User">
    SELECT * FROM user WHERE ${columnName} = #{value}
</select>

<select id="selectFromTable" resultType="User">
    SELECT * FROM ${tableName} WHERE id = #{id}
</select>

参数类型

<!-- 基本类型 -->
<select id="selectById" resultType="User">
    SELECT * FROM user WHERE id = #{id}
</select>

<!-- 字符串 -->
<select id="selectByUsername" resultType="User">
    SELECT * FROM user WHERE username = #{username}
</select>

<!-- 对象 -->
<insert id="insert" parameterType="User">
    INSERT INTO user (username, password) VALUES (#{username}, #{password})
</insert>

<!-- Map -->
<select id="selectByMap" resultType="User">
    SELECT * FROM user WHERE username = #{username} AND email = #{email}
</select>

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

<!-- 数组 -->
<select id="selectByIdArray" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

内置参数

MyBatis 提供了两个内置参数：

<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    <where>
        <if test="_parameter != null">
            AND username = #{username}
        </if>
    </where>
</select>

<select id="selectByDatabaseId" resultType="User">
    <if test="_databaseId == 'mysql'">
        SELECT * FROM user LIMIT 10
    </if>
    <if test="_databaseId == 'oracle'">
        SELECT * FROM user WHERE ROWNUM &lt;= 10
    </if>
</select>

参数	说明
_parameter	单个参数时为该参数，多个参数时为 Map
_databaseId	当前数据库厂商标识

完整示例

实体类

@Data
public class User {
    private Long id;
    private String username;
    private String password;
    private String email;
    private String phone;
    private Integer status;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
    
    // 一对一关联
    private Order latestOrder;
    
    // 一对多关联
    private List<Order> orders;
}

Mapper 接口

public interface UserMapper {
    User selectById(Long id);
    List<User> selectAll();
    List<User> selectByCondition(UserQuery query);
    int insert(User user);
    int insertBatch(List<User> users);
    int update(User user);
    int deleteById(Long id);
    int deleteByIds(List<Long> ids);
    User selectWithOrders(Long id);
}

XML 映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mybatis.mapper.UserMapper">
    
    <resultMap id="BaseResultMap" type="User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="email" property="email"/>
        <result column="phone" property="phone"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>
    
    <resultMap id="UserWithOrdersMap" type="User" extends="BaseResultMap">
        <collection property="orders" ofType="Order">
            <id column="order_id" property="id"/>
            <result column="order_no" property="orderNo"/>
            <result column="amount" property="amount"/>
        </collection>
    </resultMap>
    
    <sql id="Base_Column_List">
        id, username, password, email, phone, status, create_time, update_time
    </sql>
    
    <select id="selectById" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM user
        WHERE id = #{id}
    </select>
    
    <select id="selectAll" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM user
        ORDER BY id DESC
    </select>
    
    <select id="selectByCondition" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM user
        <where>
            <if test="username != null and username != ''">
                AND username LIKE CONCAT('%', #{username}, '%')
            </if>
            <if test="email != null and email != ''">
                AND email = #{email}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
        </where>
        ORDER BY id DESC
    </select>
    
    <insert id="insert" parameterType="User" 
            useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user (username, password, email, phone, status)
        VALUES (#{username}, #{password}, #{email}, #{phone}, #{status})
    </insert>
    
    <insert id="insertBatch" parameterType="list">
        INSERT INTO user (username, password, email, phone, status)
        VALUES
        <foreach collection="list" item="user" separator=",">
            (#{user.username}, #{user.password}, #{user.email}, #{user.phone}, #{user.status})
        </foreach>
    </insert>
    
    <update id="update" parameterType="User">
        UPDATE user
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="email != null">email = #{email},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="status != null">status = #{status},</if>
        </set>
        WHERE id = #{id}
    </update>
    
    <delete id="deleteById">
        DELETE FROM user WHERE id = #{id}
    </delete>
    
    <delete id="deleteByIds">
        DELETE FROM user WHERE id IN
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    
    <select id="selectWithOrders" resultMap="UserWithOrdersMap">
        SELECT u.*, o.id as order_id, o.order_no, o.amount
        FROM user u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE u.id = #{id}
    </select>
    
</mapper>

小结

本章详细介绍了 XML 映射器的各种元素：

• select/insert/update/delete：CRUD 操作语句
• sql：可重用的 SQL 片段
• resultMap：复杂结果映射
• 参数处理：# 和 $ 的区别

下一章将介绍注解开发方式。