Mybatis 批量执行

一、使用动态SQL 中的 Foreach 批量插入

1.MySQL

// 实体类

public class MyUser {
    private Integer id;
    private String name;
    private Integer age;
    private Dept dept;


public class Dept {
    private Integer id;
    private String name;
    private List<MyUser> myUsers;

SQL

<!-- 一条 SQL -->
<!--public Boolean addMyUsers(@Param("users") List<MyUser> users);-->
<insert id="addMyUsers">
    insert into myuser(name,age,did) values
    <foreach collection="users" item="user" separator=",">
      (#{user.name},#{user.age},#{user.dept.id})
    </foreach>
</insert>

<!-- 多条 SQL -->
<!-- 一次执行多条 SQL 需在 JDBC 数据库连接属性添加 allowMultiQueries=true -->
<!--public Boolean addMyUsers(List<MyUser> users);-->
<insert id="addMyUsers">
    <foreach collection="list" item="user" separator=";">
        insert into myuser(name,age,did) values (#{user.name},#{user.age},#{user.dept.id})
    </foreach>
</insert>

测试代码

/**
 * driver=com.mysql.cj.jdbc.Driver
 * url=jdbc:mysql://192.168.8.136:3306/mybatis?allowMultiQueries=true
 * username=root
 * password=root
 */
public static void main(String[] args) {
    SqlSession session = null;
    try {
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        session = sqlSessionFactory.openSession();

        MyUserMapper mapper = session.getMapper(MyUserMapper.class);

        Dept dept = new Dept(2,null);
        MyUser myUser1 = new MyUser(null,"xsa",34,dept);
        MyUser myUser2 = new MyUser(null,"fgb",24,dept);
        MyUser myUser3 = new MyUser(null,"wdx",18,dept);
        List<MyUser> list = new ArrayList<>();
        list.add(myUser1);
        list.add(myUser2);
        list.add(myUser3);
        mapper.addMyUsers(list);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (session != null) {
            session.close();
        }
    }
}

2.Oracle

<!-- Oracle数据库批量保存，Oracle不支持 values(),(),()
    1、多个insert放在begin - end里面
        begin
            insert into employees(employee_id,last_name,email)
            values(employees_seq.nextval,'test_001','test_001@atguigu.com');
            insert into employees(employee_id,last_name,email)
            values(employees_seq.nextval,'test_002','test_002@atguigu.com');
        end;
    2、利用中间表：
        insert into employees(employee_id,last_name,email)
           select employees_seq.nextval,lastName,email from(
                  select 'test_a_01' lastName,'test_a_e01' email from dual
                  union
                  select 'test_a_02' lastName,'test_a_e02' email from dual
                  union
                  select 'test_a_03' lastName,'test_a_e03' email from dual
           ) -->
<insert id="addEmps" databaseId="oracle">
    <!-- oracle第一种批量方式 -->
    <foreach collection="emps" item="emp" open="begin" close="end;">
        insert into employees(employee_id,last_name,email) values(employees_seq.nextval,#{emp.lastName},#{emp.email});
    </foreach>

    <!-- oracle第二种批量方式  -->
    insert into employees(employee_id,last_name,email)
    <foreach collection="emps" item="emp" separator="union" open="select employees_seq.nextval,lastName,email from(" close=")">
        select #{emp.lastName} lastName,#{emp.email} email from dual
    </foreach>
</insert>
<sql id="insertColumn">
    <if test="_databaseId=='oracle'">
        employee_id,last_name,email
    </if>
    <if test="_databaseId=='mysql'">
        last_name,email,gender,d_id
    </if>
</sql>

 

二、使用 Mybatis 的批量执行器

1.单独使用

public static void main(String[] args) {
    SqlSession session = null;
    try {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        // 获取批量执行器，设置不自动提交（默认 false）
        session = sqlSessionFactory.openSession(ExecutorType.BATCH,false);

        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();

        long start = System.currentTimeMillis();
        for (int i = 0; i < 1000; i++) {
            user.setName(UUID.randomUUID().toString());
            user.setAge(25);
            mapper.insertUser(user);
        }
        long end = System.currentTimeMillis();

        //批量：预编译sql一次 ==> 设置参数（1000次）===> 执行（1次）===> 执行时长：889
        //非批量：（预编译sql=设置参数=执行）（1000次） ===> 执行时长：8812
        System.out.println("执行时长："+(end-start));

        // 使用 JDBC 事务需管理要手动提交事务
        session.commit();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (session != null) {
            session.close();
        }
    }
}

 

2.在 SSM 中使用

首先给容器中添加一个可批量执行的 SqlSession，两种方式

xml 方式

<!--配置一个可以进行批量执行的sqlSession  -->
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactoryBean"></constructor-arg>
    <constructor-arg name="executorType" value="BATCH"></constructor-arg>
</bean>

代码方式

/**
 * 配置一个可以进行批量执行的 sqlSession
 */
@Bean
public SqlSessionTemplate getSqlSessionTemplate(SqlSessionFactoryBean sqlSessionFactoryBean) throws Exception {
    /**
     * Simple Executor -- SIMPLE 普通的执行器，默认
     * Reuse Executor -执行器会重用预处理语句（prepared statements）
     * Batch Executor --批量执行器
     */
    SqlSessionTemplate sessionTemplate = new SqlSessionTemplate(sqlSessionFactoryBean.getObject(), ExecutorType.BATCH);
    return sessionTemplate;
}

使用批量执行，在 service 中注入即可

// @Autowired
// private SqlSessionTemplate sqlSessionTemplate;
@Autowired
private SqlSession sqlSession;

public List<Employee> getEmps(){
    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    return employeeMapper.getEmps();
}

 

---

https://www.cnblogs.com/jhxxb/p/10451387.html