Mybatis框架进行批量导入和删除有三种方式

首先创建一个数据库

CREATE TABLE user (
  id varchar(32) CHARACTER SET utf8 NOT NULL,
  name varchar(50) CHARACTER SET utf8 DEFAULT NULL ,
  dflag char(1) CHARACTER SET utf8 DEFAULT NULL ,
  PRIMARY KEY (`id`)
) 

 jdbc.properties配置

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://127.0.0.1:3306/qingmu?characterEncoding=utf-8
mysql.username=root
mysql.password=admin
#定义初始连接数
mysql.initialSize=1
#定义最大连接数
mysql.maxActive=20
#定义最大空闲
mysql.maxIdle=20
#定义最小空闲
mysql.minIdle=1
#定义最长等待时间
mysql.maxWait=60000

sqlMapperConfig的配置文件:

<?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="db.properties"></properties>
    <!-- 自动扫描pojo包下的全部类-->
    <typeAliases>
        <package name="com.qingmu.pojo"></package>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <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="UserMapper.xml"></mapper>
    </mappers>
</configuration>

 

第一种:普通的for循环

就是假如说有100条数据,要全部插入到数据库中,可以直接使用for循环进行

其他的配置文件都不用改动,只需要

   @Test
    //for循环插入大量的数据
    public void insertBatch() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user=null;
        for (int i = 9; i < 19; i++) {
            user = new User();
            user.setUsername("关羽");
            user.setSex("男");
            user.setBirthday(new Date());
            user.setAddress("大树楼桑");
            user.setId(i);
            mapper.insertUser(user);
            sqlSession.commit();
        }

    }

第二种需要为openSession添加一个参数:

@Test
public void testInsertBatch2() throws Exception {
    long start = System.currentTimeMillis();
    User user;
    SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//跟上述sql区别
    UserDao mapper = sqlSession.getMapper(UserDao.class);
    for (int i = 0; i < 500; i++) {
        user = new User();
        user.setId("test" + i);
        user.setName("name" + i);
        user.setDelFlag("0");
        mapper.insert(user);
    }
    sqlSession.commit();
    long end = System.currentTimeMillis();
    System.out.println("---------------" + (start - end) + "---------------");
}

mapper的映射文件不需要进行更改

第三种就是使用foreach标签

<insert id="insertBatch">
    INSERT INTO t_user
            (id, name, dflag)
    VALUES
    <foreach collection ="list" item="user" separator =",">
         (#{user.id}, #{user.name}, #{user.dFlag})
    </foreach >
</insert>

@Test
public void testInsertBatch() throws Exception {

    List<User> list = new ArrayList<>();
    User user;
    for (int i = 0; i < 10000; i++) {
        user = new User();
        user.setId("test" + i);
        user.setName("name" + i);
        user.setDelFlag("0");
        list.add(user);
    }
    userService.insertBatch(list);
}

特别注意：mysql默认接受sql的大小是1048576(1M)，即第三种方式若数据量超过1M会报如下异常：（可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的＂max_allowed_packet = 1M＂）

nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576).

You can change this value on the server by setting the max_allowed_packet' variable.

总结:使用动态sql拼出来的批量导入用时最少,而且效率高,其他两种,效率在数据量较大的情况下的时候,性能较差.