springboot-数据库数据提取及详细处理

2022-08-12 12:18:50

疫情大数据平台,后台首要的任务是从各种渠道获得数据及对获取后的数据进行处理,本篇主要是记录各类历史数据从数据库中取出,并进行详细处理的过程

主要使用MybatisPlus进行复杂查找

MybatisPlus进行简单查找时较为方便,但进行复杂查找时,需要注意的地方有很多,也比较麻烦

例如

sql语句:sql="SELECT max(confirm),cast(sum(confirm_add) as signed),date_format(date,'%Y-%m') " \"FROM epidemicplatform.province_history " \"where province={} " \"GROUP BY DATE_FORMAT(date, '%Y-%m');"

查询语句中不能出现下划线,及较低版本的sql中,出现在select中的字段也必须出现在group by中,否则会报错

QueryWrapper<Province_history> queryWrapper1=newQueryWrapper<>();
        queryWrapper1.eq("province",region);
        queryWrapper1.groupBy("DATE_FORMAT(date, '%Y-%m')").select("max(confirm) as maxconfirm","DATE_FORMAT(date, '%Y-%m') as d","sum(confirm_add) as sumconfirmadd");

部分提取代码

QueryWrapper<Province_history1> queryWrapper1=newQueryWrapper<>();
        queryWrapper1.eq("province", region);
        queryWrapper1.ge("date","2022-04-01");
        queryWrapper1.groupBy("DATE_FORMAT(date, '%Y-%m-%d')").select("DATE_FORMAT(date, '%Y-%m-%d') as date1","heal_add as sumhealadd","dead_add as sumdeadadd","confirm_add as sumconfirmadd");List<Province_history1> history= province_historyMapper1.selectList(queryWrapper1);
QueryWrapper<Province_history1> queryWrapper3=newQueryWrapper<>();
            queryWrapper3.eq("province", region);
            queryWrapper3.ge("date","2022-05-01");
            queryWrapper3.groupBy("date").select("date");List<Province_history1> daten= province_historyMapperX.selectList(queryWrapper3);
QueryWrapper<Province_history1> queryWrapper1=newQueryWrapper<>();
            queryWrapper1.eq("province", region);
            queryWrapper1.ge("date","2022-05-01");
            queryWrapper1.select("date as date1","confirm as sumconfirmadd");List<Province_history1> history= province_historyMapperX.selectList(queryWrapper1);
QueryWrapper<City_history1> queryWrapper2=newQueryWrapper<>();
            queryWrapper2.eq("province_name", region);
            queryWrapper2.ge("date","2022-05-01");
            queryWrapper2.groupBy("city_name").select("city_name");List<City_history1> cityn= city_historyMapper1.selectList(queryWrapper2);

整体来说,数据提取是较为简单的,较难的是数据处理成需要的样子再传给前端,采用的是手动构造的方法,让部分数据拼接。
其中需要注意的是复杂数组的取数通用性问题,以及部分数据提取失效的问题,取数的是很好由于一些原因,能正常获得长度,但取出的数据全部为null,只能转为其他列名取出,此时取数通用性问题就较为繁琐,因为虽然数据的排序整体有一定的顺序,但是几乎不可能符合前端的格式要求,要根据整体的逻辑进行复制的数据提取

部分代码,由于过于繁琐,就只展示少量处理过程

for(int i=0; i< t_number; i= i+ date_number){if(i!= t_number- date_number)
                    fin= fin+" \""+ history.get(i).getCity()+"\""+",";else
                    fin= fin+" \""+ history.get(i).getCity()+"\""+"]";}for(int i=0; i< date_number; i++){if(i!= date_number-1)
                    fin= fin+" \""+ history.get(i).getDate1()+"\""+",";else
                    fin= fin+" \""+ history.get(i).getDate1()+"\""+"]";}int q=0;for(int i=0; i< t_number; i= i+ date_number){

                fin= fin+"\n"+"   [";for(int x=0; x< date_number; x++){if(q== t_number)break;if(q% date_number!= date_number-1)
                        fin= fin+" "+ history.get(q).getConfirm1()+",";else
                        fin= fin+" "+ history.get(q).getConfirm1()+"]";
                    q++;}if(i!= t_number- date_number)
                    fin= fin+",";else
                    fin= fin+"]";}
for(int i=0; i< date_number; i++){if(i!= date_number-1)
                    fin= fin+" \""+ history.get(i).getDate1()+"\""+",";else
                    fin= fin+" \""+ history.get(i).getDate1()+"\""+"]";}for(int i=0; i< date_number; i++){if(i!= date_number-1)
                    fin= fin+ history.get(i).getSumconfirmadd()+",";else
                    fin= fin+ history.get(i).getSumconfirmadd()+"]]";}
  • 作者:飞鸡炸弹
  • 原文链接:https://blog.csdn.net/aunt_y/article/details/124999142
    更新时间:2022-08-12 12:18:50