Mybatis批量插入功能

数据库的结构如下所示

使用insert标签与foreach标签实现批量插入功能。

测试插入10000条数据的时间，测试代码如下，时间959ms。

public class Test {
    public  static  void main(String[] args) throws Exception {
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class);

        List<Account> accounts = new ArrayList<>();
        for (int i = 0; i < 10000; i++){
            accounts.add(new Account("admin" +i, "password", "test"));
        }

        Date date = new Date();
        accountMapper.insertBatch(accounts);

        /*for (int i = 0; i < 10000; i++){
            accountMapper.insert(accounts.get(i));
        }*/
        sqlSession.commit();
        System.out.println(new Date().getTime() - date.getTime());
        sqlSession.close();
    }

}

 mapper.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">
<mapper namespace="com.hyself.icake.dao.AccountMapper">
<insert id="insertBatch" useGeneratedKeys="true">
    INSERT INTO account(account, password, nick_name) VALUES
    <foreach collection="accounts" item="account" separator=",">
        (#{account.account},#{account.password},#{account.nickname})
    </foreach>
</insert>
</mapper>

从上面可以xml文件可以看出，此功能其实是使用了INSERT INTO ... VALUES (),(),()...功能，Mybatis还支持批量提交插入语句，原理是实现了N条INSERT INTO ... VALUES (); INSERT INTO ... VALUES()...;...

mapper.xml的配置如下，可以看到分隔符的配置改成了"；",这个地方也可以不配置分隔符，直接在INSERT INTO语句的最后加上分号。除了在XML中设置，还需要在在数据库的url后面加上允许提交多个语句的配置（?allowMultiQueries=true），否则提交会失败。

<?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.hyself.icake.dao.AccountMapper">
    <insert id="insertBatch2" useGeneratedKeys="true">
        <foreach collection="accounts" item="account" separator=";">
            INSERT INTO account(account, password, nick_name) VALUES (#{account.account},#{account.password},#{account.nickname})
        </foreach>
    </insert>
</mapper>

使用openSession(ExecutorType.BATCH);去实现批量插入

代码如下，测试时间为2900ms

public class Test {
    public  static  void main(String[] args) throws Exception {
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class);

        List<Account> accounts = new ArrayList<>();
        for (int i = 0; i < 10000; i++){
            accounts.add(new Account("admin" +i, "password", "test"));
        }

        /*accountMapper.insertBatch(accounts);*/
        Date date = new Date();
        for (int i = 0; i < 10000; i++){
            accountMapper.insert(accounts.get(i));
        }
        sqlSession.commit();
        System.out.println(new Date().getTime() - date.getTime());
        sqlSession.close();
    }

}

 mapper.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">
<mapper namespace="com.hyself.icake.dao.AccountMapper">
    <insert id="insert">
        INSERT INTO account(account, password, nick_name) VALUES
            (#{account.account},#{account.password},#{account.nickname})
    </insert>
    
</mapper>

 

但是insert标签与foreach标签的办法因为数字符串拼接，所以当个数太大的时候，程序会报异常。

备注：mapper文件的配置如下。

<?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.hyself.icake.dao.AccountMapper">
<insert id="insertBatch" useGeneratedKeys="true" keyProperty="">
    INSERT INTO account(account, password, nick_name) VALUES
    <foreach collection="accounts" item="account" separator=",">
        (#{account.account},#{account.password},#{account.nickname})
    </foreach>
</insert>
<insert id="insert">
    INSERT INTO account(account, password, nick_name) VALUES
     (#{account.account},#{account.password},#{account.nickname})
</insert>


</mapper>