screen-api 增加小时和五分钟跨表查询

kaiyu

2021-09-23 d32bc8f5fa5cd1a9ef293614124420b17acdaa5f

screen-api
                  增加小时和五分钟跨表查询

 screen-api/src/main/java/com/moral/api/config/mybatis/MybatisIntercept.java

New file
@@ -0,0 +1,55 @@
package com.moral.api.config.mybatis;

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import java.sql.Connection;
import java.util.Properties;

import static javax.xml.bind.JAXBIntrospector.getValue;


/**
 * @ClassName MybatisIntercept
 * @Description TODO
 * @Author 陈凯裕
 * @Date 2021/9/17 10:57
 * @Version TODO
 **/
//@Component
/*@Intercepts({
        //type指定代理的是那个对象，method指定代理Executor中的那个方法,args指定Executor中的query方法都有哪些参数对象
        //由于Executor中有两个query，因此需要两个@Signature
        @Signature(type = Executor.class,method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class })//需要代理的对象和方法
})*/

@Intercepts({
        //type指定代理的是那个对象，method指定代理Executor中的那个方法,args指定Executor中的query方法都有哪些参数对象
        //由于Executor中有两个query，因此需要两个@Signature
        @Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class,Integer.class})
})
public class MybatisIntercept implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        System.out.println(11);
        return null;
    }

    @Override
    public Object plugin(Object target) {
        Object wrap = Plugin.wrap(target, this);
        return wrap;
    }

    @Override
    public void setProperties(Properties properties) {

    }

}

 screen-api/src/main/java/com/moral/api/config/mybatis/MybatisPlusConfig.java

@@ -1,10 +1,20 @@
package com.moral.api.config.mybatis;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.parsers.DynamicTableNameParser;
import com.baomidou.mybatisplus.extension.parsers.ITableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.moral.api.entity.HistoryFiveMinutely;
import com.moral.api.mapper.HistoryFiveMinutelyMapper;
import com.moral.api.service.HistorySecondUavService;
import com.moral.api.service.impl.HistorySecondUavServiceImpl;
import com.moral.api.service.impl.OrganizationServiceImpl;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@@ -15,29 +25,37 @@
@Configuration
public class MybatisPlusConfig {

    /**
    * @Description: 设置动态表名
            * @Param: []
            * @return: com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor
            * @Author: 陈凯裕
            * @Date: 2021/7/15
            */
    public static ThreadLocal<String> tableName = new ThreadLocal<>();

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        interceptor.addInnerInterceptor(new PaginationInnerInterceptorIIL(DbType.MYSQL));

        // 动态表名SQL解析器
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        HashMap<String, TableNameHandler> map = new HashMap<String, TableNameHandler>();
        map.put("history_five_minutely",(sql,tableName)->{ Date date = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
            String month = sdf.format(date);
            return tableName+"_"+month;});
        HashMap<String, TableNameHandler> map = new HashMap<>();
        map.put("history_hourly", (sql, tableName) -> tableName+this.tableName.get());
        map.put("history_five_minutely", (sql, tableName) -> tableName+this.tableName.get());

        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map);
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);

        return interceptor;
    }

    /*@Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //分页插件
        PaginationInnerInterceptorIIL paginationInnerInterceptorIIL = new PaginationInnerInterceptorIIL(DbType.MYSQL);
        paginationInnerInterceptorIIL.setMultiTableQueryName(multiTableQueryNames);
        interceptor.addInnerInterceptor(paginationInnerInterceptorIIL);

        return interceptor;
    }*/

}

 screen-api/src/main/java/com/moral/api/config/mybatis/PaginationInnerInterceptorIIL.java

New file
@@ -0,0 +1,103 @@
package com.moral.api.config.mybatis;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.ParameterUtils;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.DialectModel;
import com.baomidou.mybatisplus.extension.plugins.pagination.dialects.IDialect;
import lombok.SneakyThrows;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @ClassName
 * @Description TODO
 * @Author 陈凯裕
 * @Date 2021/9/18 10:13
 * @Version TODO
 **/
public class PaginationInnerInterceptorIIL extends PaginationInnerInterceptor {

    private List<String> multiTableQueryName;

    public void setMultiTableQueryName(List<String> multiTableQueryName) {
        this.multiTableQueryName = multiTableQueryName;
    }

    public PaginationInnerInterceptorIIL(DbType dbType) {
        super(dbType);
    }

    @SneakyThrows
    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);
        if (null == page) {
            return;
        }

        // 处理 orderBy 拼接
        boolean addOrdered = false;
        String buildSql = boundSql.getSql();
        List<OrderItem> orders = page.orders();
        if (!CollectionUtils.isEmpty(orders)) {
            addOrdered = true;
            buildSql = this.concatOrderBy(buildSql, orders);
        }


        // size 小于 0 不构造分页sql
        if (page.getSize() < 0) {
            if (addOrdered) {
                PluginUtils.mpBoundSql(boundSql).sql(buildSql);
            }
            return;
        }

        handlerLimit(page);
        IDialect dialect = findIDialect(executor);

        //判断表名是否需要分表查询
        String tableName = getTableName(buildSql);
        if(multiTableQueryName.contains(tableName)){
            System.out.println(111);
        }

        final Configuration configuration = ms.getConfiguration();
        DialectModel model = dialect.buildPaginationSql(buildSql, page.offset(), page.getSize());
        PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);

        List<ParameterMapping> mappings = mpBoundSql.parameterMappings();
        Map<String, Object> additionalParameter = mpBoundSql.additionalParameters();
        model.consumers(mappings, configuration, additionalParameter);
        mpBoundSql.sql(model.getDialectSql());
        mpBoundSql.parameterMappings(mappings);
    }

    private String getTableName(String buildSql) throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse(buildSql);
        PlainSelect selectBody = (PlainSelect) select.getSelectBody();
        Table table = (Table) selectBody.getFromItem();
        return  table.getName();
    }



}

 screen-api/src/main/java/com/moral/api/service/HistoryFiveMinutelyService.java

@@ -5,6 +5,7 @@
import com.moral.api.pojo.dto.historyFiveMinutely.DeviceAndFiveMinuteDataDTO;
import com.moral.api.pojo.form.historyFiveMinutely.QueryDeviceAndFiveMinuteDataForm;

import java.util.Date;
import java.util.List;
import java.util.Map;

@@ -38,4 +39,5 @@

    //获取5分钟风场数据
    List<Object> getAreaWindData(Map<String,Object> params);

}

 screen-api/src/main/java/com/moral/api/service/impl/HistoryFiveMinutelyServiceImpl.java

@@ -2,9 +2,14 @@

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.ISqlSegment;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.segments.MergeSegments;
import com.baomidou.mybatisplus.core.conditions.segments.NormalSegmentList;
import com.moral.api.config.mybatis.MybatisPlusConfig;
import com.moral.api.entity.Device;
import com.moral.api.entity.HistoryFiveMinutely;
import com.moral.api.entity.HistoryHourly;
import com.moral.api.entity.MonitorPoint;
import com.moral.api.mapper.HistoryFiveMinutelyMapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
@@ -15,7 +20,9 @@
import com.moral.api.service.HistoryFiveMinutelyService;
import com.moral.api.service.MonitorPointService;
import com.moral.constant.RedisConstants;
import com.moral.constant.SeparateTableType;
import com.moral.util.DateUtils;
import com.moral.util.MybatisPLUSUtils;
import io.lettuce.core.GeoCoordinates;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
@@ -287,6 +294,39 @@
        return list;
    }

    /**
     * @Description: 查询一段时间内某一mac的数据
     * @Param: [mac, startDate, endDate]
     * @return: java.util.List<com.moral.api.entity.HistoryHourly>
     * @Author: 陈凯裕
     * @Date: 2021/9/23
     */
    public List<HistoryFiveMinutely> getValueByMacAndTime(String mac, Date startDate, Date endDate){
        QueryWrapper<HistoryFiveMinutely> wrapper = new QueryWrapper<>();
        wrapper.eq("mac",mac);
        wrapper.between("time",startDate,endDate);
        List<String> tableNames = MybatisPLUSUtils.getTableNamesByWrapper(startDate, endDate, SeparateTableType.MONTH);
        List<HistoryFiveMinutely> datas = multiTableQuery(wrapper, tableNames);
        return datas;
    }

    /**
     * @Description: 多表查询，传入表名集合，以及条件wrapper，返回数据
     * @Param: [wrapper, tableNames]
     * @return: java.util.List<com.moral.api.entity.HistoryHourly>
     * @Author: 陈凯裕
     * @Date: 2021/9/23
     */
    private List<HistoryFiveMinutely> multiTableQuery(QueryWrapper<HistoryFiveMinutely> wrapper,List<String> tableNames){
        List<HistoryFiveMinutely> result = new ArrayList<>();
        for (String tableName : tableNames) {
            MybatisPlusConfig.tableName.set(tableName);
            List<HistoryFiveMinutely> datas = historyFiveMinutelyMapper.selectList(wrapper);
            result.addAll(datas);
        }
        return result;
    }





 screen-api/src/main/java/com/moral/api/service/impl/HistoryHourlyServiceImpl.java

@@ -1,19 +1,23 @@
package com.moral.api.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.moral.api.config.mybatis.MybatisPlusConfig;
import com.moral.api.entity.HistoryFiveMinutely;
import com.moral.api.entity.HistoryHourly;
import com.moral.api.mapper.HistoryHourlyMapper;
import com.moral.api.service.HistoryHourlyService;
import com.moral.constant.Constants;
import com.moral.constant.SeparateTableType;
import com.moral.util.AQIUtils;
import com.moral.util.DateUtils;

import com.moral.util.MybatisPLUSUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.*;

/**
 * <p>
@@ -48,4 +52,38 @@
        result.put("AQI", AQIUtils.hourlyAqi(data));
        return result;
    }

    /**
    * @Description: 查询一段时间内某一mac的数据
            * @Param: [mac, startDate, endDate]
            * @return: java.util.List<com.moral.api.entity.HistoryHourly>
            * @Author: 陈凯裕
            * @Date: 2021/9/23
            */
    public List<HistoryHourly> getValueByMacAndTime(String mac,Date startDate,Date endDate){
        QueryWrapper<HistoryHourly> wrapper = new QueryWrapper<>();
        wrapper.eq("mac",mac);
        wrapper.between("time",startDate,endDate);
        List<String> tableNames = MybatisPLUSUtils.getTableNamesByWrapper(startDate, endDate, SeparateTableType.MONTH);
        List<HistoryHourly> datas = multiTableQuery(wrapper, tableNames);
        return datas;
    }

    /**
    * @Description: 多表查询，传入表名集合，以及条件wrapper，返回数据
            * @Param: [wrapper, tableNames]
            * @return: java.util.List<com.moral.api.entity.HistoryHourly>
            * @Author: 陈凯裕
            * @Date: 2021/9/23
            */
    private List<HistoryHourly> multiTableQuery(QueryWrapper<HistoryHourly> wrapper,List<String> tableNames){
        List<HistoryHourly> result = new ArrayList<>();
        for (String tableName : tableNames) {
            MybatisPlusConfig.tableName.set(tableName);
            List<HistoryHourly> datas = historyHourlyMapper.selectList(wrapper);
            result.addAll(datas);
        }
        return result;
    }

}

 screen-api/src/main/java/com/moral/api/service/impl/HistorySecondUavServiceImpl.java

@@ -328,7 +328,6 @@
            String value = JSON.toJSONString(valueMap);
            data.setValue(value);
        }
        System.out.println(111);
    }

}

 screen-common/src/main/java/com/moral/constant/KafkaConstants.java

@@ -37,7 +37,6 @@
    * */
    public static final String CRUISER_TOPIC_HOUR = "cruiser_hour_data";


    /**
     * 存入数据库的消费组
     */

 screen-common/src/main/java/com/moral/constant/SeparateTableType.java

New file
@@ -0,0 +1,18 @@
package com.moral.constant;

public enum SeparateTableType {

    YEAR("yyyy"),
    MONTH("yyyy_MM"),
    DAY("yyyy_MM_dd");

    private final String format;

    SeparateTableType(String format) {
        this.format = format;
    }

    public String getFormat() {
        return format;
    }
}

 screen-common/src/main/java/com/moral/util/DateUtils.java

@@ -1284,6 +1284,170 @@
    }

    /**
     * @Description: 获取两个日期相差几个月
     * @Param: [start, end]
     * @return: int
     * @Author: 陈凯裕
     * @Date: 2021/9/23
     */
    public static int getMonth(Date start, Date end) {
        if (start.after(end)) {
            Date t = start;
            start = end;
            end = t;
        }
        Calendar startCalendar = Calendar.getInstance();
        startCalendar.setTime(start);
        Calendar endCalendar = Calendar.getInstance();
        endCalendar.setTime(end);
        Calendar temp = Calendar.getInstance();
        temp.setTime(end);
        temp.add(Calendar.DATE, 1);

        int year = endCalendar.get(Calendar.YEAR)
                - startCalendar.get(Calendar.YEAR);
        int month = endCalendar.get(Calendar.MONTH)
                - startCalendar.get(Calendar.MONTH);

        if ((startCalendar.get(Calendar.DATE) == 1)
                && (temp.get(Calendar.DATE) == 1)) {
            return year * 12 + month + 1;
        } else if ((startCalendar.get(Calendar.DATE) != 1)
                && (temp.get(Calendar.DATE) == 1)) {
            return year * 12 + month;
        } else if ((startCalendar.get(Calendar.DATE) == 1)
                && (temp.get(Calendar.DATE) != 1)) {
            return year * 12 + month;
        } else {
            return (year * 12 + month - 1) < 0 ? 0 : (year * 12 + month);
        }
    }

    /**
     * @Description: 获取两个日期之间所有的月份
     * @Param: [start, end]
     * @return: java.util.List<java.lang.String>
     * @Author: 陈凯裕
     * @Date: 2021/9/23
     */
    public static List<String> getAllMonth(Date start, Date end) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(start);
        // 获取开始年份和开始月份
        int startYear = calendar.get(Calendar.YEAR);
        int startMonth = calendar.get(Calendar.MONTH);
        // 获取结束年份和结束月份
        calendar.setTime(end);
        int endYear = calendar.get(Calendar.YEAR);
        int endMonth = calendar.get(Calendar.MONTH);
        //
        List<String> list = new ArrayList<String>();

        for (int i = startYear; i <= endYear; i++) {
            String date = "";
            if (startYear == endYear) {
                for (int j = startMonth; j <= endMonth; j++) {
                    if (j < 9) {
                        date = i + "-0" + (j + 1);
                    } else {
                        date = i + "-" + (j + 1);
                    }
                    list.add(date);
                }

            } else {
                if (i == startYear) {
                    for (int j = startMonth; j < 12; j++) {
                        if (j < 9) {
                            date = i + "-0" + (j + 1);
                        } else {
                            date = i + "-" + (j + 1);
                        }
                        list.add(date);
                    }
                } else if (i == endYear) {
                    for (int j = 0; j <= endMonth; j++) {
                        if (j < 9) {
                            date = i + "-0" + (j + 1);
                        } else {
                            date = i + "-" + (j + 1);
                        }
                        list.add(date);
                    }
                } else {
                    for (int j = 0; j < 12; j++) {
                        if (j < 9) {
                            date = i + "-0" + (j + 1);
                        } else {
                            date = i + "-" + (j + 1);
                        }
                        list.add(date);
                    }
                }

            }

        }

        return list;
    }

    /**
     * @Description: 获取两个日期之间所有的年份
     * @Param: [start, end]
     * @return: java.util.List<java.lang.String>
     * @Author: 陈凯裕
     * @Date: 2021/9/23
     */
    public static List<String> getAllYear(Date start, Date end) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(start);
        int startYear = calendar.get(Calendar.YEAR);
        calendar.setTime(end);
        int endYear = calendar.get(Calendar.YEAR);

        List<String> list = new ArrayList<>();
        list.add(String.valueOf(startYear));

        int i = endYear - startYear;

        for (int j = 1; j < i + 1; j++) {
            list.add(String.valueOf(startYear + j));
        }

        return list;
    }

    public static List<String> getAllDays(Date start, Date end) {
        List<String> list = new ArrayList<String>();
        SimpleDateFormat outformat = new SimpleDateFormat("yyyy-MM-dd");

        Calendar sCalendar = Calendar.getInstance();
        sCalendar.setTime(start);
        int year = sCalendar.get(Calendar.YEAR);
        int month = sCalendar.get(Calendar.MONTH);
        int day = sCalendar.get(Calendar.DATE);
        sCalendar.set(year, month, day, 0, 0, 0);

        Calendar eCalendar = Calendar.getInstance();
        eCalendar.setTime(end);
        year = eCalendar.get(Calendar.YEAR);
        month = eCalendar.get(Calendar.MONTH);
        day = eCalendar.get(Calendar.DATE);
        eCalendar.set(year, month, day, 0, 0, 0);

        while (sCalendar.before(eCalendar)) {
            list.add(outformat.format(sCalendar.getTime()));
            sCalendar.add(Calendar.DAY_OF_YEAR, 1);
        }
        list.add(outformat.format(eCalendar.getTime()));

        return list;

    }


    /**
     * 时间戳转 date
     *
     * @param longDate 时间戳

 screen-common/src/main/java/com/moral/util/MybatisPLUSUtils.java

New file
@@ -0,0 +1,58 @@
package com.moral.util;

import com.baomidou.mybatisplus.core.conditions.AbstractWrapper;
import com.baomidou.mybatisplus.core.conditions.ISqlSegment;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.segments.MergeSegments;
import com.baomidou.mybatisplus.core.conditions.segments.NormalSegmentList;
import com.moral.constant.SeparateTableType;
import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ClassName MybatisPLUSUtils
 * @Description TODO
 * @Author 陈凯裕
 * @Date 2021/9/23 10:45
 * @Version TODO
 **/
public class MybatisPLUSUtils {

    /**
     * @Description: 根据wrapper查询条件获取分表表名
     * @Param: [wrapper]
     * @return: java.util.List<java.lang.String>
     * @Author: 陈凯裕
     * @Date: 2021/9/23
     */
    public static List<String> getTableNamesByWrapper(Date startDate, Date endDate,SeparateTableType type) {

        List<String> tableNames = new ArrayList<>();

        if (type == SeparateTableType.MONTH) {
            List<String> months = DateUtils.getAllMonth(startDate, endDate);
            for (String month : months) {
                month = month.replaceAll("-", "");
                tableNames.add("_" + month);
            }
        } else if (type == SeparateTableType.YEAR) {
            List<String> years = DateUtils.getAllYear(startDate, endDate);
            for (String year : years) {
                tableNames.add("_" + year);
            }
        } else {
            List<String> days = DateUtils.getAllDays(startDate, endDate);
            for (String day : days) {
                day = day.replaceAll("-", "");
                tableNames.add("_"+day);
            }
        }
        return tableNames;
    }

}