如红框为influxdb使用jar包。
![](https://i-blog.csdnimg.cn/blog_migrate/98ed03fa18720455eb8cf07f2707a472.png)
——influxdb工具类——
package com.sqlServerToInfluxdb.jdbc;
import java.util.Map;
import org.influxdb.InfluxDB;
import org.influxdb.InfluxDBFactory;
import org.influxdb.dto.Point;
import org.influxdb.dto.Point.Builder;
import org.influxdb.dto.Pong;
import org.influxdb.dto.Query;
import org.influxdb.dto.QueryResult;
import org.influxdb.InfluxDBFactory;
import org.influxdb.dto.Point;
import org.influxdb.dto.Point.Builder;
import org.influxdb.dto.Pong;
import org.influxdb.dto.Query;
import org.influxdb.dto.QueryResult;
/**
* 时序数据库 influxdb 连接
*
* */
public class influxdbJDBC {
private String username = "admin";//用户名
private String password = "admin";//密码
private String openurl = "http://127.0.0.1:8086";//连接地址
private String database = "energy_db";//数据库
//private String measurement = "sys_code";//表
private InfluxDB influxDB;
public influxdbJDBC() {
influxDbBuild();
createRetentionPolicy();
}
/**连接时序数据库,获取influxDB**/
public InfluxDB influxDbBuild() {
influxDB = InfluxDBFactory.connect(openurl, username, password);
Pong pong = influxDB.ping();
if(pong != null){
System.out.println("pong:" + pong + ",连接成功!");
}else{
System.out.println("连接失败");
return null;
}
influxDB.createDatabase(database);
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", "defalut",database,"30d",1);
this.query(command);
}
/*
* 查询
* @param command 查询语句
* @return
* */
public QueryResult query(String command) {
Query query = new Query(command, database);
QueryResult a = influxDB.query(query);
return a;
}
/*
* 插入
* @param measurment 表
* @param tags 标签
* @param fields 字段
* */
public void insert(String measurement,Map<String, String> tags, Map<String, Object> fields) {
Builder builder = Point.measurement(measurement);//源码讲表名赋值到类中
builder.tag(tags);//源码对标签进行验证,不得有空
builder.fields(fields);//源码将数据值赋值到类中
influxDB.write(database, "", builder.build());//向数据库写数据,builder.build():验证表名不得为空,field数据长度大于0。
}
/*
* 删除
* @param command 删除语句
* @return 返回错误信息
*/
public String deleteMeasurementData(String command) {
QueryResult result = influxDB.query(new Query(command, database));
return result.getError();
}
/*
* 创建数据库
* @param dbName
*/
public void creatDB(String dbName) {
influxDB.createDatabase(dbName);
}
/*
* 删除数据库
* @param dbName
*/
public void deleteDB(String dbName) {
influxDB.deleteDatabase(dbName);
}
}
* 时序数据库 influxdb 连接
*
* */
public class influxdbJDBC {
private String username = "admin";//用户名
private String password = "admin";//密码
private String openurl = "http://127.0.0.1:8086";//连接地址
private String database = "energy_db";//数据库
//private String measurement = "sys_code";//表
private InfluxDB influxDB;
public influxdbJDBC() {
influxDbBuild();
createRetentionPolicy();
}
/**连接时序数据库,获取influxDB**/
public InfluxDB influxDbBuild() {
influxDB = InfluxDBFactory.connect(openurl, username, password);
Pong pong = influxDB.ping();
if(pong != null){
System.out.println("pong:" + pong + ",连接成功!");
}else{
System.out.println("连接失败");
return null;
}
influxDB.createDatabase(database);
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", "defalut",database,"30d",1);
this.query(command);
}
/*
* 查询
* @param command 查询语句
* @return
* */
public QueryResult query(String command) {
Query query = new Query(command, database);
QueryResult a = influxDB.query(query);
return a;
}
/*
* 插入
* @param measurment 表
* @param tags 标签
* @param fields 字段
* */
public void insert(String measurement,Map<String, String> tags, Map<String, Object> fields) {
Builder builder = Point.measurement(measurement);//源码讲表名赋值到类中
builder.tag(tags);//源码对标签进行验证,不得有空
builder.fields(fields);//源码将数据值赋值到类中
influxDB.write(database, "", builder.build());//向数据库写数据,builder.build():验证表名不得为空,field数据长度大于0。
}
/*
* 删除
* @param command 删除语句
* @return 返回错误信息
*/
public String deleteMeasurementData(String command) {
QueryResult result = influxDB.query(new Query(command, database));
return result.getError();
}
/*
* 创建数据库
* @param dbName
*/
public void creatDB(String dbName) {
influxDB.createDatabase(dbName);
}
/*
* 删除数据库
* @param dbName
*/
public void deleteDB(String dbName) {
influxDB.deleteDatabase(dbName);
}
}
————influxdb批量插入数据————自己数据为从sqlserver中获取,可自行定义————
public static void main(String[] args) throws Exception {
//sqlserver获取数据
sqlServerJDBC sqlJDBC = new sqlServerJDBC();
String sql = "select top(100) ATime,TagID,nValue from Tag20171202 ORDER BY TagID,ATime";
List<Map<String, String>> sqlserverlist = sqlJDBC.query(sql);
System.out.println("开始时间:" + new Date().getTime());
//创建influxdb连接对象
InfluxDB client = InfluxDBFactory.connect("http://localhost:8086","admin","admin");
int i = 0;//控制循环,批量存储,每10万条数据存储一次
//声明influxdb批量插入数据对象
BatchPoints batchPoints = BatchPoints
.database("energy_db")
.consistency(InfluxDB.ConsistencyLevel.ALL)
.build();
//遍历sqlserver获取数据
for(Map<String, String> map : sqlserverlist){
//创建单条数据对象——表名
Point point = Point.measurement("influxdb")
//tag属性——只能存储String类型
.tag("TAG_TAGID", map.get("tagid"))
.tag("TAG_ATIME", map.get("atime").substring(0,11))
//field存储数据
.addField("FIELDS_NVALUE", map.get("nvalue"))
.addField("FIELDS_ATIME", String.valueOf(i))
.build();
//将单条数据存储到集合中
batchPoints.point(point);
i++;
//每读取十万条数据提交到influxdb存储一次
if(i / 100000 == 1){
i = 0;
client.write(batchPoints);
batchPoints = BatchPoints
.database("energy_db")
.consistency(InfluxDB.ConsistencyLevel.ALL)
.build();
}
}
client.write(batchPoints);
System.out.println("结束时间:" + new Date().getTime() + ",i:");
}
//sqlserver获取数据
sqlServerJDBC sqlJDBC = new sqlServerJDBC();
String sql = "select top(100) ATime,TagID,nValue from Tag20171202 ORDER BY TagID,ATime";
List<Map<String, String>> sqlserverlist = sqlJDBC.query(sql);
System.out.println("开始时间:" + new Date().getTime());
//创建influxdb连接对象
InfluxDB client = InfluxDBFactory.connect("http://localhost:8086","admin","admin");
int i = 0;//控制循环,批量存储,每10万条数据存储一次
//声明influxdb批量插入数据对象
BatchPoints batchPoints = BatchPoints
.database("energy_db")
.consistency(InfluxDB.ConsistencyLevel.ALL)
.build();
//遍历sqlserver获取数据
for(Map<String, String> map : sqlserverlist){
//创建单条数据对象——表名
Point point = Point.measurement("influxdb")
//tag属性——只能存储String类型
.tag("TAG_TAGID", map.get("tagid"))
.tag("TAG_ATIME", map.get("atime").substring(0,11))
//field存储数据
.addField("FIELDS_NVALUE", map.get("nvalue"))
.addField("FIELDS_ATIME", String.valueOf(i))
.build();
//将单条数据存储到集合中
batchPoints.point(point);
i++;
//每读取十万条数据提交到influxdb存储一次
if(i / 100000 == 1){
i = 0;
client.write(batchPoints);
batchPoints = BatchPoints
.database("energy_db")
.consistency(InfluxDB.ConsistencyLevel.ALL)
.build();
}
}
client.write(batchPoints);
System.out.println("结束时间:" + new Date().getTime() + ",i:");
}
————influxdb查询————
public static void main(String[] args) {
influxdbJDBC infJDBC = new influxdbJDBC();
String sql = "select * from influxdb";
QueryResult results = infJDBC.query(sql);
if(results.getResults() == null){
System.out.println("数据为空!");
return;
}
List<dataInfo> lists = new ArrayList<dataInfo>();
for(Result result : results.getResults()){
List<Series> series = result.getSeries();
for(Series serie : series){
List<List<Object>> values = serie.getValues();//字段字集合
List<String> colums = serie.getColumns();//字段名
System.out.println("colums:" + colums);
for(List<Object> n : values){
System.out.println("value:" + n);
}
//lists.addAll(getQueryData(colums,values));
}
System.out.println("数据长度:" + series.size());
}
}
influxdbJDBC infJDBC = new influxdbJDBC();
String sql = "select * from influxdb";
QueryResult results = infJDBC.query(sql);
if(results.getResults() == null){
System.out.println("数据为空!");
return;
}
List<dataInfo> lists = new ArrayList<dataInfo>();
for(Result result : results.getResults()){
List<Series> series = result.getSeries();
for(Series serie : series){
List<List<Object>> values = serie.getValues();//字段字集合
List<String> colums = serie.getColumns();//字段名
System.out.println("colums:" + colums);
for(List<Object> n : values){
System.out.println("value:" + n);
}
//lists.addAll(getQueryData(colums,values));
}
System.out.println("数据长度:" + series.size());
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/85d6fa0f358800aae9eb1cd2315ae808.png)