springboot-mybatis 批量insert

 

 

springboot mybatis 批量insert 操作

直接上代码：

1.首先要在pom.xml中导入包：

springboot 1.5.8

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- 使用数据源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.25</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            
            <version>1.3.2</version>
        </dependency>

　　

2.springboot mybatis配置：

package com.xxx.common.config;


@Configuration
@MapperScan(basePackages="com.xxx.mapper")
public class MyBatisConfig {

    @Autowired
    private Environment env;

    /**
     * 创建数据源
     * @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错
     */
    @Bean
    //@Primary
    public DataSource getDataSource() throws Exception{
        Properties props = new Properties();
        props.put("driverClassName", env.getProperty("jdbc.driverClassName"));
        props.put("url", env.getProperty("jdbc.url"));
        props.put("username", env.getProperty("jdbc.username"));
        props.put("password", env.getProperty("jdbc.password"));
        return DruidDataSourceFactory.createDataSource(props);
    }
    @Bean
    public PlatformTransactionManager txManager() throws Exception {
        return new DataSourceTransactionManager(getDataSource());
    }

    /**
     * 根据数据源创建SqlSessionFactory
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource ds) throws Exception{
        SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
        fb.setDataSource(ds);//指定数据源(这个必须有，否则报错)
        fb.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        //下边两句仅仅用于*.xml文件，如果整个持久层操作不需要使用到xml文件的话（只用注解就可以搞定），则不加
        fb.setTypeAliasesPackage(env.getProperty("mybatis.basePackage"));//指定基包
        fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapperLocations")));//指定xml文件位置

        return fb.getObject();
    }
    
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

 

3.mybatis-config.xml:

<?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>
  <!-- 全局参数 -->
  <settings>
    <!-- 使全局的映射器启用或禁用缓存。 -->
    <setting name="cacheEnabled" value="true"/>
    <!-- 全局启用或禁用延迟加载。当禁用时，所有关联对象都会即时加载。 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- 当启用时，有延迟加载属性的对象在被调用时将会完全加载任意属性。否则，每种属性将会按需要加载。 -->
    <setting name="aggressiveLazyLoading" value="true"/>
    <!-- 是否允许单条sql 返回多个数据集  (取决于驱动的兼容性) default:true -->
    <setting name="multipleResultSetsEnabled" value="true"/>
    <!-- 是否可以使用列的别名 (取决于驱动的兼容性) default:true -->
    <setting name="useColumnLabel" value="true"/>
    <!-- 允许JDBC 生成主键。需要驱动器支持。如果设为了true，这个设置将强制使用被生成的主键，有一些驱动器不兼容不过仍然可以执行。  default:false  -->
    <setting name="useGeneratedKeys" value="true"/>
    <!-- 指定 MyBatis 如何自动映射 数据基表的列 NONE：不隐射　PARTIAL:部分  FULL:全部  -->
    <setting name="autoMappingBehavior" value="PARTIAL"/>
    <!-- 这是默认的执行类型  （SIMPLE: 简单； REUSE: 执行器可能重复使用prepared statements语句；BATCH: 执行器可以重复执行语句和批量更新）  -->
    <setting name="defaultExecutorType" value="SIMPLE"/>
    <!-- 使用驼峰命名法转换字段。 -->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <!-- 设置本地缓存范围 session:就会有数据的共享  statement:语句范围 (这样就不会有数据的共享 ) defalut:session -->
    <setting name="localCacheScope" value="SESSION"/>
    <!-- 设置但JDBC类型为空时,某些驱动程序 要指定值,default:OTHER，插入空值时不需要指定类型 -->
    <setting name="jdbcTypeForNull" value="NULL"/>
    <setting name="logImpl" value="LOG4J2"/>
    <!--<setting name="logImpl" value="STDOUT_LOGGING"/>-->
  </settings>
  <plugins>
    <plugin interceptor="com.github.pagehelper.PageHelper">
      <property name="dialect" value="mysql"/>
      <property name="offsetAsPageNum" value="false"/>
      <property name="rowBoundsWithCount" value="false"/>
      <property name="pageSizeZero" value="true"/>
      <property name="reasonable" value="false"/>
      <property name="supportMethodsArguments" value="false"/>
      <property name="returnPageInfo" value="none"/>
    </plugin>
  </plugins>
</configuration>

 

4.application.yml:

server:
  port: 8080
  context-path: /test
  tomcat:
    max-threads: 800

#dev
jdbc:
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
  username: root
  password: 123456

#mybatis config
mybatis:
  basePackage: com.xxx.model
  mapperLocations: classpath*:/mapper/**/*.xml

 

5.java model:

package com.xxx.model;


/**
 * 实体类对应的数据表为： aaa
 * @author Jeff
 * @date 2017-11-09 14:28:47
 */

public class IotBasRunType222 {

    private Integer runType;


    private String name;

 
    private String nameEn;

   //省略get set。。。
}

 

6.java mapper:

package com.xxx.mapper;


public interface IotBasRunTypeMapper222  {
    int insertBatch(List<IotBasRunType222 > iotBasRunTypeList);
}

 

7.xml 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.xxx.mapper.IotBasRunTypeMapper222" >

<sql id="Base_Column_List" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    net_type, t_name, name_en
  </sql>
  
  <insert id="insertBatch" parameterType="java.util.List">
     insert into aaa (<include refid="Base_Column_List" />)
      values 
    <foreach collection="list" item="item" index="index"  separator=",">
    (#{item.runType,jdbcType=INTEGER}, #{item.name,jdbcType=VARCHAR}, #{item.nameEn,jdbcType=VARCHAR})
    </foreach>
 </insert>
</mapper>


#如果要设置默认值，可以采用如下方式：
    
<insert id="insertBatch" parameterType="java.util.List">
     insert into xxx (<include refid="Base_Column_List" />)
      values 
    <foreach collection="list" item="item" index="index"  separator=",">
    <trim prefix=" (" suffix=")" suffixOverrides="," >
      #{item.deviceType,jdbcType=SMALLINT}, #{item.deviceModel,jdbcType=SMALLINT},
     
      <choose>
          <when test="item.delayTime != null">
               #{item.delayTime,jdbcType=INTEGER},
          </when>
          <otherwise>
              0,
          </otherwise>
      </choose>
      <choose>
          <when test="item.delaycanceTime != null">
               #{item.delaycanceTime,jdbcType=INTEGER},
          </when>
          <otherwise>
              0,
          </otherwise>
      </choose>
      <choose>
          <when test="item.delta != null">
              #{item.delta,jdbcType=DOUBLE},
          </when>
          <otherwise>
              0,
          </otherwise>
      </choose>

      </trim>
    </foreach>
 </insert>

 

8.service imp:

package com.xxx.service.impl;


/**
 * @desc   
 * @author  create author by deer
 * @date  2017年11月28日上午9:41:18
 */
@Service
public class IotBasRunType222ServiceImpl extends IotBaseServiceImpl<IotBasRunType222>{
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;
    
      public boolean insertBatch(List<IotBasRunType222> members)
                throws Exception {
            // TODO Auto-generated method stub
            int result = 1;
            SqlSession batchSqlSession = null;
            try {
                batchSqlSession = this.sqlSessionTemplate
                        .getSqlSessionFactory()
                        .openSession(ExecutorType.BATCH, false);// 获取批量方式的sqlsession
              //通过新的session获取mapper
                IotBasRunTypeMapper222  mapper = batchSqlSession.getMapper(IotBasRunTypeMapper222.class);
                
                //int batchCount = 1000;// 每批commit的个数
                int batchCount = 10;// 每批commit的个数
                int batchLastIndex = batchCount;// 每批最后一个的下标
              
                for (int index = 0; index < members.size();) {
                    if (batchLastIndex >= members.size()) {
                        batchLastIndex = members.size();
                        
                        result = result + mapper.insertBatch(members.subList(index, batchLastIndex));
                        batchSqlSession.commit();
                        //清理缓存，防止溢出
                        batchSqlSession.clearCache();
                        System.out.println("index:" + index+ " batchLastIndex:" + batchLastIndex);
                        break;// 数据插入完毕，退出循环
                    } else {
                       
                          result = result + mapper.insertBatch(members.subList(index, batchLastIndex));
                        batchSqlSession.commit();
                        //清理缓存，防止溢出
                        batchSqlSession.clearCache();
                        System.out.println("index:" + index+ " batchLastIndex:" + batchLastIndex);
                        index = batchLastIndex;// 设置下一批下标
                        batchLastIndex = index +(batchCount-1);
                    }
                    System.out.println("=============>result=["+result+"] begin=["+index+"] end=["+batchLastIndex+"]");
                }
                batchSqlSession.commit();
            } catch(Exception e) {
                e.printStackTrace();
            }
            finally {
                batchSqlSession.close();
            }
            return true;

}

 

9.test类：

package com.xxx.test.service;

/**
 * @desc   
 * @class  BatchInsertTest
 * @author  create author by deer
 * @date  2017年11月28日上午10:01:43
 */
@RunWith(SpringJUnit4ClassRunner.class)  // SpringJUnit支持，由此引入Spring-Test框架支持！
@SpringBootTest
public class BatchInsertTest {

    @Autowired
    private  IotBasRunType222ServiceImpl iotBasRunType222ServiceImpl;
    
    @Test
    public void test() {
        
        try {
            iotBasRunType222ServiceImpl.insertBatch(getListDatas());
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    private List<IotBasRunType222> getListDatas(){
        List<IotBasRunType222> list = new ArrayList<>();
        IotBasRunType222 otype = null;
        for(int i=1;i<=35;i++) {
            otype = new IotBasRunType222();
            otype.setRunType(i);
            otype.setName("名称"+i);
            otype.setNameEn("nameEn"+i);
            list.add(otype);
        }
        return list;
        
    }
}

 

10：参考网址

http://blog.csdn.net/wlwlwlwl015/article/details/50246717
https://teakki.com/p/57df75551201d4c1629b82d5
http://www.cnblogs.com/xcch/articles/2042298.html
http://www.cnblogs.com/admol/articles/4248159.html