mysql mybatis批量url mybatis批量处理

转载

definitely 2023-11-19 07:29:20

文章标签 mysql mybatis批量url sql System mysql 文章分类 MySQL 数据库

MyBatis简介

MyBatis是一个支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录。

一、mybiats foreach标签

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有 item，index，collection，open，separator，close。item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔符，close表示以什么结束，在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：

如果传入的是单参数且参数类型是一个List的时候，collection属性值为list

如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array

如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了

具体用法如下:

<insert id="insertUserbatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        insert into user(username,birthday,sex,address) values
        <foreach collection="list" item="user" separator=",">
            (#{user.username},#{user.birthday},#{user.sex},#{user.address} )
        </foreach>
 </insert>

二、mybatis ExecutorType.BATCH

Mybatis内置的ExecutorType有3种，默认的是simple，该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql；而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句，显然batch性能将更优；但batch模式也有自己的问题，比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的

具体用法如下:

*方式一 spring+mybatis 的

public SqlSession getSqlSession() throws Exception{
        InputStream inputStream =Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        return sqlSession;
    }


    @Test
    public void getfind2() throws Exception{
        SqlSession sqlSession = getSqlSession();
        long start = System.currentTimeMillis();
        User2Mapper user2Mapper = sqlSession.getMapper(User2Mapper.class);
        User user1 = new User("ew1",new Date(),"1","1321");
        for (int i =0;i<10000;i++){
           user2Mapper.insertUserbatchForNew(user1);
        }
        System.out.println(user1.getId());
        sqlSession.commit();
        long end  = System.currentTimeMillis();
        System.out.println(end - start);
        System.out.println(user1.getId());

    }

 <insert id="insertUserbatchForNew" parameterType="com.he.Po.User" useGeneratedKeys="true" keyProperty="id">
        insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address} )

 </insert>

以上是mybatis的批量操作情况，对于mybatis可以对比下面的jdbc批量操作的情况

DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=100000 DO
        INSERT INTO text VALUES(i,CONCAT('姓名',i),'XXXXXXXXX');
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();

执行CALL proc_initData()后，本来想想，再慢10W条数据顶多30分钟能搞定吧，结果我打了2把LOL后，回头一看，还在执行，此时心里是彻底懵逼的....待我打完第三把结束后，终于执行完了，这种方法若是让我等上几百万条数据，是不是早上去上班，下午下班回来还没结束呢？10W条数据，有图有真相

mysql mybatis批量url mybatis批量处理_mysql mybatis批量url

JDBC往数据库中普通插入方式

后面查了一下，使用JDBC批量操作往数据库插入100W+的数据貌似也挺快的，

先来说说JDBC往数据库中普通插入方式，简单的代码大致如下，循环了1000条，中间加点随机的数值，毕竟自己要拿数据测试，数据全都一样也不好区分

private String url = "jdbc:mysql://localhost:3306/test01";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 1000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);27                     pstm.executeUpdate();
            }
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

输出结果：OK,用时：738199，单位毫秒，也就是说这种方式与直接数据库中循环是差不多的。

在讨论批量处理之前，先说说遇到的坑，首先，JDBC连接的url中要加rewriteBatchedStatements参数设为true是批量操作的前提，其次就是检查mysql驱动包时候是5.1.13以上版本（低于该版本不支持），因网上随便下载了5.1.7版本的，然后执行批量操作（100W条插入），结果因为驱动器版本太低缘故并不支持，导致停止掉java程序后，mysql还在不断的往数据库中插入数据，最后不得不停止掉数据库服务才停下来...

那么低版本的驱动包是否对100W+数据插入就无力了呢？实际还有另外一种方式，效率相比来说还是可以接受的。

使用事务提交方式

先将命令的提交方式设为false，即手动提交conn.setAutoCommit(false);最后在所有命令执行完之后再提交事务conn.commit();

private String url = "jdbc:mysql://localhost:3306/test01";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 100000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.executeUpdate();
            }
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

以上代码插入10W条数据，输出结果：OK,用时：18086，也就十八秒左右的时间，理论上100W也就是3分钟这样，勉强还可以接受。

批量处理

接下来就是批量处理了，注意，一定要5.1.13以上版本的驱动包。

private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 100000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.addBatch();
            }
            pstm.executeBatch();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

10W输出结果：OK,用时：3386，才3秒钟.

批量操作+事务

然后我就想，要是批量操作+事务提交呢？会不会有神器的效果？

private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 100000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.addBatch();
            }
            pstm.executeBatch();
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

以下是100W数据输出对比：（5.1.17版本MySql驱动包下测试，交替两种方式下的数据测试结果对比）

批量操作(10W)	批量操作+事务提交(10W)	批量操作(100W)	批量错作+事务提交(100W)
OK,用时：3901	OK,用时：3343	OK,用时：44242	OK,用时：39798
OK,用时：4142	OK,用时：2949	OK,用时：44248	OK,用时：39959
OK,用时：3664	OK,用时：2689	OK,用时：44389	OK,用时：39367

可见有一定的效率提升，但是并不是太明显，当然因为数据差不算太大，也有可能存在偶然因数，毕竟每项只测3次。

预编译+批量操作

网上还有人说使用预编译+批量操作的方式能够提高效率更明显，但是本人亲测，效率不高反降，可能跟测试的数据有关吧。

预编译的写法，只需在JDBC的连接url中将写入useServerPrepStmts=true即可，

如：

private String url = "jdbc:mysql://localhost:3306/test01?useServerPrepStmts=true&rewriteBatchedStatements=true"

好了，先到这里...

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：jquery制作简历 jquery网页制作

下一篇：android开发封装父类activity android retrofit封装

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯