spring boot批量写入mysql springboot es批量导入

批量导入数据以用户为例[不贴数据库操作代码,都是简单的插入和查询操作自己写]

• 1 导入依赖
• 2 批量导入模板下载
• 3 批量导入数据

• 准备工作
• 实现代码
• 测试接口

• 4 下载导入失败表格
• 5 实体类
• 6 常见错误

• 1 easyexcel Create workbook failure

1 导入依赖

<!--excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>
        <!--通用mapper-->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.0.4</version>
        </dependency>

2 批量导入模板下载

Result是通用返回类，这里就不贴了，不想写的话可以将返回结果类型改成String或对应的实体类，直接可以copy，无需改动，最多改一下实现层下载文件名
controller 层

@Slf4j
@RestController
@RequestMapping("/user")
public class UserController {
	/*
    用户数据批量导入模板下载
     */
	@GetMapping("/templateDownLoad")
    public Result<Object> templateDownLoad(HttpServletResponse response){
        return userService.templateDownLoad(response);
    }
}

service 层

public interface UserService {
    Result<Object> templateDownLoad(HttpServletResponse response);
}

service 实现层

@Slf4j
@Service
public class UserServiceImpl implements UserService {
	@Override
    public Result<Object> templateDownLoad(HttpServletResponse response) {
       return responseTemplate(response);
    }
    private Result<Object> responseTemplate(HttpServletResponse response) {
    	//路径资源
        ClassPathResource pathResource = new ClassPathResource("/template/用户数据批量导入模板.xlsx");
        try {
            InputStream inputStream = pathResource.getInputStream();
            //输出的文件名称，格式
            String fileName = URLEncoder.encode("userTemplate","UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");
            //输出文件
            ExcelWriter writer = EasyExcel.write(response.getOutputStream()).withTemplate(inputStream).build();
            writer.finish();
            return Result.success();
        } catch (IOException e) {
            log.error("用户数据批量导入模板下载失败");
            return Result.fail("用户数据批量导入模板下载失败");
        }
    }
}

模板位置

浏览器测试

3 批量导入数据

准备工作

数据库表如图

ums_member_import_record 导入信息记录表

ums_member_import_detail 细明表

ums_member 用户信息表

excel模板内容如图

实现代码

复制添加到之前对应的类中即可
controller层

@PostMapping("/excelUpload")
    public Result<Object> excelUpload(MultipartFile file){
        return userService.upload(file);
    }

service层

Result<Object> upload(MultipartFile file);

service实现层

@Override
    public Result<Object> upload(MultipartFile file) {
        return excelUpload(file);
    }
	private Result<Object> excelUpload(MultipartFile file) {
        //用于重命名上传的文件
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        String filePath;
        String filename;
        String newFilename;
        //上传文件保存路径
        String savePath = "D:\\home\\user\\excel";
        if (file!=null&&!file.isEmpty()){
            filename = file.getOriginalFilename();

            String sub = filename.split("\\.")[0];
            String end = filename.split("\\.")[1];
            log.debug(end);
            if (end.equals("xlsx")||end.equals("xls")){
                //重命名上传文件
                newFilename = sub+"_"+format.format(new Date())+end;
                filePath = savePath + "\\"+ newFilename;
                //如果父文件夹不存在就创建
                File newFile = new File(filePath);
                if (!newFile.getParentFile().exists()){
                     if (newFile.getParentFile().mkdirs()){
                         log.debug("文件夹创建成功");
                     }else {
                         return Result.fail("文件夹创建失败");
                     }
                }
                try {
                    //存储上传的文件
                    file.transferTo(newFile);
                } catch (IOException e) {
                    //log.error("文件上传失败");
                    return Result.fail("文件上传失败");
                }
                //此处应该还有一个操作人id的,即登录者的userId,这里是测试接口暂且不用
                UserImportRecord record = UserImportRecord.builder().fileName(filename).newFileName(newFilename).createTime(new Date()).build();
                //返回记录生成的id，以便后续下载导入失败数据，没用通用mapper
                //即要返回插入的自增id作为record id
                userImportRecordMapper.insertRecord(record);
                ExcelLister<UserTemplate> lister = new ExcelLister<>();
                ExcelReader reader = EasyExcel.read(filePath,UserTemplate.class,lister).build();
                ReadSheet sheet = EasyExcel.readSheet(0).build();
                reader.read(sheet);

                List<Object> list = lister.getDataList();
                if (CollectionUtils.isEmpty(list))
                    return Result.fail("请按模板导入数据");
                int failNum =0;
                for(Object data : list){
                    UserImportFail fail = new UserImportFail();
                    UserImportDetail detail = new UserImportDetail();

                    UserTemplate template = (UserTemplate) data;
                    //安字段名赋值
                    BeanUtils.copyProperties(template,detail);
                    detail.setRecordId(record.getId());
                    //设置状态1-成功，2-失败
                    detail.setStatus(1);

                    //效验数据，失败的不导入
                    if(!checkImportData(template,fail)){
                        failNum++;
                        detail.setStatus(2);
                        detail.setReason(fail.getReason());
                        detail.setCreateTime(new Date());
                        userImportDetailMapper.insertSelective(detail);
                        continue;
                    }
                    User user = new User();
                    BeanUtils.copyProperties(template,user);
                    //设置默认密码，要加密的自己写
                    //user.setPassword(encode("123456"));
                    user.setPassword("123456");
                    user.setStatus(1);
                    user.setLevel(1);
                    //user.setIcon(defaultUrl);
                    user.setCreateTime(new Date());
                    try {
                        userMapper.insertSelective(user);
                    } catch (Exception e) {
                        log.error("用户数据插入失败：{}",e.toString());
                        failNum++;
                       detail.setStatus(2);
                       detail.setReason("账号生成失败");
                       userImportDetailMapper.insertSelective(detail);
                    }
                    userImportDetailMapper.insertSelective(detail);
                }
                record.setAllNum(list.size());
                record.setFailNum(failNum);
                record.setSucceedNum(list.size()-failNum);
                record.setCreateTime(new Date());
                userImportRecordMapper.updateByPrimaryKeySelective(record);
                UserImportRecordDto recordDto = new UserImportRecordDto();
                BeanUtils.copyProperties(record,recordDto);
                return Result.success(recordDto);
            }else {
                return Result.fail("请上传excel文件");
            }
        }else {
            return Result.fail("文件不能为空");
        }
    }
    //效验数据，根据需求自己更改
    private boolean checkImportData(UserTemplate template, UserImportFail fail) {
            BeanUtils.copyProperties(template,fail);
            if(isEmpty(template.getUsername())){
                fail.setReason("用户名不能为空");
                return false;
            }
            if(isEmpty(template.getName())){
                fail.setReason("姓名不能为空");
                return false;
            }
            if(isEmpty(template.getPhone())){
                fail.setReason("手机号不能为空");
                return false;
            }
            if(isEmpty(template.getGender())){
                fail.setReason("姓别不能为空");
                return false;
            }
            if(isEmpty(template.getCity())){
                fail.setReason("所在地不能为空");
                return false;
            }
            if(isEmpty(template.getBirthday())){
                fail.setReason("生日不能为空");
                return false;
            }
            Example e = new Example(User.class);
            e.createCriteria().andEqualTo("username",template.getUsername());
            int count = userMapper.selectCountByExample(e);
            if (count > 0){
                fail.setReason("用户名已注册");
                return false;
            }
            e = new Example(User.class);
            e.createCriteria().andEqualTo("phone",template.getPhone());
            count = userMapper.selectCountByExample(e);
            if (count > 0){
                fail.setReason("手机号已绑定");
                return false;
            }
        return true;
    }
    private boolean isEmpty(String str){
        return str == null || str.trim().equals("");
    }

测试接口

测试数据共30个数据，预计失败8个

使用posman 测试

结果符合预期结果

4 下载导入失败表格

controller层

@GetMapping("/failUploadDownload/{code}")
    public Result<Object> failUploadDownload(@PathVariable("code")String code,HttpServletResponse response){
        return userService.failUploadDownload(code,response);
    }

service层

Result<Object> failUploadDownload(String code,HttpServletResponse response);

service实现层

@Override
    public Result<Object> failUploadDownload(String code, HttpServletResponse response) {
        Example example = new Example(UserImportDetail.class);
        example.createCriteria().andEqualTo("status",2);
        example.createCriteria().andEqualTo("recordId",code);
        List<UserImportDetail> userImportDetails = userImportDetailMapper.selectByExample(example);

        try {
            ClassPathResource resource = new ClassPathResource("/template/导入失败模板.xlsx");
            String filename  = URLEncoder.encode("result","UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+filename+".xlsx");
            ExcelWriter writer = EasyExcel.write(response.getOutputStream()).withTemplate(resource.getInputStream()).build();
            writer.fill(userImportDetails,EasyExcel.writerSheet(0).build());
            writer.finish();
            return Result.success();
        } catch (Exception e) {
            log.error("下载导入结果失败:{}",e.toString());
            return Result.fail("下载导入结果失败");
        }
    }

模板位置及内容

浏览器测试下载

下载结果

与预期结果相同，excel 批量导入数据到此基本完成

5 实体类

User

@Data
@Table(name = "ums_member")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    private String password;
    private String name;
    private String phone;
    private Integer status;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
    private String icon;
    private String gender;
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date birthday;
    private String city;
    private Integer level;
}

UserImportRecordDto 插入结果返回信息

@Data
public class UserImportRecordDto {
    /*
    返回该次导入记录号
     */
    private Long id;

    //总数
    private Integer allNum;

    //成功数量
    private Integer succeedNum;

    //失败数量
    private Integer failNum;

}

UserImportDetail 详细实体类

@Data
@Table(name = "ums_member_import_detail")
public class UserImportDetail {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private String id;
    @Column(name = "record_id")
    private Long recordId;
    @Column(name = "username")
    private String username;
    @Column(name = "name")
    private String name;
    @Column(name = "phone")
    private String phone;
    @Column(name = "gender")
    private String gender;
    @Column(name = "birthday")
    private String birthday;
    @Column(name = "city")
    private String city;
    @Column(name = "status")
    private Integer status;
    @Column(name = "reason")
    private String reason;
    @Column(name = "create_time")
    private Date createTime;
    @Column(name = "update_time")
    private Date updateTime;

}

UserImportFail 失败实体类

@Data
public class UserImportFail {

    private String username;
    private String name;
    private String phone;
    private String gender;
    private String birthday;
    private String city;
    private String status;
    private String reason;
}

UserImportRecord 导入记录实体类

@Data
@Builder
@Table(name = "ums_member_import_record")
public class UserImportRecord implements Serializable{

        private static final long serialVersionUID = 447595778318537938L;

        @Id
        @Column(name = "id")
        private Long id;

        @Column(name = "user_id")
        private Long userId;

        @Column(name = "file_name")
        private String fileName;

        @Column(name = "new_file_name")
        private String newFileName;

        @Column(name = "all_num")
        private Integer allNum;

        @Column(name = "succeed_num")
        private Integer succeedNum;

        @Column(name = "fail_num")
        private Integer failNum;

        @Column(name = "fail_file_name")
        private String failFileName;

        @Column(name = "create_time")
        private Date createTime;

        @Column(name = "update_time")
        private Date updateTime;

}

UserTemplate excel获取模版实体类

@Data
public class UserTemplate {

    @ExcelProperty(index = 0)
    private String username;
    @ExcelProperty(index = 1)
    private String name;
    @ExcelProperty(index = 2)
    private String phone;
    @ExcelProperty(index = 3)
    private String gender;
    @ExcelProperty(index = 4)
    private String birthday;
    @ExcelProperty(index = 5)
    private String city;

}

6 常见错误

1 easyexcel Create workbook failure

解决方法
在配置文件中添加

<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-resources-plugin</artifactId>
				<configuration>
					<nonFilteredFileExtensions>
						<nonFilteredFileExtension>xls</nonFilteredFileExtension>
						<nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
					</nonFilteredFileExtensions>
				</configuration>
			</plugin>