spring mybatis更新几种操作

时间:2021-1-18 作者:admin

spring mybatis更新几种操作

简述

在日常编程中难免会有批量操作,实现物理批量操作框架有有不少,比如mybatis plus,还有提及一些比如跨数据库操作也有操作的中间件比如阿里的ADB,还有apache开源的shardingsphere,哈哈,这都是微服务时代产生的产品,这里不多说。这里主要讲解两种实现批量更新,在学习之前,建议了解一下AOP和mybait的plugin原理intercepter
1、mybatis拦截器实现批量操作
2、mybatis循环,利用case when实现批量操作

代码实践

拦截器批量操作

1、编写批量操作类
在类中继承mybatis自带的批量操作类

package com.lgh.batch.inteceptor;

import org.apache.ibatis.executor.BatchExecutor;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.transaction.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * 批量操作
 *
 * @author guohu
 */
final class BatchExecutorAdaptor extends BatchExecutor {
    private Logger log = LoggerFactory.getLogger(BatchExecutorAdaptor.class);

    public BatchExecutorAdaptor(Configuration configuration, Transaction transaction) {
        super(configuration, transaction);
    }

    @Override
    public int update(MappedStatement ms, Object parameter) throws SQLException {
        if (parameter == null) {
            super.update(ms, parameter);
        }
        final Object params;
        if (parameter instanceof Map) {
            final Map<String, Object> paramMap = (Map<String, Object>) parameter;
            if (paramMap == null || paramMap.size() != 1) {
                if (paramMap.size() == 2 && paramMap.get("collection") != null) {
                    params = paramMap.get("collection");
                } else if (!paramMap.containsKey("param1")) {
                    return super.update(ms, parameter);
                } else {
                    params = paramMap.get("param1");
                }
            } else {
                params = paramMap.values().iterator().next();
            }
        } else if (parameter instanceof Iterable || parameter.getClass().isArray()) {
            params = parameter;
        } else {
            params = Collections.singletonList(parameter);
        }

        final Iterable<?> paramIterable = toIterable(params);
        try {
            for (Object obj : paramIterable) {
                super.update(ms, obj);
            }
            List<BatchResult> batchResults = doFlushStatements(false);
            if (batchResults == null || batchResults.size() == 0) {
                return 0;
            }
            return resolveUpdateResult(batchResults);
        } catch (Exception e) {
            log.error("batch execute", e);
            doFlushStatements(true);
            /**
             * 批量插入,则报异常
             */
            if ("INSERT".equalsIgnoreCase(ms.getSqlCommandType().name())) {
                throw e;
            }
            return 0;
        }
    }


    private Iterable<?> toIterable(final Object params) {
        if (params == null) {
            return Collections.emptyList();
        }
        Iterable<?> paramIterable;
        if (params instanceof Iterable) {
            paramIterable = (Iterable<?>) params;
        } else if (params.getClass().isArray()) {
            Object[] array = (Object[]) params;
            paramIterable = Arrays.asList(array);
        } else {
            paramIterable = Collections.singletonList(params);
        }
        return paramIterable;
    }

    private int resolveUpdateResult(final List<BatchResult> batchResults) {
        int result = 0;
        for (BatchResult batchResult : batchResults) {
            int[] updateCounts = batchResult.getUpdateCounts();
            if (updateCounts == null || updateCounts.length == 0) {
                continue;
            }
            for (int updateCount : updateCounts) {
                result += updateCount;
            }
        }
        return result;
    }

}

批量拦截器

实现批量操作类之后,当然是注入拦截器啦。

package com.lgh.batch.inteceptor;

import com.lgh.batch.utils.ExecutorUtil;
import com.lgh.batch.utils.Reflection;
import org.apache.ibatis.executor.BatchExecutor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.Configuration;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.SQLException;
import java.util.Properties;

/**
 * 批量操作拦截器,以Batch结束语句
 *
 * @author guohu
 */
@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
})
public class BatchExecutorInterceptor implements Interceptor {

    private static final Logger LOGGER = LoggerFactory.getLogger(BatchExecutorInterceptor.class);

    @Override
    public Object intercept(final Invocation invocation) throws Throwable {
        //check argument
        if (invocation.getArgs()[1] == null) {
            return invocation.proceed();
        }
        final MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        // 是否需要批处理标识
        if (!mappedStatement.getId().endsWith("Batch")) {
            return invocation.proceed();
        }
        // 找到执行对象
        final Executor targetExecutor = ExecutorUtil.getTargetExecutor((Executor) invocation.getTarget());
        // 若是批处理,则不做操作
        if (targetExecutor instanceof BatchExecutor) {
            return invocation.proceed();
        }
        // 获取配置文件
        final Configuration configuration = (Configuration) Reflection.getField("configuration", targetExecutor);
        // 创建批处理对象
        final BatchExecutor batchExecutor = new BatchExecutorAdaptor(configuration, targetExecutor.getTransaction());
        try {
            return batchExecutor.update(mappedStatement, invocation.getArgs()[1]);
        } catch (SQLException e) {
            LOGGER.error("batch excute", e);
            batchExecutor.flushStatements(true);
            throw e;
        }
    }

    @Override
    public Object plugin(final Object target) {
        if (!(target instanceof Executor)) {
            return target;
        }
        if (target instanceof BatchExecutor) {
            return target;
        }

        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(final Properties properties) {
    }
}

Configuration注入拦截器

  @Bean
    public Interceptor myBatchInterceptor() {
        return new BatchExecutorInterceptor();
    }

测试sql

<?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.lgh.mapper.UserTableMapper" >
    <update id="updateBatch" parameterType="java.util.List">
        update user set name=#{name} where id=#{id}
    </update>
</mapper>

mapper对象

package com.lgh.mapper;

import com.lgh.entity.UserTable;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserTableMapper {

    List<UserTable> findAll();
    int updateBatch(List<UserTable> userTableList);

实体操作对象

package com.lgh.entity;

public class UserTable {
    private String id;
    private String name;
    private String msg;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }
}

测试类

package com.lgh;

import com.lgh.entity.UserTable;
import com.lgh.mapper.UserTableMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.ArrayList;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class RunApplicationTest {
    @Autowired
    private UserTableMapper userTableMapper;

    @Test
    public void testSql() {
        List<UserTable> list = new ArrayList<>();
        UserTable table1 = new UserTable();
        table1.setId("1");
        table1.setName("张三1");
        UserTable table2 = new UserTable();
        table2.setId("2");
        table2.setName("张三2");
        UserTable table3 = new UserTable();
        table3.setId("3");
        table3.setName("张三3");
        list.add(table1);
        list.add(table2);
        list.add(table3);
        userTableMapper.updateBatch(list);
    }
}

查看运行结果可知,只是执行一次update而已,实现物理批量操作
spring mybatis更新几种操作

利用mybatis实现批量

  <update id="updateByList" parameterType="list">
        update user
        set name = CASE
            <foreach collection="users" item="user">
                WHEN id = #{user.id} THEN #{user.name}
            </foreach>
          END
        where id IN
        <foreach collection="users" item="user" separator="," open="(" close=")">
            #{user.id}
        </foreach>
    </update>

执行结果

2021-01-16 20:17:16.877 DEBUG 11388 --- [           main] c.l.mapper.UserTableMapper.updateByList  : ==>  Preparing: update user set name = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? END where id IN ( ? , ? , ? ) 
2021-01-16 20:17:16.921 DEBUG 11388 --- [           main] c.l.mapper.UserTableMapper.updateByList  : ==> Parameters: 1(String), 张三1(String), 2(String), 张三2(String), 3(String), 张三3(String), 1(String), 2(String), 3(String)
2021-01-16 20:17:16.928 DEBUG 11388 --- [           main] c.l.mapper.UserTableMapper.updateByList  : <==    Updates: 3

源码

github

参考文献
sql批量

声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎进行举报,并提供相关证据,工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。