美文网首页
2019-05-19_Mybatis关于mysql批量插入学习

2019-05-19_Mybatis关于mysql批量插入学习

作者: kikop | 来源:发表于2019-05-19 22:36 被阅读0次

    Mybatis关于mysql批量插入学习
    1.概述
    对mybatis常用的操作命令做一下总结,数据库基于mysql。后面章节会涉及数据的批量插入、批量更新和更新后返回主键ID等内容。
    1.数据库信息
    1.1.表结构定义
    CREATE TABLE users (
    id int(11) NOT NULL AUTO_INCREMENT,
    NAME varchar(20) DEFAULT NULL,
    age int(11) DEFAULT NULL,
    remark varchar(200) DEFAULT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=682 DEFAULT CHARSET=utf8;

    1.2.数据库配置db.properties
    mysql.driver=com.mysql.jdbc.Driver

    需要开启多行执行功能

    jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false"

    jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true"

    mysql.url=jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    mysql.username=root
    mysql.password=123456
    1.3.实体类
    package com.tech.ability.mybatis.Entity;

    /**

    • Created by kikop on 2019/1/17.
      */
      public class MyUser {

      //实体类的属性和表的字段名称一一对应
      private int id;
      private String name;
      private int age;

      public String getRemark() {
      return remark;
      }

      public void setRemark(String remark) {
      this.remark = remark;
      }

      private String remark;

      public int getId() {
      return id;
      }

      public void setId(int id) {
      this.id = id;
      }

      public String getName() {
      return name;
      }

      public void setName(String name) {
      this.name = name;
      }

      public int getAge() {
      return age;
      }

      public void setAge(int age) {
      this.age = age;
      }

      @Override
      public String toString() {
      return "MyUser{" +
      "age=" + age +
      ", id=" + id +
      ", name='" + name + ''' +
      ", remark='" + remark + ''' +
      '}';
      }

    }
    2.mybatis mapper接口文件
    package com.tech.ability.mybatis.Mapper;

    import com.alibaba.fastjson.JSONObject;
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.SelectKey;
    import org.springframework.data.repository.query.Param;

    /**



    <mapper namespace="com.tech.ability.mybatis.Mapper.MyUserMapper">





    <insert id="batchInsertUserInfoListByUnionAll" parameterType="com.alibaba.fastjson.JSONObject">
    insert into users(name,age,remark)
    <foreach collection="idList" index="index" item="currentRow" separator="union all">
    ( select '{currentRow.name}',#{currentRow.age},'{currentRow.remark}')
    </foreach>
    </insert>

    <!--7.批量插入-->
    <!--union all的方式-->
    <!--oracle:useGeneratedKeys="false"-->
    <!--主键在myaql自增涨-->
    <insert id="batchInsertUserInfoListByExecutorBach" parameterType="com.alibaba.fastjson.JSONObject">
        insert into users(name,age,remark)
        values('${name}',#{age},'${remark}')
    </insert>
    

    </mapper>
    4.mybatis工具类
    package com.tech.ability.mybatis;

    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.ExecutorType;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;

    import java.io.IOException;
    import java.io.InputStream;

    /**

    • Created by kikop on 2019/5/19.
      */
      public class mybatisUtils {
      //mybatis核心配置文件
      private static final String mybatis_configPath = "mybatisconfig.xml";
    /**
     * 读取SqlSession
     *
     * @return
     */
    public static SqlSession getSqlSession(ExecutorType execType, boolean isAutoCommit) {
    
        SqlSession sqlSession = null;
        try {
            //1.读取mybatis的配置文件
            //String resource = "mybatisconfig.xml";
    
            //2.构建sqlSession的工厂
            //使用类加载器加载 mybatis 的 classspath中配置文件
            InputStream inputStream = null;
    

    // InputStream inputStream = mybatisTest.class.getClassLoader().getResourceAsStream(resource);
    inputStream = Resources.getResourceAsStream(mybatis_configPath);
    SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            //2.构建sqlSession的工厂
            //使用MyBatis提供的Resources类加载mybatis的配置文件(它也加载关联的映射文件)
            //Reader reader = Resources.getResourceAsReader(resource);
            //SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
    
            //3.创建能执行映射文件中sql的sqlSession
    

    // sqlSession = sessionFactory.openSession(isAutoCommit);
    sqlSession = sessionFactory.openSession(execType, isAutoCommit);

        } catch (IOException e) {
            e.printStackTrace();
        }
    
        return sqlSession;
    
    }
    
    /**
     * 关闭session(回收连接池必须)
     *
     * @param session
     */
    public static void closeSqlSession(SqlSession session) {
        session.close();
    }
    
    /**
     * 关闭inputStream
     *
     * @param inputStream
     */
    public static void closeInputSream(InputStream inputStream) {
        try {
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    

    }
    5.测试代码
    5.1.测试1

    package com.tech.ability.mybatis;

    import com.alibaba.fastjson.JSONObject;
    import com.tech.ability.mybatis.Entity.MyUser;
    import org.apache.ibatis.session.ExecutorType;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.logging.log4j.LogManager;
    import org.apache.logging.log4j.Logger;

    import java.util.ArrayList;
    import java.util.List;

    /**

    • Created by kikop on 2019/1/17.
      */
      public class mybatisTest {

      private static final Logger logger = LogManager.getLogger(mybatisTest.class);

    /**
     * 批量插入实现1
     */
    public static void batchInsertUserInfoListByUnionAllTest() {
    
        //1.构建sqlSession
        SqlSession session = mybatisUtils.getSqlSession(ExecutorType.SIMPLE, false);
    
        //2.映射sql的标识字符串
        StringBuffer statement = new StringBuffer();
        statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper");  //mapper标签的namespace属性的值
        statement.append(".batchInsertUserInfoListByUnionAll");  //select标签的id属性值
    
        //3.定义查询条件
        List<JSONObject> idList = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("name", String.format("name_%d", i));
            jsonObject.put("age", i);
            jsonObject.put("remark", String.format("remark_%d", i));
            idList.add(jsonObject);
        }
        JSONObject requestJsonObject = new JSONObject();
        requestJsonObject.put("idList", idList);
        //4.执行查询返回一个唯一user对象的sql
        int updateResult = session.insert(statement.toString(), requestJsonObject);
        System.out.println("[insertUserXml]影响的行数updateResult:" + updateResult + "返回值respID:" + requestJsonObject.get("respID"));
    
        if (updateResult > 0) {
            session.commit();
        } else {
            session.rollback();
        }
        //5.资源释放(连接池回收)
        mybatisUtils.closeSqlSession(session);
    

    }

    SLF4J: Class path contains multiple SLF4J bindings.
    SLF4J: Found binding in [jar:file:/E:/workfolder/JavaWebSoft/maven/LocalMavenRep/org/apache/logging/log4j/log4j-slf4j-impl/2.1/log4j-slf4j-impl-2.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
    SLF4J: Found binding in [jar:file:/E:/workfolder/JavaWebSoft/maven/LocalMavenRep/org/slf4j/slf4j-log4j12/1.6.1/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
    SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
    SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
    2019-05-19 17:06:11.931 DEBUG [main][LogFactory.java:124] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
    2019-05-19 17:06:11.945 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
    2019-05-19 17:06:11.946 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
    2019-05-19 17:06:11.946 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
    2019-05-19 17:06:11.947 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
    2019-05-19 17:06:12.132 DEBUG [main][JdbcTransaction.java:129] - Opening JDBC Connection
    2019-05-19 17:06:12.591 DEBUG [main][PooledDataSource.java:378] - Created connection 898694235.
    2019-05-19 17:06:12.591 DEBUG [main][JdbcTransaction.java:95] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
    2019-05-19 17:06:12.595 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
    2019-05-19 17:06:12.600 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) ( select 'name_0',?,'remark_0') union all ( select 'name_1',?,'remark_1') union all ( select 'name_2',?,'remark_2') union all ( select 'name_3',?,'remark_3') union all ( select 'name_4',?,'remark_4') union all ( select 'name_5',?,'remark_5') union all ( select 'name_6',?,'remark_6') union all ( select 'name_7',?,'remark_7') union all ( select 'name_8',?,'remark_8') union all ( select 'name_9',?,'remark_9') union all ( select 'name_10',?,'remark_10') union all ( select 'name_11',?,'remark_11') union all ( select 'name_12',?,'remark_12') union all ( select 'name_13',?,'remark_13') union all ( select 'name_14',?,'remark_14') union all ( select 'name_15',?,'remark_15') union all ( select 'name_16',?,'remark_16') union all ( select 'name_17',?,'remark_17') union all ( select 'name_18',?,'remark_18') union all ( select 'name_19',?,'remark_19') union all ( select 'name_20',?,'remark_20') union all ( select 'name_21',?,'remark_21') union all ( select 'name_22',?,'remark_22') union all ( select 'name_23',?,'remark_23') union all ( select 'name_24',?,'remark_24') union all ( select 'name_25',?,'remark_25') union all ( select 'name_26',?,'remark_26') union all ( select 'name_27',?,'remark_27') union all ( select 'name_28',?,'remark_28') union all ( select 'name_29',?,'remark_29') union all ( select 'name_30',?,'remark_30') union all ( select 'name_31',?,'remark_31') union all ( select 'name_32',?,'remark_32') union all ( select 'name_33',?,'remark_33') union all ( select 'name_34',?,'remark_34') union all ( select 'name_35',?,'remark_35') union all ( select 'name_36',?,'remark_36') union all ( select 'name_37',?,'remark_37') union all ( select 'name_38',?,'remark_38') union all ( select 'name_39',?,'remark_39') union all ( select 'name_40',?,'remark_40') union all ( select 'name_41',?,'remark_41') union all ( select 'name_42',?,'remark_42') union all ( select 'name_43',?,'remark_43') union all ( select 'name_44',?,'remark_44') union all ( select 'name_45',?,'remark_45') union all ( select 'name_46',?,'remark_46') union all ( select 'name_47',?,'remark_47') union all ( select 'name_48',?,'remark_48') union all ( select 'name_49',?,'remark_49') union all ( select 'name_50',?,'remark_50') union all ( select 'name_51',?,'remark_51') union all ( select 'name_52',?,'remark_52') union all ( select 'name_53',?,'remark_53') union all ( select 'name_54',?,'remark_54') union all ( select 'name_55',?,'remark_55') union all ( select 'name_56',?,'remark_56') union all ( select 'name_57',?,'remark_57') union all ( select 'name_58',?,'remark_58') union all ( select 'name_59',?,'remark_59') union all ( select 'name_60',?,'remark_60') union all ( select 'name_61',?,'remark_61') union all ( select 'name_62',?,'remark_62') union all ( select 'name_63',?,'remark_63') union all ( select 'name_64',?,'remark_64') union all ( select 'name_65',?,'remark_65') union all ( select 'name_66',?,'remark_66') union all ( select 'name_67',?,'remark_67') union all ( select 'name_68',?,'remark_68') union all ( select 'name_69',?,'remark_69') union all ( select 'name_70',?,'remark_70') union all ( select 'name_71',?,'remark_71') union all ( select 'name_72',?,'remark_72') union all ( select 'name_73',?,'remark_73') union all ( select 'name_74',?,'remark_74') union all ( select 'name_75',?,'remark_75') union all ( select 'name_76',?,'remark_76') union all ( select 'name_77',?,'remark_77') union all ( select 'name_78',?,'remark_78') union all ( select 'name_79',?,'remark_79') union all ( select 'name_80',?,'remark_80') union all ( select 'name_81',?,'remark_81') union all ( select 'name_82',?,'remark_82') union all ( select 'name_83',?,'remark_83') union all ( select 'name_84',?,'remark_84') union all ( select 'name_85',?,'remark_85') union all ( select 'name_86',?,'remark_86') union all ( select 'name_87',?,'remark_87') union all ( select 'name_88',?,'remark_88') union all ( select 'name_89',?,'remark_89') union all ( select 'name_90',?,'remark_90') union all ( select 'name_91',?,'remark_91') union all ( select 'name_92',?,'remark_92') union all ( select 'name_93',?,'remark_93') union all ( select 'name_94',?,'remark_94') union all ( select 'name_95',?,'remark_95') union all ( select 'name_96',?,'remark_96') union all ( select 'name_97',?,'remark_97') union all ( select 'name_98',?,'remark_98') union all ( select 'name_99',?,'remark_99')
    2019-05-19 17:06:12.661 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 0(Integer), 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 6(Integer), 7(Integer), 8(Integer), 9(Integer), 10(Integer), 11(Integer), 12(Integer), 13(Integer), 14(Integer), 15(Integer), 16(Integer), 17(Integer), 18(Integer), 19(Integer), 20(Integer), 21(Integer), 22(Integer), 23(Integer), 24(Integer), 25(Integer), 26(Integer), 27(Integer), 28(Integer), 29(Integer), 30(Integer), 31(Integer), 32(Integer), 33(Integer), 34(Integer), 35(Integer), 36(Integer), 37(Integer), 38(Integer), 39(Integer), 40(Integer), 41(Integer), 42(Integer), 43(Integer), 44(Integer), 45(Integer), 46(Integer), 47(Integer), 48(Integer), 49(Integer), 50(Integer), 51(Integer), 52(Integer), 53(Integer), 54(Integer), 55(Integer), 56(Integer), 57(Integer), 58(Integer), 59(Integer), 60(Integer), 61(Integer), 62(Integer), 63(Integer), 64(Integer), 65(Integer), 66(Integer), 67(Integer), 68(Integer), 69(Integer), 70(Integer), 71(Integer), 72(Integer), 73(Integer), 74(Integer), 75(Integer), 76(Integer), 77(Integer), 78(Integer), 79(Integer), 80(Integer), 81(Integer), 82(Integer), 83(Integer), 84(Integer), 85(Integer), 86(Integer), 87(Integer), 88(Integer), 89(Integer), 90(Integer), 91(Integer), 92(Integer), 93(Integer), 94(Integer), 95(Integer), 96(Integer), 97(Integer), 98(Integer), 99(Integer)
    2019-05-19 17:06:12.665 DEBUG [main][BaseJdbcLogger.java:132] - <== Updates: 100
    2019-05-19 17:06:12.666 DEBUG [main][JdbcTransaction.java:66] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
    2019-05-19 17:06:12.668 DEBUG [main][JdbcTransaction.java:117] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
    2019-05-19 17:06:12.669 DEBUG [main][JdbcTransaction.java:85] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]
    2019-05-19 17:06:12.670 DEBUG [main][PooledDataSource.java:332] - Returned connection 898694235 to pool.

    5.2.测试2

    /**
    * 批量插入实现2
    * Batch返回值:固定
    */
    public static void batchInsertUserInfoListByExecutorBachTest2() {

        //1.构建sqlSession
        SqlSession session = mybatisUtils.getSqlSession(ExecutorType.BATCH, false);
    
        //2.映射sql的标识字符串
        StringBuffer statement = new StringBuffer();
        statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper");  //mapper标签的namespace属性的值
        statement.append(".batchInsertUserInfoListByExecutorBach");  //select标签的id属性值
    
        try {
            //3.定义查询条件
            for (int i = 0; i < 100; i++) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("name", String.format("name_%d", i));
                jsonObject.put("age", i);
                jsonObject.put("remark", String.format("remark_%d", i));
    

    // handler.parameterize(stmt);
    // handler.batch(stmt);
    // return -2147482646;

                //4.执行查询返回一个唯一user对象的sql
                int updateResult = session.insert(statement.toString(), jsonObject);
                System.out.println("[insertUserXml]影响的行数updateResult:" + updateResult + "返回值respID:" + jsonObject.get("respID"));
    
            }
            session.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            session.rollback();
        } finally {
            //5.资源释放(连接池回收)
            mybatisUtils.closeSqlSession(session);
        }
    

    }
    6.调用
    public static void main(String[] args) throws Exception {
    //getUserInfoByIDTest();
    //getUserInfoByInTest();
    //getUserInfoByInnerForeachTest();
    //batchUpdateUserInfoListManualTest();
    //batchUpdateUserInfoListAutoTest();
    //batchInsertUserInfoListByUnionAllTest();
    //batchInsertUserInfoListByExecutorBachTest2();
    //insertUserXmlTest();
    batchInsertUserInfoListByUnionAllTest();
    }
    }

    2019-05-19 22:31:24.436 DEBUG [main][LogFactory.java:124] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
    2019-05-19 22:31:24.449 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
    2019-05-19 22:31:24.450 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
    2019-05-19 22:31:24.450 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
    2019-05-19 22:31:24.451 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.
    2019-05-19 22:31:24.576 DEBUG [main][JdbcTransaction.java:129] - Opening JDBC Connection
    2019-05-19 22:31:24.915 DEBUG [main][PooledDataSource.java:378] - Created connection 1786294176.
    2019-05-19 22:31:24.918 DEBUG [main][JdbcTransaction.java:95] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:24.921 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:24.923 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_0',?,'remark_0')
    2019-05-19 22:31:24.985 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 0(Integer)
    [batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
    2019-05-19 22:31:24.986 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:24.987 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_1',?,'remark_1')
    2019-05-19 22:31:24.988 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 1(Integer)
    [batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
    2019-05-19 22:31:24.988 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:24.989 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_2',?,'remark_2')
    2019-05-19 22:31:24.991 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 2(Integer)
    [batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
    2019-05-19 22:31:24.991 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:24.992 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_3',?,'remark_3')
    2019-05-19 22:31:24.992 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 3(Integer)
    [batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
    2019-05-19 22:31:24.993 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:24.993 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_4',?,'remark_4')
    2019-05-19 22:31:24.995 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 4(Integer)
    [batchInsertUserInfoListByExecutorBach]影响的行数updateResult:-2147482646返回值respID:null
    2019-05-19 22:31:24.997 DEBUG [main][JdbcTransaction.java:66] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:25.002 DEBUG [main][JdbcTransaction.java:117] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:25.003 DEBUG [main][JdbcTransaction.java:85] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]
    2019-05-19 22:31:25.003 DEBUG [main][PooledDataSource.java:332] - Returned connection 1786294176 to pool.
    总结
    1.数据库连接时,设置运行多行查询。
    2.多条语句,用“;”隔开即可。
    3.batchInsertUserInfoListByUnionAll 一次性事务提交,数据库执行的sql长度有限制。
    4.batchInsertUserInfoListByExecutorBach均是一次性提交。

    相关文章

      网友评论

          本文标题:2019-05-19_Mybatis关于mysql批量插入学习

          本文链接:https://www.haomeiwen.com/subject/wdeczqtx.html