Java后端：Excel文件上传，解析数据到数据库

SUMMERENT

已于 2023-08-25 15:29:16 修改

阅读量4.8k

点赞数

分类专栏： java 文章标签： java 数据库 mybatis

于 2023-03-03 13:48:09 首次发布

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/SUMMERENT/article/details/129305150

版权

java 专栏收录该内容

22 篇文章 2 订阅

订阅专栏

目录

1、实体类层：Student

2、Mapper层：StudentMapper

3、StudentMapper.xml

4、service层：StudentService

5、实现类层：StudentServiceImpl

6、Controller层：StudentController

7、Dto：添加上传由此类先进行解析

8、工具类：Utils

8.1：ResponseResult

8.2：ValidatorUtils

8.3：BusinessException

9、DemoApplication启动类

10、application.yml 配置文件

11、准备数据和Postman测试

11.1：准备数据

11.2：Postman测试

使用的是mysql数据库，上传成功

注意！！！：pom.xml配置，需要导入的Maven在这里，案例使用的是mysql数据库

以学生表为例:解析Excel表中Name、Age的值保存到数据库

1、实体类层：Student

package coo.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;

import java.util.Date;

@Data
@TableName("STUDENT_DEMO")
public class Student {
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;

    @TableField("name")
    private String name;

    @TableField("age")
    private String age;

    @TableField(value = "create_time", fill = FieldFill.INSERT)
    private Date createTime;

    @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;

    @TableField(value = "deleted_flag", fill = FieldFill.INSERT)
    @TableLogic
    private Integer deletedFlag;
    /**
     * 业务主键
     * @return String
     */
    public String getBusinessPrimaryKey(){
        return this.age+this.name;
    }

}

2、Mapper层：StudentMapper

package coo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import coo.entity.Student;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface StudentMapper extends BaseMapper<Student> {

}

3、StudentMapper.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="coo.mapper.StudentMapper">

</mapper>

4、service层：StudentService

package coo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import coo.entity.Student;

import java.util.List;

public interface StudentService extends IService<Student> {

    boolean isNotExists(Student student) throws Exception;

    void uploadStudent(List<Student> stdProducts) throws Exception;
}

5、实现类层：StudentServiceImpl

package coo.service.impl;

import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import coo.entity.Student;
import coo.mapper.StudentMapper;
import coo.service.StudentService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.transaction.annotation.Transactional;

import javax.xml.rpc.ServiceException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {

    @Override
    public boolean isNotExists(Student student) throws Exception {
        if (ObjectUtil.isEmpty(student) || ObjectUtil.isEmpty(student.getName())) {
            throw new ServiceException("Name 不能为空");
        }
        LambdaQueryWrapper<Student> queryWrapper = new LambdaQueryWrapper();
        queryWrapper.eq(Student::getDeletedFlag,0);
        queryWrapper.eq(StringUtils.isNotBlank(student.getAge()),Student::getAge, student.getAge());
        queryWrapper.eq(StringUtils.isNotBlank(student.getName()),Student::getName, student.getName());

        return this.count(queryWrapper) < 1;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void uploadStudent(List<Student> students) throws Exception {
        List<Student> addList = new ArrayList<>();
        for (Student student : students) {
            //判断主键是否重复
            if (this.isNotExists(student)) {
                //新增
                student.setCreateTime(new Date());
                student.setUpdateTime(new Date());
                student.setDeletedFlag(0);
                addList.add(student);
            } else {
                //更新
                student.setUpdateTime(new Date());
                LambdaQueryWrapper<Student> queryWrapper = new LambdaQueryWrapper<>();
                queryWrapper.eq(StringUtils.isNotBlank(student.getAge()), Student::getAge, student.getAge());
                queryWrapper.eq(StringUtils.isNotBlank(student.getName()), Student::getName, student.getName());
                queryWrapper.eq(Student::getDeletedFlag, 0);
                this.update(student, queryWrapper);
            }
        }
        if (!addList.isEmpty()) {
            //新增
            this.saveBatch(addList);
        }
    }
}

6、Controller层：StudentController

package coo.controller;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelReader;
import io.swagger.annotations.ApiOperation;
import coo.dto.StudentDto;
import coo.entity.Student;
import coo.exception.BusinessException;
import coo.service.StudentService;
import coo.utils.ResponseResult;
import coo.utils.ValidatorUtils;
import org.apache.commons.lang3.StringUtils;
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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/student")
public class StudentController {
    @Autowired
    private StudentService studentService;
    /**
     * student数据文件上传
     * @param file
     * @return ResponseResult<Void>
     */
    @PostMapping("/upload")
    @ApiOperation(tags = "student页面操作", value = "student数据文件上传")
    public ResponseResult uploadProductId(MultipartFile file) throws Exception {

        if (ObjectUtil.isNull(file)) {
            throw new BusinessException("请检查Excel是否有数据");
        }

        // 文件名的获取 并判断是否是excel
        String originalFilename = file.getOriginalFilename();
        if (StringUtils.isBlank(originalFilename)) {
            throw new BusinessException("上传的文件名为空");
        }
        String fileSuffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
        if (!"xls".equals(fileSuffix) && !"xlsx".equals(fileSuffix)) {
            throw new BusinessException("文件格式不正确,请检查后重新上传!");
        }
        // 设置表头别名
        Map<String, String> headerAlis = new HashMap<>(16);
        String[] studentColumns = {"Name#name", "Age#age"};
        for (String stuExcelExportColumn : studentColumns) {
            String[] split = stuExcelExportColumn.split("#");
            headerAlis.put(split[0], split[1]);
        }
        // 将excel中的数据读取成list
        ExcelReader reader = new ExcelReader(file.getInputStream(), 0);
        reader.setHeaderAlias(headerAlis);
        List<StudentDto> studentDtos = reader.readAll(StudentDto.class);

        // 判断必填项
        for (int i = 0; i < studentDtos.size(); i++) {
            StudentDto studentDto = studentDtos.get(i);
            try {
                // 校验必填项
                ValidatorUtils.validateFast(studentDto);
            }catch (Exception e){
                int j = i+2;
                throw new BusinessException("第" + j + "行 " + e.getMessage());
            }
        }
        List<String> checkList = new ArrayList<>();
        List<Student> students = BeanUtil.copyToList(studentDtos, Student.class);
        for (int i = 0; i < students.size(); i++)  {
            Student student = students.get(i);
            String businessPrimaryKey = student.getBusinessPrimaryKey();
            if (checkList.contains(businessPrimaryKey)){
                int oldRow = checkList.indexOf(businessPrimaryKey) +2;
                int nowRow = i+2;
                throw new BusinessException("第"+nowRow+"行数据和第"+oldRow+"行数据重复,请修改后上传!");
            }
            checkList.add(businessPrimaryKey);
        }
        studentService.uploadStudent(students);
        return ResponseResult.success();
    }
}

7、Dto：添加上传由此类先进行解析

package coo.dto;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.validation.constraints.NotBlank;

@Data
public class StudentDto {
    @ApiModelProperty(value = "name",required = true)
    @NotBlank(message = "Name不能为空，请调整后重新上传!")
    private String name;

    private String age;
}

8、工具类：Utils

8.1：ResponseResult

package coo.utils;

import lombok.Data;

@Data
public class ResponseResult<T> {
     private static final ResponseResult<Void> OK = new ResponseResult<>();
    /**
     * 是否成功标记。
     */
    private boolean success = true;
    /**
     * 错误码。
     */
    private String errorCode = "0";
    /**
     * 错误信息描述。
     */
    private String errorMessage = "NO-MESSAGE";
    /**
     * 实际数据。
     */
    private T data = null;
    /**
     * 创建成功对象。
     * @return 返回创建的ResponseResult实例对象
     */
    public static ResponseResult<Void> success() {
        return OK;
    }
}

8.2：ValidatorUtils

package coo.utils;

import coo.exception.BusinessException;
import org.hibernate.validator.HibernateValidator;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.util.Set;

public class ValidatorUtils {
    private static Validator validatorFast = Validation.byProvider(HibernateValidator.class).configure().failFast(true).buildValidatorFactory().getValidator();

    /**
     * 校验遇到第一个不合法的字段直接返回不合法字段，后续字段不再校验
     */
    public static <T> Set<ConstraintViolation<T>> validateFast(T domain) throws Exception {
        Set<ConstraintViolation<T>> validateResult = validatorFast.validate(domain);
        if (validateResult.size() > 0) {
            throw new BusinessException(validateResult.iterator().next().getPropertyPath() + "：" + validateResult.iterator().next().getMessage());
        }
        return validateResult;
    }
}

8.3：BusinessException

package coo.exception;

public class BusinessException extends Exception {
    private static final long serialVersionUID = -3804995326646218863L;
    private String errCode;
    private String errMsg;
    
    public BusinessException(String errMsg) {
        super(errMsg);
        this.errMsg = errMsg;
    }
}

9、DemoApplication启动类

@EnableAsync
@SpringBootApplication
@MapperScan("coo.mapper")
public class DemoApplication{

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

10、application.yml 配置文件

server:
  port: 8003
logging:
  level:
    com.mapper: debug

spring:
  application:
    name: ceshi
  datasource:
    url: jdbc:mysql://ip地址:端口号/数据库?useSSL=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8
    username: 用户名
    password: 密码
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource


# mapper 接口的扫描
mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml

11、准备数据和Postman测试

11.1：准备数据

11.2：Postman测试

使用的是mysql数据库，上传成功

注意！！！：pom.xml配置，需要导入的Maven在这里，案例使用的是mysql数据库

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>name.remal.gradle-plugins.lombok</groupId>
            <artifactId>lombok</artifactId>
            <version>2.0.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>6.2.5.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>6.2.0.Final</version>
        </dependency>
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>1.6.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.10</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.16</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.18</version>
        </dependency>
        <dependency>
            <groupId>org.ifinalframework.annotation</groupId>
            <artifactId>final-annotation-web</artifactId>
            <version>1.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.axis</groupId>
            <artifactId>axis-jaxrpc</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>1.6.8</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.5.8</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

关注

0
点赞
踩
45

收藏

觉得还不错? 一键收藏
8
评论
Java后端：Excel文件上传，解析数据到数据库

以学生表为例:解析Excel表中Name、Age的值保存到数据库。
复制链接

扫一扫

专栏目录

评论 8

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。