关于mybatis的三种批量插入以及效率比较
1.表结构
CREATE TABLE `t_user` (
`id` varchar(32) CHARACTER SET utf8 NOT NULL COMMENT '主键',
`name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户名',
`del_flag` char(1) CHARACTER SET utf8 DEFAULT NULL COMMENT '删除标示',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2.1 jdbc.properties配置
mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://127.0.0.1:3306/ssm
mysql.username=root
mysql.password=admin
#定义初始连接数
mysql.initialSize=1
#定义最大连接数
mysql.maxActive=20
#定义最大空闲
mysql.maxIdle=20
#定义最小空闲
mysql.minIdle=1
#定义最长等待时间
mysql.maxWait=60000
2.2 spring-mybatis.xml配置
<context:component-scan base-package="com.win.ssm"/>
<context:property-placeholder location="classpath:jdbc.properties"/>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${mysql.driver}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
<!-- 初始化链接大小-->
<property name="initialSize" value="${mysql.initialSize}"/>
<!-- 连接池最大数量-->
<property name="maxActive" value="${mysql.maxActive}"/>
<!-- 连接池最大空闲-->
<property name="maxIdle" value="${mysql.maxIdle}"/>
<!-- 连接池最小空闲 -->
<property name="minIdle" value="${mysql.minIdle}"></property>
<!-- 获取连接最大等待时间-->
<property name="maxWait" value="${mysql.maxWait}"/>
</bean>
<!-- spring与mybatis整合类 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<!-- 查找接口的别名 -->
<property name="typeAliasesPackage" value="com.win"/>
<!-- 自动扫描mapping.xml文件-->
<property name="mapperLocations" value="classpath:/mapping/*.xml"/>
</bean>
<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSessionFactory" />
<!--<constructor-arg index="1" value="BATCH" />-->
</bean>
<!-- 扫描DAO接口 -->
<bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.win.ssm.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- 事务管理 -->
<bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
第一种：普通for循环插入
①junit类
@Test
public void testInsertBatch2() throws Exception {
long start = System.currentTimeMillis();
User user;
SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(false);
UserDao mapper = sqlSession.getMapper(UserDao.class);
for (int i = 0; i < 500; i++) {
user = new User();
user.setId("test" + i);
user.setName("name" + i);
user.setDelFlag("0");
mapper.insert(user);
}
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
}
②xml配置
<insert id="insert">
INSERT INTO t_user (id, name, del_flag)
VALUES(#{id}, #{name}, #{delFlag})
</insert>
第二种：mybatis BATCH模式插入
①junit类
@Test
public void testInsertBatch2() throws Exception {
long start = System.currentTimeMillis();
User user;
SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//跟上述sql区别
UserDao mapper = sqlSession.getMapper(UserDao.class);
for (int i = 0; i < 500; i++) {
user = new User();
user.setId("test" + i);
user.setName("name" + i);
user.setDelFlag("0");
mapper.insert(user);
}
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
}
②xml配置与第一种②中使用相同
第三种：foreach方式插入
①junit类
@Test
public void testInsertBatch() throws Exception {
long start = System.currentTimeMillis();
List<User> list = new ArrayList<>();
User user;
for (int i = 0; i < 10000; i++) {
user = new User();
user.setId("test" + i);
user.setName("name" + i);
user.setDelFlag("0");
list.add(user);
}
userService.insertBatch(list);
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
}
②xml配置
<insert id="insertBatch">
INSERT INTO t_user
(id, name, del_flag)
VALUES
<foreach collection ="list" item="user" separator =",">
(#{user.id}, #{user.name}, #{user.delFlag})
</foreach >
</insert>
特别注意：mysql默认接受sql的大小是1048576(1M)，即第三种方式若数据量超过1M会报如下异常：（可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的＂max_allowed_packet = 1M＂）
nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576).
You can change this value on the server by setting the max_allowed_packet' variable.
结果对比：
| 条 | 第一种 | 第二种 | 第二种 |
|---|---|---|---|
| 500条 | 7742 | 7388 | 622 |
| 1000条 | 15290 | 15078 | 746 |
| 5000条 | 78011 | 177350 | 1172 |
| 10000条 | 397472 | 201180 | 1205 |
项目实例:
service:
/**
* DatePermission 表数据服务层接口
*/
public interface DatePermissionService {
/**
* 新增用户所具有的仓库
*/
void save(Long userId,List<Long> list);
}
serviceimpl:
@Slf4j
@Service
public class DatePermissionServiceImpl implements DatePermissionService {
@Autowired
private DatePermissionMapper datePermissionMapper;
@Override
public void save(Long userId, List<Long> list) {
List<DatePermissionEntity> entityList = new ArrayList<>();
for (Long aLong : list) {
DatePermissionEntity entity = new DatePermissionEntity();
entity.setUserId(userId);
entity.setWarehousesId(aLong);
entityList.add(entity);
}
datePermissionMapper.insertBatch(entityList);
}
}
mapper:
public interface DatePermissionMapper extends BaseMapper<DatePermissionEntity> {
void insertBatch(List<DatePermissionEntity> list);
}
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="xxx.DatePermissionMapper">
<insert id="insertBatch">
INSERT INTO user_warehouses
(user_id, warehouses_id)
VALUES
<foreach collection ="list" item="datePermissionEntity" separator =",">
(#{datePermissionEntity.userId}, #{datePermissionEntity.warehousesId})
</foreach >
</insert>
</mapper>
实体类:
@TableName(value = "user_warehouses")
public class DatePermissionEntity extends BaseEntity {
@TableField(exist = false)
private static final long serialVersionUID = 1L;
/**
* 主键
*/
@TableId(type = IdType.AUTO)
private Long id;
/**
* 用户Id
*/
private Long userId;
/**
* 仓库Id
*/
private Long warehousesId;
get/set(略)
表字段:
测试结果:
关于分割字符串(按逗号分割字符串/字符串分割成数组)
分割字符串
List<Long> ids = Arrays.asList(roleIds.split(",")).stream().map(s ->
Long.parseLong(s.trim())).collect(Collectors.toList());
项目实例:
@PostMapping("delete")
public JsonResult delete(String roleIds) {
List<Long> ids = Arrays.asList(roleIds.split(",")).stream().map(s -> Long.parseLong(s.trim())).collect(Collectors.toList());
for (Long id : ids) {
MagGuserRoleEntity guserRoleEntity = new MagGuserRoleEntity();
guserRoleEntity.setRoleId(id);
MagStaffRoleEntity staffRoleEntity = new MagStaffRoleEntity();
staffRoleEntity.setRoleId(id);
Integer guserRoleCnt = magGuserRoleService.selectCount(guserRoleEntity);
Integer staffRoleCnt = magStaffRoleService.selectCount(staffRoleEntity);
if (guserRoleCnt > 0 || staffRoleCnt > 0) {
MagRoleEntity role = magRoleService.selectById(id);
return JsonResult.resultError("角色[" + role.getName() + "]已被使用，不可删除!");
}
}
magRoleService.deleteBatchIds(ids);
return JsonResult.resultSuccess("删除成功!");
}
同样的组装字符串/拼接字符串
info.setPermissionIds(Joiner.on(",").join(permIds));
项目实例:
@RequestMapping("info")
public JsonResult info(Long id) {
PerFrontUserEntity entity = userSessionRedisUtil.getSessionUserObjInfo(request);
if (id == null || id < 0) {
return JsonResult.resultError("参数错误");
}
MagRoleEntity qo = new MagRoleEntity();
qo.setId(id);
qo.setTenId(entity.getTenId());
// qo.setTenId(getTenId(entity.getTenId()));
qo.setGuserId(entity.getGuserId());
MagRoleEntity info = magRoleService.findInfo(qo);
if (info != null) {
//角色拥有的所有权限(包含子、父级)
List<Long> permIds = magRolePermissionService.getPermIdsByRole(id);
//角色拥有的父权限
List<Long> pIds = new ArrayList<>();
if (!CollectionUtils.isEmpty(permIds)) {
for (int i = 0; i < permIds.size(); i++) {
MagPermissionEntity permissionEntity = magPermissionService.selectById(permIds.get(i));
//排除一级父权限
if (permissionEntity.getPid() != null) {
pIds.add(permissionEntity.getPid().longValue());
}
}
permIds.removeAll(pIds);
}
info.setPermissionIds(Joiner.on(",").join(permIds));
return JsonResult.resultSuccess(info);
}
return JsonResult.resultError("该角色不存在");
}