MyBatis 作为 Java 持久层框架，XML 映射文件是其核心配置方式。本文系统整理 MyBatis XML 中高频使用的 SQL 操作模板，涵盖多个实战场景：批量插入（<foreach> 拼接 VALUES）、批量更新（多条 UPDATE 语句以 ; 分隔）、批量删除（<foreach> 拼接 IN 条件）、时间范围查询（支持 Date 和 LocalDateTime 参数）、动态时间查询（使用 <where> + <if> 条件判断）、按日期部分比较（DATE() 函数忽略时间部分）、一对多嵌套查询（<collection> 配合 select 属性实现关联查询）。同时附有实际执行 SQL 的注释示例，展示不同日期格式的传参方式。

本文适用于使用 MyBatis 进行数据库操作的 Java 开发者。

批量插入

java
import org.apache.ibatis.annotations.Param;

void insertBatch(@Param("records") List<Order> records);

xml
<insert id="insertBatch">
    <if test="records != null and records.size > 0">
        insert into t_order(title,remark,create_time,create_user_id,create_user_name)
        values
        <foreach collection="records" item="item" separator=",">
            (#{item.title},#{item.remark},#{item.createTime},#{item.createUserId},#{item.createUserName})
        </foreach>
    </if>
</insert>

批量更新

java
import org.apache.ibatis.annotations.Param;

void updateBatch(@Param("records") List<Order> records);

xml
<update id="updateBatch">
    <if test="records != null and records.size > 0">
        <foreach collection="records" item="item" separator=";">
            update t_order
            <set>
                <if test="item.title != null and item.title != ''">
                    title = #{item.title},
                </if>
                <if test="item.remark != null and item.remark != ''">
                    remark = #{item.remark},
                </if>
            </set>
            where id = #{item.id}
        </foreach>
    </if>
</update>

批量删除

java
import org.apache.ibatis.annotations.Param;

void deleteBatch(@Param("ids") List<Long> ids);

xml
<delete id="deleteBatch">
    <if test="ids != null and ids.size > 0">
        delete from t_order where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </if>
</delete>

时间范围查询 Date

java
List<Order> selectByTimeRange(@Param("startTime") Date startTime, @Param("endTime") Date endTime);

xml

<select id="selectByTimeRange" resultType="cn.odboy.dal.dataobject.Order">
    SELECT * FROM orders
    WHERE create_time BETWEEN #{startTime} AND #{endTime}
    ORDER BY create_time DESC
</select>

时间范围查询 LocalDateTime

java
List<Order> selectByLocalDateTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

xml

<select id="selectByLocalDateTimeRange" resultType="cn.odboy.dal.dataobject.Order">
    SELECT * FROM orders
    WHERE create_time BETWEEN #{startTime} AND #{endTime}
    ORDER BY create_time DESC
</select>

时间范围查询 动态Map

java
List<Order> selectByDynamicTimeRange(Map<String, Object> params);

xml

<select id="selectByDynamicTimeRange" resultType="cn.odboy.dal.dataobject.Order">
    SELECT * FROM orders
    <where>
        <if test="startTime != null">
            AND create_time &gt;= #{startTime}
        </if>
        <if test="endTime != null">
            AND create_time &lt;= #{endTime}
        </if>
    </where>
    ORDER BY create_time DESC
</select>

【实际执行SQL】演示：最好转成字符串后再传入Xml中

java
// SELECT * FROM system_user WHERE (create_time > '2025-08-17T16:48:18.400+0800');
systemUserMapper.selectList(new LambdaQueryWrapper<SystemUserTb>().gt(SystemUserTb::getCreateTime, new Date()));

// SELECT * FROM system_user WHERE (create_time > '2025-08-17T16:50:16.786+0800');
systemUserMapper.selectList(new LambdaQueryWrapper<SystemUserTb>().gt(SystemUserTb::getCreateTime, DateTime.now()));

// SELECT * FROM system_user WHERE (create_time > '2025-08-17 16:53:46');
systemUserMapper.selectList(new LambdaQueryWrapper<SystemUserTb>().gt(SystemUserTb::getCreateTime, DateUtil.formatDateTime(new Date())));

// SELECT * FROM system_user WHERE (create_time > '2025-08-17');
systemUserMapper.selectList(new LambdaQueryWrapper<SystemUserTb>().gt(SystemUserTb::getCreateTime, DateUtil.formatDate(new Date())));

使用日期函数进行日期部分比较

java
/**
 * 按日期部分查询（忽略时间部分）
 * @param startDate 开始日期（只包含日期部分）
 * @param endDate 结束日期（只包含日期部分）
 */
List<Order> selectByDatePart(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

xml
<select id="selectByDatePart" resultType="cn.odboy.dal.dataobject.Order">
    SELECT * FROM orders
    WHERE DATE(create_time) BETWEEN #{startDate} AND #{endDate}
    ORDER BY create_time DESC
</select>

如果上面的startDate，含有时间部分，但是只想比较日期部分，可以这么写

xml

<select id="selectByDatePart" resultType="cn.odboy.dal.dataobject.Order">
    SELECT * FROM orders
    WHERE DATE(create_time) BETWEEN DATE(#{startDate}) AND DATE(#{endDate})
    ORDER BY create_time DESC
</select>

一对多嵌套查询

xml
<resultMap id="userWithOrdersMap" type="User">
  <id property="id" column="id"/>
  <result property="name" column="name"/>
  <collection property="orders" ofType="Order" select="selectOrdersByUserId" column="id"/>
</resultMap>

<select id="selectUserWithOrders" resultMap="userWithOrdersMap">
  SELECT id, name FROM users WHERE id = #{id}
</select>

<select id="selectOrdersByUserId" resultType="Order">
  SELECT * FROM orders WHERE user_id = #{userId}
</select>