excel批量插入数据库
使用hutool中的excel工具类
导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
package com.zgh.exam.controller;
import com.zgh.exam.service.FileInService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
@Api(tags = "用户信息批量导入")
@RestController
@RequestMapping("/exam/v1/file")
public class FileInController {
@Autowired
private FileInService fileInService;
@ApiOperation("excel批量导入数据库")
@PostMapping("/import")
public String importExcel(MultipartFile file) throws IOException {
return fileInService.excelIn(file);
}
}
package com.zgh.exam.service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
public interface FileInService {
/**
* excel导入数据库
* @param file
* @throws IOException
*/
String excelIn(MultipartFile file) throws IOException;
}
package com.zgh.exam.service.impl;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.zgh.exam.dto.UserDto;
import com.zgh.exam.service.FileInService;
import com.zgh.exam.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
@Service
@Slf4j
public class FileInServiceImpl implements FileInService {
@Autowired
private UserService userService;
@Override
public String excelIn(MultipartFile file) throws IOException {
//请求进入时间
long start = System.currentTimeMillis();
//文件处理成io流
InputStream in = file.getInputStream();
//io流给ExcelReader
ExcelReader excelReader = ExcelUtil.getReader(in);
//忽略第一行头(第一行是中文的情况),直接读取表的内容
List<List<Object>> list = excelReader.read(1);
List<UserDto> dtoList = CollUtil.newArrayList();
//获取所有的List<UserDto>
for (List<Object> row : list) {
UserDto userDto = new UserDto();
//excel内属性
userDto.setId(row.get(0).toString());
userDto.setUserName(row.get(1).toString());
userDto.setJobNum(row.get(2).toString());
userDto.setGender((byte) ("男".equals(row.get(3)) ? 1 : 0));
userDto.setEmail(row.get(4).toString());
userDto.setZiCompany(row.get(5).toString());
userDto.setPositionx(row.get(6).toString());
userDto.setDescx(row.get(7).toString());
//表须有属性
userDto.setDelete((byte) 0);
userDto.setCreateId("系统默认");
userDto.setUpdateId("系统默认");
userDto.setCreateTime(LocalDateTime.now());
userDto.setUpdateTime(LocalDateTime.now());
dtoList.add(userDto);
//****类似一一对应****
}
//excel数据读取完毕,准备入库
long inStart = System.currentTimeMillis();
//===============================多线程 进行 批量插入============================================
List<List<UserDto>> lists = ListUtil.split(dtoList, 1000);
for (List<UserDto> userDtoList : lists) {
ExecutorService es = Executors.newFixedThreadPool(16);
es.execute(new Runnable() {
@Override
public void run() {
userService.batchInsert(userDtoList);
}
});
}
//==================================多线程 进行 批量插入======================================================
long inEnd = System.currentTimeMillis();
long inTime = inEnd - inStart;
log.info(" 插入耗费时间:{}", inTime);
//请求结束时间
long end = System.currentTimeMillis();
long allTime = end - start;
log.info(" 总耗费时间:{}", allTime);
String res = "插入耗费时间:" + inTime + " ,总耗费时间:" + allTime;
return res;
}
}
package com.zgh.exam.service;
import com.zgh.exam.dto.UserDto;
import java.util.List;
public interface UserService {
/**
* 批量插入
*
* @param list
* @return
*/
int batchInsert(List<UserDto> list);
}
package com.zgh.exam.service.impl;
import cn.hutool.core.bean.BeanUtil;
import com.zgh.exam.dao.UserMapper;
import com.zgh.exam.dto.UserDto;
import com.zgh.exam.entity.User;
import com.zgh.exam.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Resource
private UserMapper userMapper;
/**
* 批量插入
*
* @param list
* @return
*/
@Override
public int batchInsert(List<UserDto> list) {
List<User> userList = BeanUtil.copyToList(list, User.class);
return userMapper.batchInsert(userList);
}
}
package com.zgh.exam.dao;
import com.zgh.exam.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
@Mapper
public interface UserMapper {
/**
* 批量插入
*
* @param list
* @return
*/
int batchInsert(@Param("list") List<User> list);
}
<?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.zgh.exam.dao.UserMapper">
<resultMap id="BaseResultMap" type="com.zgh.exam.entity.User">
<id column="id" jdbcType="VARCHAR" property="id"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="job_num" jdbcType="VARCHAR" property="jobNum"/>
<result column="gender" jdbcType="TINYINT" property="gender"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="zi_company" jdbcType="VARCHAR" property="ziCompany"/>
<result column="positionx" jdbcType="VARCHAR" property="positionx"/>
<result column="descx" jdbcType="VARCHAR" property="descx"/>
<result column="is_delete" jdbcType="TINYINT" property="delete"/>
<result column="create_id" jdbcType="VARCHAR" property="createId"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_id" jdbcType="VARCHAR" property="updateId"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<sql id="Base_Column_List">
id,
user_name,
job_num,
gender,
email,
zi_company,
positionx,
descx,
is_delete,
create_id,
create_time,
update_id,
update_time
</sql>
<insert id="batchInsert">
insert into user
(id, user_name, job_num, gender, email, zi_company, positionx, descx, is_delete,
create_id, create_time, update_id, update_time)
values
<foreach collection="list" item="item" separator=",">
(#{item.id,jdbcType=VARCHAR}, #{item.userName,jdbcType=VARCHAR}, #{item.jobNum,jdbcType=VARCHAR},
#{item.gender,jdbcType=TINYINT}, #{item.email,jdbcType=VARCHAR}, #{item.ziCompany,jdbcType=VARCHAR},
#{item.positionx,jdbcType=VARCHAR}, #{item.descx,jdbcType=VARCHAR}, #{item.delete,jdbcType=TINYINT},
#{item.createId,jdbcType=VARCHAR}, #{item.createTime,jdbcType=TIMESTAMP},
#{item.updateId,jdbcType=VARCHAR},
#{item.updateTime,jdbcType=TIMESTAMP})
</foreach>
</insert>
</mapper>