我要评论一、准备工作
1、导入pom.xml依赖
<dependencies>
<dependency>
<groupid>org.springframework.boot</groupid>
<artifactid>spring-boot-starter-web</artifactid>
</dependency>
<!-- mysql驱动依赖 -->
<dependency>
<groupid>mysql</groupid>
<artifactid>mysql-connector-java</artifactid>
<version>8.0.29</version>
</dependency>
<!--mybatis依赖-->
<dependency>
<groupid>org.mybatis.spring.boot</groupid>
<artifactid>mybatis-spring-boot-starter</artifactid>
<version>2.2.2</version>
</dependency>
<!--mybatis-plus依赖-->
<dependency>
<groupid>com.baomidou</groupid>
<artifactid>mybatis-plus-boot-starter</artifactid>
<version>3.5.2</version>
</dependency>
<!-- lombok依赖 -->
<dependency>
<groupid>org.projectlombok</groupid>
<artifactid>lombok</artifactid>
<optional>true</optional>
</dependency>
<dependency>
<groupid>org.springframework.boot</groupid>
<artifactid>spring-boot-starter-test</artifactid>
<scope>test</scope>
</dependency>
</dependencies>2、配置yml文件
server:
port: 8080
spring:
datasource:
username: root(用户名)
password: root(密码)
url: jdbc:mysql://localhost:3306/test(数据库名字)?useunicode=true&characterencoding=utf-8&usessl=false&servertimezone=utc
driver-class-name: com.mysql.cj.jdbc.driver
mybatis:
mapper-locations: classpath:mapping/*.xml3、公用的user类
@data
public class user {
private int id;
private string username;
private string password;
}
二、mybatis利用for循环批量插入
1、编写userservice服务类
测试一万条数据耗时情况
@service
public class userservice {
@resource
private usermapper usermapper;
public void insertusers(){
long start = system.currenttimemillis();
for(int i = 0 ;i < 10000; i++) {
user user = new user();
user.setusername("name" + i);
user.setpassword("password" + i);
usermapper.insertusers(user);
}
long end = system.currenttimemillis();
system.out.println("一万条数据总耗时：" + (end-start) + "ms" );
}
}
2、编写usermapper接口
@mapper
public interface usermapper {
integer insertusers(user user);
}
3、编写usermapper.xml文件
<?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.ithuang.demo.mapper.usermapper">
<insert id="insertusers">
insert into user (username, password)
values(#{username}, #{password})
</insert>
</mapper>4、进行单元测试
@springboottest
class demoapplicationtests {
@resource
private userservice userservice;
@test
public void insert(){
userservice.insertusers();
}
}
5、结果输出
一万条数据总耗时：26348ms
三、mybatis的手动批量提交
1、其他保持不变
service层作稍微的变化
@service
public class userservice {
@resource
private usermapper usermapper;
@resource
private sqlsessiontemplate sqlsessiontemplate;
public void insertusers(){
//关闭自动提交
sqlsession sqlsession = sqlsessiontemplate.getsqlsessionfactory().opensession(executortype.batch, false);
usermapper usermapper = sqlsession.getmapper(usermapper.class);
long start = system.currenttimemillis();
for(int i = 0 ;i < 10000; i++) {
user user = new user();
user.setusername("name" + i);
user.setpassword("password" + i);
usermapper.insertusers(user);
}
sqlsession.commit();
long end = system.currenttimemillis();
system.out.println("一万条数据总耗时：" + (end-start) + "ms" );
}
}
2、结果输出
一万条数据总耗时：24516ms
四、mybatis以集合方式批量新增
1、编写userservice服务类
@service
public class userservice {
@resource
private usermapper usermapper;
public void insertusers(){
long start = system.currenttimemillis();
list<user> userlist = new arraylist<>();
user user;
for(int i = 0 ;i < 10000; i++) {
user = new user();
user.setusername("name" + i);
user.setpassword("password" + i);
userlist.add(user);
}
usermapper.insertusers(userlist);
long end = system.currenttimemillis();
system.out.println("一万条数据总耗时：" + (end-start) + "ms" );
}
}
3、编写usermapper接口
@mapper
public interface usermapper {
integer insertusers(list<user> userlist);
}
4、编写usermapper.xml文件
<?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.ithuang.demo.mapper.usermapper">
<insert id="insertusers">
insert into user (username, password)
values
<foreach collection ="userlist" item="user" separator =",">
(#{user.username}, #{user.password})
</foreach>
</insert>
</mapper>以下完整且规范的写法
注：如果需要mysql支持批量操作需要在yml的url配置中新增allowmultiqueries=true，支持以;分隔批量执行sql。
例如：
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?allowmultiqueries=true&usessl=false
username:
password: 完整代码：
<insert id="insertuserbatch">
<foreach collection ="list" item="item" separator =";">
insert into user
<trim prefix="(" suffix=")" suffixoverrides=",">
<if test="item.id != null and item.id != ''">id,</if>
<if test="item.username != null and item.username != ''">username,</if>
<if test="item.password != null and item.password != ''">password,</if>
<if test="item.sex != null and item.sex != ''">sex,</if>
<if test="item.age != null">age,</if>
<if test="item.address != null and item.address != ''">address,</if>
<if test="item.createtime != null">create_time,</if>
</trim>
values
<trim prefix="(" suffix=")" suffixoverrides=",">
<if test="item.id != null and item.id != ''">#{item.id},</if>
<if test="item.username != null and item.username != ''">#{item.username},</if>
<if test="item.password != null and item.password != ''">#{item.password},</if>
<if test="item.sex != null and item.sex != ''">#{item.sex},</if>
<if test="item.age != null">#{item.age},</if>
<if test="item.address != null and item.address != ''">#{item.address},</if>
<if test="item.createtime != null">#{item.createtime},</if>
</trim>
</foreach>
</insert>5、输出结果
一万条数据总耗时：521ms
五、mybatis-plus提供的savebatch方法
1、编写userservice服务
@service
public class userservice extends serviceimpl<usermapper, user> implements iservice<user> {
public void insertusers(){
long start = system.currenttimemillis();
list<user> userlist = new arraylist<>();
user user;
for(int i = 0 ;i < 10000; i++) {
user = new user();
user.setusername("name" + i);
user.setpassword("password" + i);
userlist.add(user);
}
savebatch(userlist);
long end = system.currenttimemillis();
system.out.println("一万条数据总耗时：" + (end-start) + "ms" );
}
}
2、编写usermapper接口
@mapper
public interface usermapper extends basemapper<user> {
}
3、单元测试结果
一万条数据总耗时：24674ms
注：
这边我没有开启rewritebatchedstatements=true，所以会非常慢，开启的话我实测过，耗时为707ms，这个参数的作用是启用批处理语句的重写功能，这对提高使用jdbc批量更新语句的性能有很大帮助。
mybatis-plus的savebatch方法默认使用jdbc的addbatch()和executebatch()方法实现批量插入。但是部分数据库的jdbc驱动并不支持addbatch()，这样每次插入都会发送一条sql语句，严重影响了批量插入的性能。
设置rewritebatchedstatements=true后，mybatis-plus会重写插入语句，将其合并为一条sql语句，从而减少网络交互次数，提高批量插入的效率。
需要加在yml配置文件中：
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?rewritebatchedstatements=true&usessl=false
username: root
password: root六、mybatis-plus提供的insertbatchsomecolumn方法
1、编写easysqlinjector 自定义类
public class easysqlinjector extends defaultsqlinjector {
@override
public list<abstractmethod> getmethodlist(class<?> mapperclass, tableinfo tableinfo) {
// 注意：此sql注入器继承了defaultsqlinjector(默认注入器)，调用了defaultsqlinjector的getmethodlist方法，保留了mybatis-plus的自带方法
list<abstractmethod> methodlist = super.getmethodlist(mapperclass, tableinfo);
methodlist.add(new insertbatchsomecolumn(i -> i.getfieldfill() != fieldfill.update));
return methodlist;
}
}
2、定义核心配置类注入此bean
@configuration
public class mybatisplusconfig {
@bean
public easysqlinjector sqlinjector() {
return new easysqlinjector();
}
}
3、编写userservice服务类
public class userservice{
@resource
private usermapper usermapper;
public void insertusers(){
long start = system.currenttimemillis();
list<user> userlist = new arraylist<>();
user user;
for(int i = 0 ;i < 10000; i++) {
user = new user();
user.setusername("name" + i);
user.setpassword("password" + i);
userlist.add(user);
}
usermapper.insertbatchsomecolumn(userlist);
long end = system.currenttimemillis();
system.out.println("一万条数据总耗时：" + (end-start) + "ms" );
}
}
4、编写easybasemapper接口
public interface easybasemapper<t> extends basemapper<t> {
/**
* 批量插入 仅适用于mysql
*
* @param entitylist 实体列表
* @return 影响行数
*/
integer insertbatchsomecolumn(collection<t> entitylist);
}
5、编写usermapper接口
@mapper
public interface usermapper<t> extends easybasemapper<user> {
}
6、单元测试结果
一万条数据总耗时：575ms
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论