Mybatis批量插入数据到数据库

Mybatis批量插入数据到数据库

常规方式(foreach)批量插入

数据库里的Teacher表有如下字段：id，tname,age。
创建与数据库表对应的实体类Teacher.java

package com.lanou3g.mybatis.bean;

import lombok.Getter;
import lombok.Setter;

import java.util.List;

@Getter
@Setter
public class Teacher {
    private Integer id;
    private String tname;
    private Integer age;

    public Teacher (){}

    public Teacher(String tname) {
        this.tname = tname;
    }

    @Override
    public String toString() {
        return "{" +
                "id:" + id +
                ", tname:'" + tname + '\'' +
                ", age:'" + age + '\'' +
                "]}\n";
    }
}

dao层接口TeacherDao

package com.lanou3g.mybatis.dao;

import com.lanou3g.mybatis.bean.Teacher;

import java.util.List;

public interface TeacherDao {

    int batchInsertByNormal(List<Teacher> teacherList);
}

数据库相关外部配置文件jdbc.properties

jdbc.url=jdbc:mysql://localhost:3306/yanfa5?characterEncoding=utf8
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root

Mybatis的全局配置文件mybatis_conf.xml
主要用于配置Mybatis的运行环境（事务管理器、数据源等）。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 引入外部的properties文件，只能引入一个 -->
    <properties resource="jdbc.properties" />
    
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    
    <!-- 定义类型别名（全局），在所有的Mapper.xml中都可以用 -->
    <typeAliases>
        <typeAlias type="com.lanou3g.mybatis.bean.Teacher" alias="Teacher" />
    </typeAliases>
    <!-- 配置多套环境的数据库连接参数（如：开发环境、生产环境） -->
    <environments default="yanfa5">
    <environment id="yanfa5">
        <!-- 事务管理器：
            MANAGED: 这个配置就是告诉mybatis不要干预事务，具体行为依赖于容器本身的事务处理逻辑。
            JDBC: 这个配置就是直接使用了 JDBC 的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作用域。
         -->
        <transactionManager type="MANAGED"/>
        <dataSource type="POOLED">
            <property name="driver" value="${jdbc.driver}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.user}"/>
            <property name="password" value="${jdbc.password}"/>
        </dataSource>
    </environment>
</environments>
    <mappers>
        <mapper resource="mapper/TeacherMapper.xml" />
    </mappers>
</configuration>

SQL配置文件TeacherMapper.xml

<?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">


<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.TeacherDao">

    <resultMap id="teacher" type="Teacher">
         <id column="t_id" property="id" />
        <result column="tname" property="tname" />
        <result column="age" property="age" />
    </resultMap>
    <!-- 常规方式批量插入 -->
    <insert id="batchInsertByNormal" parameterType="Teacher">
        insert into teacher (tname, age) values
        <!--open属性表示在拼接的SQL语句前加"xx",separator属性表示在拼接的SQL语句之间加"xx"，close属性表示在拼接的SQL语句后加"xx"-->
        <foreach collection="list" item="teacher" separator="," close=";">
            (#{teacher.tname}, #{teacher.age})
        </foreach>
    </insert>
</mapper>

工具类MyBatisTools.java
封装Mybatis初始化操作

package com.lanou3g.mybatis;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 封装Mybatis初始化操作
 * 支持创建多env sqlSessionFactory
 * 整个应用生命周期内相同env的sqlSessionFactory对象只有一个
 */
@Slf4j
public class MyBatisTools {

    private static ConcurrentHashMap<String, SqlSessionFactory> factoryMap = new MyConcurrentHashMap();

    private static MyBatisTools myBatisTools;

    private MyBatisTools() {}

    public static MyBatisTools getInstance() {
        if(myBatisTools == null) {
            synchronized (MyBatisTools.class) {
                if(myBatisTools == null) {
                    myBatisTools = new MyBatisTools();
                }
            }
        }
        log.debug("当前一共有: " + factoryMap.size() +"个SqlSessionFactory实例");
        log.debug("他们分别是: " + factoryMap);
        return myBatisTools;
    }

    public SqlSessionFactory getSessionFactory() {
        return getSessionFactory(null);
    }

    public SqlSessionFactory getSessionFactory(String env) {
        try {
            // 1. 读入配置文件
            InputStream in = Resources.getResourceAsStream("mybatis_conf.xml");
            // 2. 构建SqlSessionFactory(用于获取sqlSession)
            SqlSessionFactory sessionFactory = null;
            synchronized (factoryMap) {
                if(factoryMap.containsKey(env)) {
                    return factoryMap.get(env);
                } else {
                    sessionFactory = new SqlSessionFactoryBuilder().build(in, env);
                    factoryMap.put(env, sessionFactory);
                }
            }
            return sessionFactory;
        } catch (Exception e) {
            log.error("初始化SqlSessionFactory失败", e);
            return null;
        }
    }

    public SqlSession openSession() {
        return getSessionFactory(null).openSession();
    }

    public SqlSession openSession(boolean autoCommit) {
        return getSessionFactory(null).openSession(autoCommit);
    }

    public SqlSession openSession(ExecutorType executorType, boolean autoCommit) {
        return getSessionFactory(null).openSession(executorType, autoCommit);
    }

    public static int flushStatement(SqlSession sqlSession) {
        int effectRows = 0;
        List<BatchResult> batchResults = sqlSession.flushStatements();
        if(batchResults == null || batchResults.size() < 1) {
            return effectRows;
        }
        int[] effectArr = batchResults.get(0).getUpdateCounts();
        for(int effectRow : effectArr) {
            effectRows += effectRow;
        }
        return effectRows;
    }

}

/**
 * 继承原生ConcurrentHashMap，处理null key问题
 */
class MyConcurrentHashMap extends ConcurrentHashMap {
    @Override
    public Object put(Object key, Object value) {
        if(key == null) {
            key = "null";
        }
        return super.put(key, value);
    }

    @Override
    public boolean containsKey(Object key) {
        if(key == null) {
            key = "null";
        }
        return super.containsKey(key);
    }

    @Override
    public Object get(Object key) {
        if(key == null) {
            key = "null";
        }
        return super.get(key);
    }
}

入口类AppTest.java

@Slf4j
public class AppTest {

    TeacherDao teacherDao = null;
    List<Teacher> testData = new ArrayList<>();
    String[] tnameArr = new String[]{"四火", "四水", "四金", "四木", "四土"};
	<!--@Before注解表示下面的test单元测试在执行之前都会先执行添加@Before注解的方法-->
    @Before
    public void setUp() {
        teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
        Random random = new Random();
        // 构造测试数据
        for(int i = 0; i < 10000; i++) {
            Teacher teacher = new Teacher();
            int idx = random.nextInt(tnameArr.length);
            teacher.setTname(tnameArr[idx] +"_"+ (i + 1));
            teacher.setAge(i+1);
            testData.add(teacher);
        }
    }

 /**
     * 常规批量插入。（通过foreach，生成很长的SQL）
     */
    @Test
    public void testBatchInsertByNormal() {

        long start = System.currentTimeMillis();
        int rows = teacherDao.batchInsertByNormal(testData);
        log.info("插入数据行数： " + rows+", 耗时： " + (System.currentTimeMillis() - start));
    }
    }

使用ExecutorType.BATCH方式执行批量操作

创建与数据库表对应的实体类Teacher.java(与foreach方式批量插入的实体类相同)

dao层接口TeacherDao

package com.lanou3g.mybatis.dao;

import com.lanou3g.mybatis.bean.Teacher;

import java.util.List;

public interface TeacherDao {

   int insertTeacher(Teacher teacher);
}

数据库相关外部配置文件jdbc.properties(与foreach方式批量插入的配置文件相同)

Mybatis的全局配置文件mybatis_conf.xml(与foreach方式批量插入的配置文件相同)
主要用于配置Mybatis的运行环境（事务管理器、数据源等）。

SQL配置文件TeacherMapper.xml

<?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">


<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.TeacherDao">

    <resultMap id="teacher" type="Teacher">
         <id column="t_id" property="id" />
        <result column="tname" property="tname" />
        <result column="age" property="age" />
    </resultMap>
     <insert id="insertTeacher" parameterType="Teacher">
       insert into teacher(tname,age) values (#{tname},#{age});
    </insert>
</mapper>

工具类MyBatisTools.java(与foreach方式批量插入的工具类相同)
作用：封装Mybatis初始化操作

入口类AppTest.java

@Slf4j
public class AppTest {

    TeacherDao teacherDao = null;
    List<Teacher> testData = new ArrayList<>();
    String[] tnameArr = new String[]{"四火", "四水", "四金", "四木", "四土"};

    @Before
    public void setUp() {
        teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
        Random random = new Random();
        // 构造测试数据
        for(int i = 0; i < 10000; i++) {
            Teacher teacher = new Teacher();
            int idx = random.nextInt(tnameArr.length);
            teacher.setTname(tnameArr[idx] +"_"+ (i + 1));
            teacher.setAge(i+1);
            testData.add(teacher);
        }
    }
/**
     * 使用ExecutorType.BATCH方式执行批量操作
     */
    @Test
    public void testBatchInsertByExecutorType() {
        SqlSessionFactory factory = MyBatisTools.getInstance().getSessionFactory();
        SqlSession sqlSession = factory.openSession(ExecutorType.BATCH, false);
        TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
        long start = System.currentTimeMillis();
        int rows = 0;
        int batchSize = 100;
        int count = 0;
        for(Teacher teacher : testData) {
            teacherDao.insertTeacher(teacher);
            count ++;
            if(count % batchSize == 0) {
                rows+=MyBatisTools.flushStatement(sqlSession);
            }
        }
        sqlSession.flushStatements();
        sqlSession.commit();
        sqlSession.close();
        log.info("插入数据行数： " + rows+", 耗时： " + (System.currentTimeMillis() - start));
    }
}