java中基于springboot + mybatisPlus 实现excel数据批量导入数据库及数据库时间和实际保存时间不一致问题

1、引入依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mysql连接-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <!--excel导入工具类-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.2</version>
        </dependency>
        <!--druid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>

相关jar包的源码地址：mirrors / alibaba / easyexcel · GitCode

2.定义接口 用于vo和dto实体类之间数据的处理及其他操作

public  interface ExcelReport {

   <T> List handleData(List<T> t);

}

3.添加execl对应实体类及数据库字段对应实体类

@TableName("excel_demo")
public class ExcelDemo implements Serializable , ExcelReport {

    private static final long serialVersionUID = 1L;

    private String stringTitle;

    private String dateTitle;

    private String numTitle;

    public String getStringTitle() {
        return stringTitle;
    }

    public void setStringTitle(String stringTitle) {
        this.stringTitle = stringTitle;
    }
    public String getDateTitle() {
        return dateTitle;
    }

    public void setDateTitle(String dateTitle) {
        this.dateTitle = dateTitle;
    }
    public String getNumTitle() {
        return numTitle;
    }

    public void setNumTitle(String numTitle) {
        this.numTitle = numTitle;
    }

    @Override
    public String toString() {
        return "ExcelDemo{" +
            "stringTitle=" + stringTitle +
            ", dateTitle=" + dateTitle +
            ", numTitle=" + numTitle +
        "}";
    }

    @Override
    public <T> List handleData(List<T> t) {
        List<Object> result = new ArrayList<>();
        //如果需要execl对应字段和数据库字段之间转换可以在这里处理
        t.forEach((x)->{
            ExcelDemoDto excelDemoDto = new ExcelDemoDto();
            ExcelDemo excelDemo = (ExcelDemo)x;
            //将里面的日期由字符串转为日期格式
            //这里转换字符串只是举个例子  实际上日期格式可以用实体对象直接读取到不用转换
            String dateTitle1 = excelDemo.getDateTitle();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            try {
                Date parse = simpleDateFormat.parse(dateTitle1);
                excelDemoDto.setDateTitle(parse);
            } catch (ParseException e) {
                throw new RuntimeException(e);
            }
            excelDemoDto.setStringTitle(excelDemo.getStringTitle());
            excelDemoDto.setNumTitle(excelDemo.getNumTitle());
            result.add(excelDemoDto);
        });
        return result;
    }
}

public class ExcelDemoDto implements Serializable {

    private static final long serialVersionUID = 1L;

    private String stringTitle;

    private Date dateTitle;

    private String numTitle;

    public String getStringTitle() {
        return stringTitle;
    }

    public void setStringTitle(String stringTitle) {
        this.stringTitle = stringTitle;
    }
    public Date getDateTitle() {
        return dateTitle;
    }

    public void setDateTitle(Date dateTitle) {
        this.dateTitle = dateTitle;
    }
    public String getNumTitle() {
        return numTitle;
    }

    public void setNumTitle(String numTitle) {
        this.numTitle = numTitle;
    }

    @Override
    public String toString() {
        return "ExcelDemo{" +
            "stringTitle=" + stringTitle +
            ", dateTitle=" + dateTitle +
            ", numTitle=" + numTitle +
        "}";
    }
}

4.代码从excel导入到vo经过数据处理成dto最后存储到数据库中

@Service
@Slf4j
public class ExcelDemoServiceImpl extends ServiceImpl<ExcelDemoMapper, ExcelDemo> implements IExcelDemoService {

    /**
     * @param t excel对应的实体类
     * @param p 保存excel数据库实体类对应的service（为了调用保存的方法保存数据）
     * @param fileName excel文件存储路径
     * @param <T>
     * @param <P>
     */
    @Override
    public <T extends ExcelReport, P extends IService> void export(T t, P p, String fileName) {
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭2
        EasyExcel.read(fileName, t.getClass(), new ReadListener<T>() {
            //单次缓存的数据
            public static final int BATCH_COUNT = 100;
            private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            @Override
            public void invoke(T data, AnalysisContext context) {
                cachedDataList.add(data);
                if (cachedDataList.size() >= BATCH_COUNT) {
                    saveData();
                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                saveData();
            }
            //加上存储数据库
            private void saveData() {
                log.info("{}条数据，开始存储数据库！", cachedDataList.size());
                p.saveBatch(t.handleData(cachedDataList));
                log.info("存储数据库成功！");
            }
        }).sheet().doRead();
    }
}

5.调用接口导入数据

@RestController
@RequestMapping("/demo")
@AllArgsConstructor
public class ExcelDemoController {

    private final IExcelDemoService excelDemoService;
    @GetMapping("/export")
    public String export(){
        excelDemoService.export(new ExcelDemo(),excelDemoService,"D:\\myproject\\excelDemo\\src\\main\\resources\\demo.xlsx");
        return  "ok";
    }
}

6.excel数据

字符串标题	日期标题	数字标题
字符串0	2020/1/1 1:01	1
字符串1	2020/1/2 1:01	2
字符串2	2020/1/3 1:01	3
字符串3	2020/1/4 1:01	4
字符串4	2020/1/5 1:01	5
字符串5	2020/1/6 1:01	6
字符串6	2020/1/7 1:01	7
字符串7	2020/1/8 1:01	8
字符串8	2020/1/9 1:01	9
字符串9	2020/1/10 1:01	10

7.最后成功导入数据库

发现个问题：数据库里保存的时间和excel里的实际时间不一致

通过排查发现数据库连接设置时区没有使用中国上海的时间。

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
#    之前的
#    url: jdbc:mysql://localhost:3306/cisql?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=UTC
#    改之后的
    url: jdbc:mysql://localhost:3306/cisql?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

改完之后发现时间和excel里的一致了