【influxDB 2.x 分页查询示例】

最新推荐文章于 2024-08-20 09:52:53 发布

来一杯Java☕

最新推荐文章于 2024-08-20 09:52:53 发布

阅读量557

点赞数 5

文章标签：时序数据库 springboot java

本文链接：https://blog.csdn.net/weixin_45095396/article/details/140694624

版权

Java代码示例

** 下面代码包含使用influxdb进行分页查询，influxdb版本为2.1.1，代码亲测可用，且解决了手动分页时出现的total总数统计等问题。**


    public Page<MonitorData> pageFromInfluxdb(MonitorData monitorData, DateTime begin, DateTime end, Long pageNum, Long pageSize) {
        Page<MonitorData> page = new Page<>();

        List<MonitorData> monitorDataList = Lists.newArrayList();
        String[] query = influxDBUtil.buildQuery4page(begin, end, monitorData, pageSize, pageNum);
        TimeInterval timer = new TimeInterval();
        List<FluxTable> table = influxDBUtil.query(query[0]);
        log.info("******************** influxDBUtil.query(query[0])执行完成，耗时{} ********************", DateUtil.formatBetween(timer.interval()));
        timer.restart();
        for (FluxTable fluxTable : table) {
            List<FluxRecord> fluxRecords = fluxTable.getRecords();
            for (FluxRecord record : fluxRecords) {
                Map<String, Object> values = record.getValues();
                MonitorData monitorData1 = new MonitorData();
                monitorData1.setMonitorDeviceCode(MapUtil.getStr(values, "monitor_device_code"));
                monitorData1.setAlarmType(MapUtil.getStr(values, "alarmType"));
                monitorData1.setAlarmLevel(MapUtil.getStr(values, "alarmLevel"));
                monitorData1.setCheckState(MapUtil.getInt(values, "checkState"));
                monitorData1.setValue(MapUtil.getStr(values, "_value"));
                monitorData1.setTime(DateUtil.toLocalDateTime(MapUtil.getDate(values, "_time")));
                monitorDataList.add(monitorData1);
            }
        }
        monitorDataList.sort(Comparator.comparing(MonitorData::getTime).reversed());
        monitorDataList = monitorDataList.subList(0, Math.min(monitorDataList.size(), pageSize.intValue()));
        log.info("******************** 遍历数据执行完成，耗时{} ********************", DateUtil.formatBetween(timer.interval()));
        List<FluxTable> count = influxDBUtil.query(query[1]);
        long total = 0;

        for (FluxTable fluxTable : count) {
            for (FluxRecord record : fluxTable.getRecords()) {
                long total1 = Long.parseLong(Objects.requireNonNull(record.getValue()).toString());
                if (total1 >= total) {
                    total = total1;
                }
            }
        }
        page.setRecords(monitorDataList);
        page.setTotal(total);
        return page;

    }





   public String[] buildQuery4page(DateTime begin, DateTime end, MonitorData monitorData, Long pageSize, Long pageNum) {
        String query = buildQueryStr(begin, end, monitorData);
        String count = query + "|> group()" + " |> count()";
        query += " |> sort(columns:[\"_time\"], desc:true)";
        query += " |> group()";
        query += " |> limit(n: " + pageSize + ", offset: " + (--pageNum * pageSize) + ")";
        return new String[]{query, count};
    }

    private String buildQueryStr(DateTime begin, DateTime end, MonitorData monitorData) {
        DateTime beginFormatDate = DateUtil.offsetHour(begin, -8);
        DateTime endFormatDate = DateUtil.offsetHour(end, -8);
        String query = "from(bucket: \"" + bueckt + "\") |> range(start: " + DateUtil.format(beginFormatDate, DatePattern.UTC_PATTERN)
                + ", stop: " + DateUtil.format(DateUtil.offsetSecond(endFormatDate, 1), DatePattern.UTC_PATTERN) +
                ") |> filter(fn: (r) => r[\"_measurement\"] == \"" + MEASUREMENT + "\"" + ")";
        String filter = "";
        String monitorDeviceCode = monitorData.getMonitorDeviceCode();
        if (StringUtils.isNotBlank(monitorDeviceCode)) {
            List<String> collect = Arrays.stream(monitorDeviceCode.split(",")).collect(Collectors.toList());
            String filter1 = " |> filter(fn: (r) => " + collect.stream().map(u -> " r[\"monitor_device_code\"] == \"" + u + "\"").collect(Collectors.joining("or", "", ")"));
            filter += filter1;
        }
        if (StringUtils.isNotBlank(monitorData.getAlarmType())) {
            String filter2 = " |> filter(fn: (r) => r[\"alarmType\"] == \"" + monitorData.getAlarmType() + "\")";
            filter += filter2;
        }
        if (StringUtils.isNotBlank(monitorData.getAlarmLevel())) {
            String filter3 = " |> filter(fn: (r) => r[\"alarmLevel\"] == \"" + monitorData.getAlarmLevel() + "\")";
            filter += filter3;
        }
        if (ObjectUtil.isNotNull(monitorData.getCheckState())) {
            String filter4 = " |> filter(fn: (r) => r[\"checkState\"] == \"" + monitorData.getCheckState() + "\")";
            filter += filter4;
        }
        String filterValue = "|> filter(fn: (r) => r[\"_field\"] == \"value\")";
        filter += filterValue;
        query += filter;
        return query;
    }

from(bucket: "station") |> range(start: 2024-07-23T16:00:00Z, stop: 2024-07-25T16:00:00Z) 
    |> filter(fn: (r) => r["_measurement"] == "monitor_data")
    |> filter(fn: (r) => r["_field"] == "value")
    |> sort(columns: ["_time"], desc: true)
    |> group()
    |> limit(n: 13, offset: 0)

|> group() 分组函数很重要，
如果只使用|> limit(n: 13, offset: 0) 来进行分页，查出的结果会有不同的分组
导致后面使用|>count()来统计条数时也会有多条结果，从而分页总数统计出问题。

在这里插入图片描述

from(bucket: "station")
    |> range(start: 2024-07-23T16:00:00Z, stop: 2024-07-25T16:00:00Z)
    |> filter(fn: (r) => r["_measurement"] == "monitor_data")
    |> filter(fn: (r) => r["_field"] == "value")
    |> group()
    |> count()