MyBatis与传统jdbc批量新增数据对比

传统JDBC批量插入方法：

1.Java代码中使用For循环直接插入SQL数据如：execute()或executeUpdate()方法。
2.借助于Statement、Prestatement对象的批处理方法addBatch

public class jdbcUtil {
    // 处理数据库事务，批量操作需要手动提交事务
    public static void commit(Connection connection){
        if (null!=connection){
            try {
                connection.commit();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }

    //事务的回滚
    public static void rollback(Connection connection){
        if (null!=connection){
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 事务的开始
    public static void begin(Connection connection) {
        if (null != connection) {
            try {
                connection.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
     /**
      * 获取连接方法
      * @Param
      * @Return
      */
     public static Connection getConnection() throws Exception {
         Properties properties = new Properties();
         InputStream is = jdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
         //jdbc文件内容以 流 的方式加载到properties文件中
         properties.load(is);
         String driver=properties.getProperty("driver");
         String username=properties.getProperty("username");
         String password=properties.getProperty("password");
         String url=properties.getProperty("url");
         System.out.println(driver+":"+password);
         Class.forName(driver);

         return DriverManager.getConnection(url,username,password);
     }
     /**
      * 通用的关闭资源的方法
      * @Param connection
      * @Param statement
      * @Param resultSet
      */
     public static void closeResources(Connection connection, Statement statement, ResultSet resultSet){
        if (null!=connection){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (null!=statement){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (null!=resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
     }
}

使用for循环插入

public class BatchTestOne {
    public static void main(String[] args) throws Exception {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        connection = jdbcUtil.getConnection();
        jdbcUtil.begin(connection); //autocommit false

        String sql = "insert into t_user(username,password) values(?,?)";
        preparedStatement=connection.prepareStatement(sql);

        long beginTime = System.currentTimeMillis();
        for (int i=0;i<10000;i++){
            preparedStatement.setString(1,"user"+(i+1));
            preparedStatement.setString(2,"pwd"+(i+1));
            preparedStatement.executeUpdate();
        }

        jdbcUtil.commit(connection);
        long endTime = System.currentTimeMillis();
        System.out.println("total time:"+(endTime-beginTime));//4150
    }
}

使用addBatch批处理方式插入

public class BatchTestTwo {
    public static void main(String[] args) throws Exception {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        connection = jdbcUtil.getConnection();
        jdbcUtil.begin(connection);

        String sql = "insert into t_user(username,password) values(?,?)";
        preparedStatement=connection.prepareStatement(sql);

        long beginTime = System.currentTimeMillis();
        for (int i=0;i<10000;i++){
            preparedStatement.setString(1,"batch"+(i+1));
            preparedStatement.setString(2,"num"+(i+1));
            preparedStatement.addBatch();

            if ((i+1)%1000==0){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }

        jdbcUtil.commit(connection);
        long endTime = System.currentTimeMillis();
        System.out.println("total time:"+(endTime-beginTime));//1817
    }
}

可以看出：使用批处理的方式要比普通的for循环要快很多。mybatis封装了jdbc，所以mybatis的批处理方式类似于addBatch

MyBatis进行批量插入的方法

MySQL添加多条数据的方式

insert into person(username,email,gender) VALUES("zhangsan","zhangsan@163.com","F"),("lisi","lisi@163.com","F")
或者
insert into person(username,email,gender) VALUES("tom","zhangsan@163.com","F");
insert into person(username,email,gender) VALUES("jerry","lisi@163.com","F")

1.借助foreach标签使用 insert into table values

public class Person {
    private Integer id;

    private String username;

    private String email;

    private String gender;

    public Person(String username, String email, String gender) {
        this.id = id;
        this.username = username;
        this.email = email;
        this.gender = gender;
    }
}

 

public interface PersonMapper {
    void addPersons(@Param("persons") List<Person> persons);
}

 

<?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="mybatis.dao.PersonMapper" >
    <resultMap id="BaseResultMap" type="mybatis.bean.Person" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="username" property="username" jdbcType="VARCHAR" />
        <result column="email" property="email" jdbcType="VARCHAR" />
        <result column="gender" property="gender" jdbcType="VARCHAR" />
    </resultMap>
    
    <insert id="addPersons">
      insert into person(username,email,gender) VALUES
      <foreach collection="persons" item="person" separator=",">
        (#{person.username},#{person.email},#{person.gender})
      </foreach>
    </insert>

</mapper>

 

<?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>
    <!-- 加入DB配置文件 -->
    <properties resource="jdbc.properties"></properties>

    <!-- 配置配置项 -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="jdbcTypeForNull" value="NULL"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

    <typeAliases>
        <package name="mybatis.bean"/>
    </typeAliases>
    
    <environments default="dev_mysql">
        <environment id="dev_mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <databaseIdProvider type="DB_VENDOR">
        <property name="MySQL" value="mysql"/>
        <property name="Oracle" value="oracle"/>
        <property name="SQL Server" value="sqlserver"/>
    </databaseIdProvider>

    <mappers>
        <mapper resource="mybatis/PersonMapper.xml"/>
    </mappers>
</configuration>

测试

public class MyBatisTest {
    public static SqlSessionFactory sqlSessionFactory = null;

    public static SqlSessionFactory getSqlSessionFactory() {
        if (sqlSessionFactory == null) {
            String resource = "mybatis-config.xml";
            try {
                Reader reader = Resources.getResourceAsReader(resource);
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return sqlSessionFactory;
    }
    public void processMybatisBatch()
    {
        SqlSession sqlSession = this.getSqlSessionFactory().openSession();
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);

        List<Person> persons=new ArrayList<Person>();

        for (int i = 0; i <1000 ; i++)
        {
            Person person=new Person("jerry"+i,"email@"+i,"f");
            persons.add(person);
        }
        personMapper.addPersons(persons);
        sqlSession.commit();

    }
    public static void main(String[] args) {
        new MyBatisTest().processMybatisBatch();
    }
}

2.借助MySQL数据库连接属性 allowMultiQueries=true

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
username=root
password=123456

然后，只需要修改上面PersonMapper.xml中的insert语句

<insert id="addPersons">
       <foreach collection="persons" item="person" separator=";">
          insert into person(username,email,gender) VALUES
         (#{person.username},#{person.email},#{person.gender})
        </foreach>
     </insert>

3.基于SqlSession的ExecutorType进行批量添加

public interface PersonMapper {
    void addPerson(Person person);
}

 

 <insert id="addPerson" parameterType="person">
        insert into person(username,email,gender) VALUES (#{username},#{email},#{gender})
    </insert>

测试

public void testBatchForExecutor() {
        SqlSession sqlSession = this.getSqlSessionFactory().openSession(ExecutorType.BATCH);

        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);

        for (int i = 0; i <10 ; i++)
        {
            personMapper.addPerson(new Person("Tom","email@"+i,"F"));
        }
        sqlSession.commit();
        sqlSession.close();
    }

总结

传统JDBC批量插入方法：
1.利用for循环进行插入的方式存在严重效率问题，需要频繁获取Session，获取连接。
2.使用批处理，代码和SQL的耦合度高，代码量较大。

MyBatis进行批量插入的方法：
1.MySQL下批量保存的两种方式，建议使用第一种
2.借助于Executor的Batch批量添加，可与Spring框架整合，数据量大的时候，我们一般采用这种方式。

作者：沧海一粟谦
链接：https://www.jianshu.com/p/a18192e2de32

往期推荐：

●史上最强Tomcat8性能优化

●日均5亿查询量的京东到家订单中心，为什么舍MySQL用ES?

●四张图带你了解Tomcat系统架构--让面试官颤抖的Tomcat回答系列！

●“12306”架构到底有多牛逼--秒杀系统的艺术

●阿里巴巴为什么能抗住90秒100亿？--服务端高并发分布式架构演进之路

●B2B电商平台--ChinaPay银联电子支付功能

●学会Zookeeper分布式锁，让面试官对你刮目相看

●SpringCloud电商秒杀微服务-Redisson分布式锁方案

一只 有深度 有灵魂 的公众号0.0