springboot使用influxdb

导入依赖

<dependency>
		<groupId>org.influxdb</groupId>
		<artifactId>influxdb-java</artifactId>
		<version>2.15</version>
</dependency>

配置文件

spring:
  influx:
    url: 
    userName: 
    password: 
    database: 
    retention_policy: autogen
    retention_policy_time: INF
    max_size: 100

数据库连接类

参数

@Configuration
@ConfigurationProperties(prefix = "spring.influx")
@Data
public class InfluxDBProperties {

    @URL
    private String url;
    @NotBlank
    private String userName;
    @NotBlank
    private String password;
    @NotBlank
    private String database;
    @NotBlank
    private String retentionPolicy;
    @NotBlank
    private String retentionPolicyTime;
    @NotBlank
    private Integer maxSize;
}

初始化连接

@Configuration
@Slf4j
public class InfluxDBConfiguration {

    @Autowired
    private InfluxDBProperties influxDBProperties;

    @Bean
    public InfluxDBConnect getInfluxDBConnect() {
        InfluxDBConnect influxDB = new InfluxDBConnect(influxDBProperties);

        influxDB.influxDbBuild();

        influxDB.createRetentionPolicy();
        log.info("init influxdb::[{}]", influxDBProperties);
        return influxDB;
    }
}

操作接口

@Data
@Slf4j
@Component
public class InfluxDBConnect implements InitializingBean {
    //连接池
    private static BlockingQueue<InfluxDB> connPool;

    /**
     * 用户名
     */
    private String userName;
    private String password;
    private String url;
    public String database;
    private Integer maxSize ;
    /**
     * 数据保存策略
     */
    private String retentionPolicy;
    /**
     * 数据保存策略中数据保存时间
     */
    private String retentionPolicyTime;

    /**
     * InfluxDB实例
     */
    private InfluxDB influxDB;
    public InfluxDBConnect(InfluxDBProperties influxDBProperties) {

        this.userName =influxDBProperties.getUserName();
        this.password = influxDBProperties.getPassword();
        this.url = influxDBProperties.getUrl();
        this.database = influxDBProperties.getDatabase();
        // autogen默认的数据保存策略
        this.retentionPolicy = influxDBProperties.getRetentionPolicy() == null || "".equals(influxDBProperties.getRetentionPolicy()) ? "autogen" : influxDBProperties.getRetentionPolicy();
        this.retentionPolicyTime = influxDBProperties.getRetentionPolicyTime() == null || "".equals(influxDBProperties.getRetentionPolicy()) ? "30d" : influxDBProperties.getRetentionPolicyTime();
        this.maxSize=influxDBProperties.getMaxSize();
        this.influxDB = influxDbBuild();
    }

    void initPool() {
        connPool = new LinkedBlockingQueue<>(maxSize);
        while (connPool.size()<maxSize) {
            if (influxDbBuild() != null) {
                try {
                    connPool.put(influxDbBuild());
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            } else {
                break;
            }
        }
    }
    public InfluxDB getInfluxDB() {
        if (connPool.size() > 0) {
            InfluxDB conn = null;
            try {
                conn = connPool.take();
                log.info("influxdb 从数据连接池里取");
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            if (conn == null) {
                initPool();
            }
            return conn;
        } else {
            log.info("influxdb 自己创建");

            InfluxDB influxDB = influxDbBuild();
            new Thread(()->{initPool();}).start();
            return influxDB;
        }
    }
    @Override
    public void afterPropertiesSet() throws Exception {
        initPool();
        influxDbBuild();
    }

    /**
     * 连接时序数据库；获得InfluxDB
     **/
    @SuppressWarnings("deprecation")
    public InfluxDB influxDbBuild() {
        try {
            if (influxDB == null) {
                if (url != null) {
                    influxDB = InfluxDBFactory.connect(url, userName, password);
                    if (influxDB != null) {
                        boolean flag = influxDB.databaseExists(database);
                        if (!flag) {
                            influxDB.createDatabase(database);
                            createRetentionPolicy(); //初始化创建保存策略
                        }
                        influxDB.setDatabase(database);
                    }
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return influxDB;
    }

    /**
     * 设置数据保存策略 defalut 策略名 /database 数据库名/ 30d 数据保存时限30天/ 1 副本个数为1/ 结尾DEFAULT
     * 表示 设为默认的策略
     */
    public void createRetentionPolicy() {
        String command = String.format("CREATE RETENTION POLICY \"%s\" ON \"%s\" DURATION %s REPLICATION %s DEFAULT",
                retentionPolicy, database, retentionPolicyTime, 1);
        this.query(command);
    }

    /**
     * 查询
     *
     * @param command 查询语句
     * @return
     */
    public QueryResult query(String command) {
        return getInfluxDB().query(new Query(command, database));

    }

    /**
     * 插入
     *
     * @param measurement 表
     * @param tags        标签
     * @param fields      字段
     */
    public void insert(String measurement, Map<String, String> tags, Map<String, Object> fields) {
        Point.Builder builder = Point.measurement(measurement);
        // 纳秒时会出现异常信息：partial write: points beyond retention policy dropped=1
        // builder.time(System.nanoTime(), TimeUnit.NANOSECONDS);
        builder.time(System.currentTimeMillis(), TimeUnit.MILLISECONDS);
        builder.tag(tags);
        builder.fields(fields);

        log.info("influxDB insert data:[{}]", builder.build().toString());
        getInfluxDB().write(database, "", builder.build());
    }

}

工具类

package born.influxdb;

import born.common.utils.Constant;
import born.common.utils.DateUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.influxdb.dto.QueryResult;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public class InfluxDBSqlUtils {

    private static final Integer defaultPage = 1;
    private static final Integer defaultPageSize = 10;

    /**
     * 通过页面查询参数拼接 influxDB 分页 limit * offset *
     *
     * @param params 页面查询参数
     * @return java.lang.StringBuffer
     * @author cq
     * @since 2020/9/22
     */
    public static StringBuffer getPage(Map<String, Object> params) {
        StringBuffer page = new StringBuffer();

        //分页参数
        Integer curPage = defaultPage;
        Integer limit = defaultPageSize;
        try {
            if (params.get(Constant.PAGE) != null) {
                curPage = Integer.parseInt((String) params.get(Constant.PAGE));
            }
            if (params.get(Constant.LIMIT) != null) {
                limit = Integer.parseInt((String) params.get(Constant.LIMIT));
            }
            if (curPage <= 0) {
                return page;
            } else {
                if (limit <= 0) {
                    limit = defaultPageSize;
                }
                page.append(" ").append("limit " + limit).append(" offset " + (limit * (curPage - 1)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return page;
    }

    /**
     * 拼接 in sql influxDB 不支持in 函数 用 or 拼接
     *
     * @param tagName 列名
     * @param args    数据 逗号隔开
     * @return java.lang.StringBuffer
     * @author cq
     * @since 2020/9/22
     */
    public static StringBuffer getINSql(String tagName, String args) {
        String regex = ",";
        StringBuffer inSQl = new StringBuffer();
        if (StringUtils.isNotEmpty(tagName) && StringUtils.isNotEmpty(args)) {
            String[] strings = args.split(regex);
            for (int i = 0; i < strings.length; i++) {
                //开始拼接 （ 不拼接 Or
                if (i == 0) {
                    inSQl.append(" (");
                    inSQl.append(" ");
                    inSQl.append(tagName);
                    inSQl.append(" = '");
                    inSQl.append(strings[i]);
                    inSQl.append("' ");
                } else {
                    inSQl.append(" OR ");
                    inSQl.append(tagName);
                    inSQl.append(" = '");
                    inSQl.append(strings[i]);
                    inSQl.append("' ");
                }
            }
            if (inSQl.length() > 0) {

            }
            inSQl.append(" )");
        }
        return inSQl;
    }

    /**
     * 格式化时间
     *
     * @param date 时间
     * @return java.lang.String
     * @author cq
     * @since 2020/9/22
     */
    public static String getTZTime(String date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date parse = sdf.parse(date);
            return getTZTime(parse);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return "";
    }

    public static String getTZTime(Date date) {
        String format = DateUtils.format(date, "yyyy-MM-dd'T'HH:mm:ss'Z'");
        return format;
    }

    public static String parseTZTime(String date){
        String STANDARD_DATE_FORMAT_UTC = "yyyy-MM-dd'T'HH:mm:ss";
        SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_DATE_FORMAT_UTC);
        sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
        SimpleDateFormat sdf2 = new SimpleDateFormat(DateUtils.DATE_TIME_PATTERN);
        try {

            return sdf2.format(sdf.parse(date));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     *  从influxDB 查询 数据 的结果转换成页面展示的样式
     *  直接通过list 存储  要求 数据一定是整齐的
     * @param queryResult 查询结果
     * @return  java.util.List<java.util.Map<java.lang.String,? extends java.lang.Object>>
     * @author  cq
     * @since  2020/9/22
     */
    public static List<Map<String, Object>> getSensorHis(QueryResult queryResult) {
        List< Map<String,  Object>> list =new ArrayList<>();
        if (queryResult.getResults() == null) {
            return null;
        } else {
            for (QueryResult.Result result : queryResult.getResults()) {
//
                List<QueryResult.Series> series = result.getSeries();
                String error = result.getError();
                if (StringUtils.isNotEmpty(error)) {
                    log.error("influxdb data ---" + error);
                }

                if (series == null) {
                    return null;

                } else {
                    //这里需要注意数据没有强规范 任何情况都可能发生。
                    try {
                        for (QueryResult.Series serie : series) {
                            Map<String, String> tags = serie.getTags();
                            List<List<Object>> values = serie.getValues();
                            String a= tags.get("a");
                            if(StringUtils.isEmpty(a)){
                                continue;
                            }
                            for(int i=0;i<values.size();i++){
                                String time = InfluxDBSqlUtils.parseTZTime(values.get(i).get(0).toString());
                                BigDecimal bigDecimal;
                                if(values.get(i).get(1)==null){
                                    bigDecimal = new BigDecimal(0);
                                }else{
                                    bigDecimal = new BigDecimal(values.get(i).get(1).toString());
                                }

//                                Map<String , Object> stringMap = list.get(i);
                                Map<String , Object> stringMap;
                                if(list.size()<i+1){
                                    stringMap=new HashMap<>();
                                    stringMap.put("dataTime",time);
                                    stringMap.put(a,bigDecimal.setScale(2,BigDecimal.ROUND_HALF_UP));
                                    list.add(stringMap);
                                }else{
                                    stringMap = list.get(i);
                                    stringMap.put(a,bigDecimal.setScale(2,BigDecimal.ROUND_HALF_UP));
                                }
                            }

                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        log.error("influx--数据 查询结果异常");
                    }

                }
            }
        }

        return list;
    }
}

业务代码

代码去敏感信息，慎用

@Resource
    InfluxDBConnect influxDBConnect;
    public PageUtils queryPageFromInflux(Map<String, Object> params) {
        //测点数量，用来计算count
        Integer number=1;
        Integer countNumber=null;
        //列名 表明 别名 不要添加单引号  可以使 双引号(\") 和 不加
        //tag 字符类型的要包 单引号   双引号情况未知
        //time 因为有时差 要 - 8h 时间条件为 time >= '2020-09-21T15:16:45Z' - 8h
        String tableName="ta";
        String id = (String) params.get("id");
        String beginDate = (String) params.get("beginDate");
        String endDate = (String) params.get("endDate");
//        InfluxDBSqlUtils.getTZTime();
        StringBuffer sql=new StringBuffer(" SELECT last(value) AS value FROM "+tableName+" ");
        List<String> whereItem=new ArrayList<>();
        //查询条件 存储到list 里 方便判断 where and 拼接
        if(StringUtils.isNotEmpty(beginDate)){
            String s="time>= '"+InfluxDBSqlUtils.getTZTime(beginDate)+"' - 8h ";
            whereItem.add(s);
        }
        if(StringUtils.isNotEmpty(endDate)){
            String s="time<= '"+InfluxDBSqlUtils.getTZTime(endDate)+"' - 8h ";
            whereItem.add(s);
        }
        if(StringUtils.isNotEmpty(sensorIds)){
            String s=InfluxDBSqlUtils.getINSql("id",sensorIds).toString();
            whereItem.add(s);
//            String regex = ",";
//            String[] strings = sensorIds.split(regex);
//            sensorNumber=strings.length;
        }
        for(int i=0;i<whereItem.size();i++){
            if(i==0){
                sql.append(" where ");
                sql.append(whereItem.get(i));
            }else{
                sql.append(" and ");
                sql.append(whereItem.get(i));
            }
        }
        sql.append(" GROUP BY time(1m),id order by time ");
        //不加分页时 的count 语句
        String countSql="select count(*) from ( "+sql.toString()+" )";
        //分页
        //pageUtils 赋值可能有问题  待确定
        sql.append(InfluxDBSqlUtils.getPage(params) );

        PageUtils pageUtils = new PageUtils(new Query<Map>().getPage(params));

        QueryResult query = influxDBConnect.query(countSql);
//        System.out.println(query.getResults().toString());
        if (query.getResults() == null) {
            return null;
        }else{
            for (QueryResult.Result result : query.getResults()) {
//
                List<QueryResult.Series> series = result.getSeries();
                String error = result.getError();
                if (StringUtils.isNotEmpty(error)) {
                    log.error("influxdb count---"+error);
                }
                if(series==null){
                    return null;

                }else {

                    for (QueryResult.Series serie : series) {
                        List<List<Object>> values = serie.getValues();
                        try{
                            countNumber = Double.valueOf(values.get(0).get(1).toString()).intValue();
                        }catch(Exception e){
                            e.printStackTrace();
                            pageUtils.setTotalCount(0);
                            return pageUtils;
                        }

                    }
                }
            }
        }
        String sql_s=("select * from ("+sql.toString()+") GROUP BY id");
        QueryResult query1 = influxDBConnect.query(sql_s);
        List<QueryResult.Result> results = query1.getResults();
        //因为influxdb特性 将count除series 数量 为数据条数 方便分页
        pageUtils.setTotalCount(0);
        if(results!=null){
            for (QueryResult.Result result : results) {
                List<QueryResult.Series> series = result.getSeries();
                if(series!=null&&series.size()>0){
                    sensorNumber=series.size();
                    pageUtils.setTotalCount(countNumber/sensorNumber);
                    if((countNumber/sensorNumber)%pageUtils.getPageSize()!=0){
                        pageUtils.setTotalPage((countNumber/sensorNumber)/pageUtils.getPageSize()+1);
                    }else{
                        pageUtils.setTotalPage((countNumber/sensorNumber)/pageUtils.getPageSize());
                    }
                }
            }
        }

        List<Map<String, Object>> sensorHis = InfluxDBSqlUtils.getSensorHis(query1);
//        System.out.println(query1.getResults().toString());
        pageUtils.setList(sensorHis);
        return pageUtils;
    }

测试类

//根据当前时间向前批量新增数据

@Test
    public void batchInsert(){
        Long cutime = System.currentTimeMillis() ; // 微秒

        Integer[] sIds=new Integer[]{ 1,2,3,4,5};

        int max=100;
        BatchPoints batchPoints = BatchPoints
                .database("database")
                .consistency(InfluxDB.ConsistencyLevel.ALL)
                .build();

        for(int i=0;i<max;i++){
            for (int j=0;j<sIds.length;j++){
                Point point = Point.measurement("ta")
                        //tag属性——只能存储String类型
                        .tag("id", sIds[j].toString())
                        .tag("status", "0")
                        //field存储数据
                        .addField("saveTime", DateUtils.format(new Date(),DateUtils.DATE_TIME_PATTERN))
                        .addField("value" ,Math.random()*100)
                        .time(cutime-60*1000*i, TimeUnit.MILLISECONDS)

                        .build();
                batchPoints.point(point);
            }
        }

        influxDBConnect.getInfluxDB().write(batchPoints);

    }

修改后的分页sql

/**
 *  分页
 *  countSql 查数组总数  即 time分组后的总数
 *  通过 timeSql 分组 查询最大和最小时间作为数据查询的条件来分组   最大时间要替换秒数
 *  selectSql 查询数据 不加分页条件
 */

-- countSql
select count(*) from (  SELECT last(value) AS value FROM his_sensorsminute  where time>= '2020-09-09T13:00:00Z' - 8h  and time<= '2020-10-09T13:57:00Z' - 8h  and  ( sensorId = '101000101'  OR sensorId = '101000201'  OR sensorId = '101000202'  OR sensorId = '101000203'  ) GROUP BY time(1m)  )
-- timeSql 
select * from (  SELECT last(value) AS value FROM his_sensorsminute  where time>= '2020-09-09T13:00:00Z' - 8h  and time<= '2020-10-09T13:57:00Z' - 8h  and  ( sensorId = '101000101'  OR sensorId = '101000201'  OR sensorId = '101000202'  OR sensorId = '101000203'  ) GROUP BY time(1m)  order by time  limit 17 offset 0 )
-- selectSql 
select * from ( SELECT last(value) AS value FROM his_sensorsminute  where time>= '2020-09-25T00:11:00Z'   and time<= '2020-09-25T00:27:59Z'   and  ( sensorId = '101000101'  OR sensorId = '101000201'  OR sensorId = '101000202'  OR sensorId = '101000203'  ) GROUP BY time(1m),sensorId order by time ) GROUP BY sensorId

参考

Influxdb基础指令
influxdb在java中的应用