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

浅浅的花香味﹌ 2024-03-26 16:42 76阅读 0赞

**目录**

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的值保存到数据库**

![9847fc077aad4bdb8cbdc90590bc26dd.png][]

### 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：准备数据 ####

![25ebc550fd0e48edb15a46e7b992baf2.png][]

#### 11.2：Postman测试 ####

![9a00a8e962084432a946508d608e3ac4.png][]

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

![af7fc048dc27472882491b3caae1cd97.png][]

#### 注意！！！：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>

[9847fc077aad4bdb8cbdc90590bc26dd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/df408df08d0348dca028cf481a8d9348.png
[25ebc550fd0e48edb15a46e7b992baf2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ba1498ef78374480a0bfe96c02da645d.png
[9a00a8e962084432a946508d608e3ac4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/2d0196f44d2c4148a92d43aa1b3ab64d.png
[af7fc048dc27472882491b3caae1cd97.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7409a62ccf8b43ecafc614259ba5e8b7.png