package com.xinan.dataservice.util;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import okhttp3.OkHttpClient;
import org.apache.commons.lang3.StringUtils;
import org.influxdb.InfluxDB;
import org.influxdb.InfluxDBFactory;
import org.influxdb.dto.Query;
import org.influxdb.dto.QueryResult;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.util.*;
import java.util.concurrent.TimeUnit;
@Data
@Slf4j
public class InfluxDbUtils {
private String userName;
private String password;
private String url;
public String database;
private String retentionPolicy;
/**
* InfluxDB实例
*/
private InfluxDB influxDb;
static OkHttpClient.Builder client = new OkHttpClient.Builder()
.readTimeout(100, TimeUnit.SECONDS);
public InfluxDbUtils(String userName, String password, String url, String database,
String retentionPolicy) {
this.userName = userName;
this.password = password;
this.url = url;
this.database = database;
this.retentionPolicy = retentionPolicy == null || "".equals(retentionPolicy) ? "autogen" : retentionPolicy;
this.influxDb = influxDbBuild();
}
/**
* 连接数据库
*
* @return influxDb实例
*/
private InfluxDB influxDbBuild() {
if (influxDb == null) {
influxDb = InfluxDBFactory.connect(url, userName, password, client);
}
influxDb.setDatabase(database);
influxDb.setLogLevel(InfluxDB.LogLevel.BASIC);
return influxDb;
}
public InfluxDB getInfluxDb(){
return this.influxDb;
}
//单条查询语句封装
/**
*
* @param influxDb 数据库实例
* @param database 数据库名字
* @param commond 单条sql语句
* @return 查询结果
*/
public static List<Map> getResult(InfluxDB influxDb, String database, String commond){
List<Map> retList = new ArrayList<>();
QueryResult queryResult = influxDb.query(new Query(commond,database));
List<QueryResult.Result> results = queryResult.getResults();
if (results==null||results.isEmpty()){
return retList;
}
QueryResult.Result result = results.get(0);
List<QueryResult.Series> seriess = result.getSeries();
if (seriess==null||seriess.isEmpty()){
return retList;
}
QueryResult.Series series = seriess.get(0);
List<String> columns = series.getColumns();
List<List<Object>> values = series.getValues();
for (List<Object> columnValue :values){
Map map = new HashMap(1);
for (int i=0;i<columnValue.size();i++){
map.put(columns.get(i),columnValue.get(i));
}
retList.add(map);
}
return retList;
}
public static List<Map> getResult(InfluxDB influxDb, String database, String commond, String queryColumnName){
if (StringUtils.isBlank(queryColumnName)) {
return getResult(influxDb, database, commond);
}
List<Map> retList = new ArrayList<>();
QueryResult queryResult = influxDb.query(new Query(commond,database));
List<QueryResult.Result> results = queryResult.getResults();
if (results==null||results.isEmpty()){
return retList;
}
QueryResult.Result result = results.get(0);
List<QueryResult.Series> seriess = result.getSeries();
if (seriess==null||seriess.isEmpty()){
return retList;
}
QueryResult.Series series = seriess.get(0);
List<String> columns = series.getColumns();
List<List<Object>> values = series.getValues();
String[] queryColumns = queryColumnName.split(",");
for (List<Object> columnValue :values){
Map map = new HashMap(1);
for (String queryColumn :queryColumns){
int index = columns.indexOf(queryColumn);
map.put(queryColumn,columnValue.get(index));
}
retList.add(map);
}
return retList;
}
//单条单列查询语句封装
/**
*
* @param influxDb 数据库实例
* @param database 数据库名字
* @param commond 单条sql语句
* @param columnName 列名
* @return 查询结果
*/
public static List<String> getResultOneColumn(InfluxDB influxDb, String database, String commond,String columnName){
List<String> retList = new ArrayList<>();
QueryResult queryResult = influxDb.query(new Query(commond,database));
List<QueryResult.Result> results = queryResult.getResults();
if (results==null||results.isEmpty()){
return retList;
}
QueryResult.Result result = results.get(0);
List<QueryResult.Series> seriess = result.getSeries();
if (seriess==null||seriess.isEmpty()){
return retList;
}
QueryResult.Series series = seriess.get(0);
List<String> columns = series.getColumns();
List<List<Object>> values = series.getValues();
//columnName
int i=-1;
for (int k=0;k<columns.size();k++){
if (columns.get(k).equals(columnName)){
i=k;
break;
}
}
if (i==-1){
try {
throw new Exception("没有找到对应列:"+columnName);
} catch (Exception e) {
log.error("没有找到对应列:"+e.getMessage());
}
}
for (List<Object> columnValue :values){
if (columnValue.get(i)==null){
retList.add("");
}else {
retList.add(String.valueOf(columnValue.get(i)));
}
}
return retList;
}
public static Set<String> getList(InfluxDB influxDb, String database, String commond, String columnName){
Set<String> retList = new HashSet<>();
QueryResult queryResult = influxDb.query(new Query(commond,database));
List<QueryResult.Result> results = queryResult.getResults();
if (results==null||results.isEmpty()){
return retList;
}
QueryResult.Result result = results.get(0);
List<QueryResult.Series> seriess = result.getSeries();
if (seriess==null||seriess.isEmpty()){
return retList;
}
QueryResult.Series series = seriess.get(0);
List<String> columns = series.getColumns();
List<List<Object>> values = series.getValues();
//columnName
int i=-1;
for (int k=0;k<columns.size();k++){
if (columns.get(k).equals(columnName)){
i=k;
break;
}
}
if (i==-1){
try {
throw new Exception("没有找到对应列:"+columnName);
} catch (Exception e) {
log.error("没有找到对应列:"+e.getMessage());
}
}
for (List<Object> columnValue :values){
if (columnValue.get(i)==null){
retList.add("");
}else {
retList.add(String.valueOf(columnValue.get(i)));
}
}
return retList;
}
}
备注:参数
(InfluxDB influxDb = influxDbUtils.getInfluxDb();同事influx数据库,时间为纳秒格式,注意转换,同时不支持时间格式转换函数,并且再springboot sql操作,不会报明显错误)