MyBatis XML映射文件中的批量操作详解

无界猴

4个月前更新

079854

MyBatis提供了多种方式来实现批量插入和更新操作，可以显著提高数据库操作的效率。下面我将详细介绍各种批量操作的方式及其实现方法。

一、批量插入操作

1. 使用foreach标签实现批量插入

这是最常用的批量插入方式，通过动态SQL的foreach标签遍历集合：

<insert id="batchInsert" parameterType="java.util.List">
    INSERT INTO user (name, age, email)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.age}, #{item.email})
    </foreach>
</insert>

特点：

生成单个INSERT语句包含多组VALUES
MySQL等数据库支持，但Oracle有1000条限制
需要配置allowMultiQueries=true(MySQL)

2. 使用BatchExecutor执行多次插入

通过SqlSession的批量模式执行：

try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    for (User user : userList) {
        mapper.insert(user);
    }
    sqlSession.commit();
}

特点：

实际生成多个INSERT语句
JDBC驱动会批量发送到数据库
需要手动控制事务

3. 多行VALUES语法（MySQL特有）

<insert id="batchInsert">
    INSERT INTO user (name, age, email) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.age}, #{item.email})
    </foreach>
    ON DUPLICATE KEY UPDATE
    age = VALUES(age),
    email = VALUES(email)
</insert>

二、批量更新操作

1. 单字段批量更新

<update id="batchUpdateStatus">
    UPDATE user SET status = #{status} 
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</update>

2. 多条件批量更新（MySQL CASE WHEN语法）

<update id="batchUpdate">
    UPDATE user
    SET
    name = CASE id
        <foreach collection="list" item="item">
            WHEN #{item.id} THEN #{item.name}
        </foreach>
    END,
    age = CASE id
        <foreach collection="list" item="item">
            WHEN #{item.id} THEN #{item.age}
        </foreach>
    END
    WHERE id IN
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</update>

3. 使用UNION ALL模拟批量更新（Oracle等）

<update id="batchUpdateOracle">
    UPDATE (
        SELECT 
            t.id,
            t.name as old_name,
            t.age as old_age,
            s.new_name,
            s.new_age
        FROM user t
        JOIN (
            <foreach collection="list" item="item" separator="UNION ALL">
                SELECT 
                    #{item.id} as id,
                    #{item.name} as new_name,
                    #{item.age} as new_age
                FROM dual
            </foreach>
        ) s ON t.id = s.id
    ) 
    SET name = new_name, age = new_age
</update>

三、批量操作的性能优化建议

合理设置批量大小：
- MySQL建议每批1000条左右
- Oracle建议每批500条左右
事务控制： // Spring事务管理 @Transactional public void batchInsert(List<User> users) { // 分批处理 int batchSize = 1000; for (int i = 0; i < users.size(); i += batchSize) { List<User> subList = users.subList(i, Math.min(i + batchSize, users.size())); userMapper.batchInsert(subList); } }
JDBC参数优化： # 在mybatis配置中 rewriteBatchedStatements=true useServerPrepStmts=true cachePrepStmts=true prepStmtCacheSize=250 prepStmtCacheSqlLimit=2048
索引优化：
- 确保批量操作的WHERE条件字段有索引
- 批量插入时考虑临时禁用索引

四、特殊数据库的批量操作

1. PostgreSQL批量插入

<insert id="batchInsertPostgreSQL">
    INSERT INTO user (name, age, email)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.age}, #{item.email})
    </foreach>
    ON CONFLICT (id) DO UPDATE
    SET name = EXCLUDED.name,
        age = EXCLUDED.age,
        email = EXCLUDED.email
</insert>

2. SQL Server MERGE语法

<update id="batchUpsertSQLServer">
    MERGE INTO user AS target
    USING (
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.id}, #{item.name}, #{item.age}, #{item.email})
        </foreach>
    ) AS source (id, name, age, email)
    ON target.id = source.id
    WHEN MATCHED THEN
        UPDATE SET 
            target.name = source.name,
            target.age = source.age,
            target.email = source.email
    WHEN NOT MATCHED THEN
        INSERT (id, name, age, email)
        VALUES (source.id, source.name, source.age, source.email);
</update>

五、常见问题解决方案

超长SQL问题：
- 分批次执行（每批500-1000条）
- 调整数据库的max_allowed_packet参数
主键冲突处理：
- 使用ON DUPLICATE KEY UPDATE（MySQL）
- 使用MERGE语句（Oracle/SQL Server）
- 先查询再决定插入或更新
事务超时问题： @Transactional(timeout = 120) // 设置更长的事务超时 public void largeBatchOperation() { // 批量操作 }