导入依赖
<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