java导入excel表格数据的功能实现,以及五次变更升级记录,防止重蹈覆辙

作者: admin 分类: 码农职场 发布时间: 2019-07-26 01:57  阅读: 405 views

背景

程序猿美名曰高薪职业,现实就是码奴。最近被派到兄弟公司做支持,也就是过来加加班,赶赶工期。之前公司的架构是dubbo框架,这里的是springCloud,没有怎么接触过,但是代码学习搬运起来还是比较快的。做边角料功能的功夫逐渐了解了现有项目的使用,开始接触编码业务逻辑部分。这不是最近刚接到一个订单相关的需求,其中有个excel的导入功能,让我这种技术差的人埋了个大坑。(公司业务新启,用户暴增)

需求

三方整理用户订单(用户名、手机号、订单号)  -> excel数据导入 -> 导入数据后续处理

第一版

工期短,所以没有太考虑实际业务量。一想到导入excel功能,读取文件那肯定是用poi喽,按照范例读取每一行,然后读取每一个cell单元格。获取到对象后,插入不是更简单了么,之前有单个插入方法,循环调用它不就好了么。整体如下:
package com.chl;

public class Example {

    //单个保存
    public int saveOrder(Object obj) {

        //validate 对象信息

        //validate 业务流程

        //validate 库存信息

        //插入操作

        //相关业务变动

        return 1;
    }


    //批量保存
    public int saveOrderBatch(String filePath) {

        //validate filePath参数

        //解析excel

        //validate excel参数

        //循环插入
        int i = 0;
        for(;;) {
            i+ = saveOrder(obj);
        }

        //返回成功数
        return i;
    }
}

由于有现成的保存方法,所以调用传参就行。随便建立了一个10条左右的测试数据到excel中,执行调用,ok! 开始开发下一个功能

产生问题

接口写好了,前端拿我的示例excel去调用,偶尔成功,偶尔超时。原来前端做了接口的响应验证,超过5s的都算调用失败。一个戴眼镜的前端大声咆哮着:”超时了,超时了…”,作为一个老鸟,听了肯定感觉不舒服,怎么着也得证明自己还未被淘汰吧。

第二版

看了下编写的业务逻辑,觉得是有挺多重复调用的验证逻辑,本来执行一次就行,实际代码逻辑是excel中存在一条记录就执行一次。于是做了调整。代码如下:
package com.chl;

public class Example {

    public void validate() {
        //validate 对象信息

        //validate 业务流程

        //validate 库存信息
    }

    //单个保存
    public int saveOrder(Object obj) {

        //插入操作

        //相关业务变动

        return 1;
    }


    //批量保存
    public int saveOrderBatch(String filePath) {

        //validate filePath参数

        //validate excel参数

        validate();

        //解析excel
        int i = 0;
        for(;;) {
            i+ = saveOrder(obj);
        }

        return i;
    }
}

把原来的每次都要执行,调整为了前期执行一次。减少了数据库的连接查询、与重复的验证逻辑。自然是快了些。前段试了几次,没有发生超时现象,提交给了测试。

产生问题

测试接到了任务,马上动手测试了起来。先新建excel文档,然后写好表头,之后写入了几条连续的数据。一切看起来很正常也很美好。最后,她优雅的划着鼠标移动,终于使得白色箭头在了excel的表格线交汇处编程了黑色加粗的小加号,残忍的向下拉3000行之多。执行导入功能,果不其然的超时了。我开始觉得测试吹毛求疵,哪会有这么多的数据量,后来经业务部门证实”有渠道一次性5w数据,你看的办吧”。实际情况都这样了,只能改了。毕竟工作了这些年,没吃过猪肉,那也是见过猪跑的。

第三版

如有大批量的数据要插入数据库中,如果一条数据执行一次数据库操作的话,要频繁的连接数据库,就算使用连接池也会短时占用大量资源。一般的做法是:1.规范要导入的文件,用数据库客户端或命令执行导入操作。2.通过代码拼接一条很长的批量插入语句,执行一次数据库操作后全部插入。由于是业务部门进行导入操作,那肯定是通过界面进行导入操作,相关的代码如下:
package com.chl;

public class Example {

    public void validate() {
        //validate 对象信息

        //validate 业务流程

        //validate 库存信息
    }

    //批量保存
    public int saveOrderBatch(String filePath) {

        //validate filePath参数

        //validate excel参数

        validate();

        //解析excel并拼接

        StringBuffer sql = new StringBuffer(" insert into tables (column1,column2,column3 ... ) values ");
                     sql.append(" (value1,value2,value3 ...)");
                     sql.append(" (value1,value2,value3 ...)");
                     sql.append(" ...");
                     sql.append(" (value1,value2,value3 ...)");
                     sql.append(" (value1,value2,value3 ...)");

        //执行一次插入

        return i;
    }

    /**
     * 定时任务
     * 循环处理插入数据的其他列信息
     */
    @Scheduled(cron = "0 10 * * * ?")
    public void autoDealBaseInfo() {

        for(;;) {

            //唯一订单号、会员卡号、会员密码

            //相关业务变动
        }

    }
}

给数据增加了处理中,已完成的状态。然后把所有数据拼接成了一个大sql进行单次插入操作。并把耗时的订单号、卡号、密码生成逻辑移动到定时任务中处理。前端能够立马拿到插入结果,解决了超时5s的问题。定时任务处理完后,修改相关数据的状态为已完成,时间一天之内都可以接受。自己做了5000条数据,执行导入功能很快就成功,定时任务也正常的处理了相关的业务数据。长舒一口气后提交给测试,测试理所当然的制造了3w条数据进行尝试,也正常。皆大欢喜之后上线了。
– 优化
1. 为了防止拼接的sql过于庞大,超过sql语句默认长度1M,所以拆分5000条数据为一个sql。3W条数据也就执行6次数据库插入。
2. insert into table() values() ;insert into table() values() ;…. 的执行效率低于insert into table() values(),values(),values(),

产生问题

业务部门得知新功能上线了,非常高兴,终于不用手动录入一条一条的数据了。立马汇总了一份12000多条的名单到excel中,进行导入功能的操作。先分批尝试了1000条数据的导入,正常,然后一次性操作剩下的名单数据。但是只成功了5000条,剩下的没有插入成功。还有一个人整理的数据缺胳膊少腿,插入成功造成了脏数据的产生。

第四版

通过查看业务日志发现,插入没有成功的原因是因为整理了非法数据(')。将第二条大sql截断了 insert into table() values('),(') 。导致没法正常执行。这是前期的数据校验没有处理好。所以开始了数据格式验证的处理

处理逻辑
– 判断excel的总行数和解析的总数是否匹配(有时空数据行因为一些操作认为是一条有效数据)
– 判断excel的每一个cell单元格是否为null(业务上不允许null)
– 检查手机号格式、长度(有时业务人员会复制一些看不到的字符到单元格中,如我碰到的是看起来手机号是11位,但实际trim(mobile)后获取到的居然是12位。可能是换行、制表符什么的
– 判断字符串类型的数据中是否包含’等字符
– 判断excel的其他数据格式等
– 对验证不符的信息进行有效返回(有效协助业务人员调整数据)

这里通过对数据的规范、及基本验证,基本杜绝了由数据本身引发的程序错误等。

产生问题

由于线上环境用户量多、操作频繁,不同任务之间可能存在资源上的竞争或冲突。一次性或者定时的操作大量数据,会对其他业务产生影响。尤其是在对一些方法、接口做大量测试的情况下,经常突然一些奇怪的问题。这里发现了一个问题是:有个同事写了一个生成唯一订单号和密码的方法,用了redis锁、加密算法、位运算等,正好在我的执行逻辑中用到。单个操作的时候没有异常,当数据量一大的时候就会产生重复数据、错误数据等。影响了业务的正常执行。

第五版

当然,到了这里导入功能本身是没有问题了,但是其引发的问题是不容小觑的。很多接口、方法调用一次、两次是不会发现问题的,但是数据量一旦上去,量变引起质变。

处理解疑过程(简单记录、不深入分析过程)
1. redis锁问题,程序中为了保证数据一致性,业务正确性很多地方用了redis锁。但是jedis客户端的连接方式太过初级,没有使用连接池对象,造成了资源性能的损耗。一个获取方法执行10000次居然要18分钟左右,且中间产生redis lock wait现象。使用连接池之后,有明显提升,3000条3分钟左右。

  1. 订单唯一性问题,这里取了时间戳部分截取 + 随机数的方式生成唯一编号,以前单个增加订单操作的时候绝对不会产生问题。但是批量操作时,时间误差到达了微秒级别,截取位置不对会导致数据的重复生成,调整之前100条数据会产生8组左右重复数据,由System.currentTimeMillis()换为System.nanoTime()后并做了截取处理后测试2w条不会出现重复。

  2. 资源占用问题,一些对象没有及时释放掉,导致部分业务没法正常执行。

  3. 数据修复问题,由于各种原因造成了部分数据错误,可怜的程序猿们还在紧张的奋斗在一线,努力的修复着数据。黑眼圈所以更重了,头发所以更白了。

产生问题

由excel导入的功能引发的问题,可能还有很多,还在持续发酵中…

总结

功能虽小、但有时候牵一发而动全身。程序猿们估计最不愿意改的代码就是一些底层代码了。那种痛苦、那种工作量,想想都发晕。所以编码的时候要未雨绸缪啊(虽然时间上、代码量上不允许)
– 要多考虑、多角度分析
– 可以利用jmeter做一些简单的性能测试
– 可以学习开源的优秀代码
– 可以不断优化、改进自己的代码
– 可以不做程序猿(还我的黑发和明眸)


   原创文章,转载请标明本文链接: java导入excel表格数据的功能实现,以及五次变更升级记录,防止重蹈覆辙

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

一条评论
  • 137博客

    2019年7月30日 10:54

    学到了不少东西!我的博客,欢迎回访

发表评论

电子邮件地址不会被公开。 必填项已用*标注

更多阅读