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