mapper批量插入

1.常规方式的批量插入

sql语句

int bathNorm(List<Teacher> teacherList);

<insert id="bathNorm" parameterType="Teacher">
        insert into teacher (tname,age) values
        <foreach collection="list" item="teacher" separator="," close=";">
            (#{teacher.tname},#{teacher.age})
        </foreach>
    </insert>

java代码

StudentDao studentDao=null;
    String[] tnameArr = new String[]{"火", "水", "金", "木", "土"};
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);
         }
     }

test类

@test
  public void testbathbrom(){
         long start =System.currentTimeMillis();
         int rows=teacherDao.bathNorm(testData);
        System.out.println("插入数据行数："+rows+"耗时："+(System.currentTimeMillis()-start));
    }

数据源

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

xml配置

<?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 resource="jdbc.properties" />
    <!--    类型别名是为 Java 类型设置一个短的名字。 它只和 XML 配置有关，存在的意义仅在于用来减少类完全限定名的冗余-->
    <typeAliases>
        <typeAlias type="com.lanou3g.mybaties.bean.Teacher" alias="Teacher" />
        <typeAlias type="com.lanou3g.mybaties.bean.Student" alias="Student"/>
     </typeAliases>
    <!-- 配置不同环境的参数 -->
    <environments default="development">
        <!-- 开发环境数据库、事务配置 -->
        <environment id="development">
            <!-- 事务管理使用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>

配置的类

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("mybaties.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);
    }
}

/**
 * 继承原生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);
    }
}

teacher类

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 "Teacher{" +
                "id=" + id +
                ", tname='" + tname + '\'' +
                ", age=" + age +
                "}\n";
    }
}

这种方式比较快，但是数据库默认上传数据是4M，超过就要修改数据库的默认值，比较有局限性。

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

配置如上
sql语句

<insert id="insertTeacher" parameterType="Teacher">
       insert into teacher(tname) values (#{tname});
    </insert>

java类

 @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) {
            rows += teacherDao.insertTeacher(teacher);
            count ++;
            if(count % batchSize == 0) {
                sqlSession.flushStatements();
            }
        }
        sqlSession.flushStatements();
        sqlSession.commit();
        sqlSession.close();
        System.out.println(("插入数据行数： " + rows+", 耗时： " + (System.currentTimeMillis() - start)));
    }

这种插入的量没有限制，但是插入的比常规方式的慢。