1.声明
当前内容主要为使用java的jdbc方式操作Apache IoTDB这个时序数据库
安装参考这个博文：Linux（CentOS7）中安装和测试IoTDB
首先需要开放端口，Apache IoTDB端口为6667
2.基本的pom依赖
<dependency>
<groupId>org.apache.iotdb</groupId>
<artifactId>iotdb-jdbc</artifactId>
<version>0.11.1</version>
</dependency>
3.基本的连接测试
连接工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @description 当前主要为一个连接控制的工具类
* @author hy
* @createTime 2021-03-05 16:45:51
*/
public class IOTdbJDBCUtils {
private static final String driver = "org.apache.iotdb.jdbc.IoTDBDriver";
private final String url;
private final String username;
private final String password;
public IOTdbJDBCUtils(String url, String username, String password) {
this.url = url;
this.username = username;
this.password = password;
}
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("当前加载的驱动不存在........,请检查后重试!");
}
}
public Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
测试连接类：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.hy.springboot.iotdb.utils.IOTdbJDBCUtils;
/**
* @description 当前内容主要是测试使用jdbc方式连接iotdb这个时序数据库
* @author hy
* @createTime 2021-03-05 16:32:12
*/
public class IOTDBConnectionTest {
private static final String url = "jdbc:iotdb://192.168.1.101:6667/";
private static final String username = "root";
private static final String password = "root";
public static void main(String[] args) {
Connection conn = new IOTdbJDBCUtils(url, username, password).getConnection();
System.out.println(conn != null ? "打开连接成功!" : "打开连接失败!");
IOTdbJDBCUtils.close(conn);
}
}
执行结果：
4.基于前面的命令行的sql执行操作
Machine实体类
import java.util.Date;
public class Machine {
private String name;
private Float temperature;
private Boolean status;
private Date timestamp;
public Date getTimestamp() {
return timestamp;
}
public void setTimestamp(Date timestamp) {
this.timestamp = timestamp;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Float getTemperature() {
return temperature;
}
public void setTemperature(Float temperature) {
this.temperature = temperature;
}
public Boolean getStatus() {
return status;
}
@Override
public String toString() {
return "Machine [name=" + name + ", temperature=" + temperature + ", status=" + status + "]";
}
public void setStatus(Boolean status) {
this.status = status;
}
public Machine() {
super();
// TODO Auto-generated constructor stub
}
public Machine(String name, Float temperature, Boolean status) {
super();
this.name = name;
this.temperature = temperature;
this.status = status;
this.timestamp = new Date();
}
}
测试类：
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import com.hy.springboot.iotdb.utils.IOTdbJDBCUtils;
/**
* @author hy
* @createTime 2021-03-06 10:21:31
* @description 当前内容主要用于测试向当前的IOTDB执行sql,默认采用jdbc方式执行
*
*/
public class IOTDBExecuteSQlTest {
private static final String host = "192.168.1.101";
private static final String url = "jdbc:iotdb://" + host + ":6667/";
private static final String username = "root";
private static final String password = "root";
public static void main(String[] args) throws InterruptedException {
Connection conn = new IOTdbJDBCUtils(url, username, password).getConnection();
System.out.println(conn != null ? "打开连接成功!" : "打开连接失败!");
try {
// 开始执行sql
// 1.创建一个个人的组：root.test
ResultSet rs = null;
String storageGroup = "root.test";
Statement statement = conn.createStatement();
String sql = String.format("set storage group to %s", storageGroup);
statement = conn.createStatement();
int createGroup = statement.executeUpdate(sql);
System.out.println("当前创建组的结果为：" + createGroup);
// 2.查询创建的组,判断是否存在
// 2.1 SHOW STORAGE GROUP // 查看所有的存储组
sql = "SHOW STORAGE GROUP";
rs = statement.executeQuery(sql);
outputResult(rs);
// 2.2 show storage group root.test // 显示这个存储组
sql = String.format("show storage group %s", storageGroup);
rs = statement.executeQuery(sql);
outputResult(rs);
// 2.3 count storage group root.test,统计存在的数量
sql = String.format("count storage group %s", storageGroup);
rs = statement.executeQuery(sql);
outputResult(rs);
// 3.创建时序字段(机器1，名称,温度,状态)
// 创建时序machine1并设置名称字段为文本类型
// create timeseries root.test.machine1.name with datatype=TEXT,encoding=PLAIN
String machineStr = "machine";
sql = "create timeseries " + storageGroup + "." + machineStr + ".name with datatype=TEXT,encoding=PLAIN";
int createSeriesField = statement.executeUpdate(sql);
System.out.println("当前创建时序字段name的结果为：" + createSeriesField);
// 创建时序machine1并设置温度字段为浮点类型
// create timeseries root.test.machine1.temperature with
// datatype=FLOAT,encoding=RLE
sql = "create timeseries " + storageGroup + "." + machineStr
+ ".temperature with datatype=FLOAT,encoding=RLE";
createSeriesField = statement.executeUpdate(sql);
System.out.println("当前创建时序字段temperature的结果为：" + createSeriesField);
// 创建时序machine1并设置状态字段为布尔类型
// create timeseries root.test.machine1.status with
// datatype=BOOLEAN,encoding=PLAIN
sql = "create timeseries " + storageGroup + "." + machineStr
+ ".status with datatype=BOOLEAN,encoding=PLAIN";
createSeriesField = statement.executeUpdate(sql);
System.out.println("当前创建时序字段status的结果为：" + createSeriesField);
// 4.查询当前root.test.machine1的所有时序字段show timeseries root.test.machine1
System.out.println("查询当前root.test.machine1的所有时序字段");
System.out.println("========================>");
sql = String.format("show timeseries %s.%s", storageGroup, machineStr);
rs = statement.executeQuery(sql);
outputResult(rs);
System.out.println("<========================");
// 5.开始添加数据,这里需要休眠，否则数据都是一起的
Machine machine1_1 = new Machine("机器1", 400.5f, true);
Thread.sleep(100);
Machine machine1_2 = new Machine("机器1", 380.5f, true);
Thread.sleep(100);
Machine machine1_3 = new Machine("机器1", 420.5f, true);
Thread.sleep(100);
Machine machine1_4 = new Machine("机器1", 390.5f, true);
DateFormat format = new SimpleDateFormat("yyyyMMddHHmmssSSS");
// insert into root.test.machine()
sql = "insert into root.test.machine(timestamp,name,temperature,status)" + " values("
+ format.format(machine1_1.getTimestamp()) + ",'" + machine1_1.getName() + "',"
+ machine1_1.getTemperature() + "," + machine1_1.getStatus() + ")";
int insert = statement.executeUpdate(sql);
System.out.println("添加数据结果：" + insert);
sql = "insert into root.test.machine(timestamp,name,temperature,status)" + " values("
+ format.format(machine1_2.getTimestamp()) + ",'" + machine1_2.getName() + "',"
+ machine1_2.getTemperature() + "," + machine1_2.getStatus() + ")";
insert = statement.executeUpdate(sql);
System.out.println("添加数据结果：" + insert);
sql = "insert into root.test.machine(timestamp,name,temperature,status)" + " values("
+ format.format(machine1_3.getTimestamp()) + ",'" + machine1_3.getName() + "',"
+ machine1_3.getTemperature() + "," + machine1_3.getStatus() + ")";
insert = statement.executeUpdate(sql);
System.out.println("添加数据结果：" + insert);
sql = "insert into root.test.machine(timestamp,name,temperature,status)" + " values("
+ format.format(machine1_4.getTimestamp()) + ",'" + machine1_4.getName() + "',"
+ machine1_4.getTemperature() + "," + machine1_4.getStatus() + ")";
insert = statement.executeUpdate(sql);
System.out.println("添加数据结果：" + insert);
// 6. 查询添加的数据
// 6.1 全部查询
sql = String.format("select * from %s.%s", storageGroup, machineStr);
rs = statement.executeQuery(sql);
outputResult(rs);
// 6.2 限制查询(就是限制查询，只查询2条数据)
sql = String.format("select * from %s.%s limit 2", storageGroup, machineStr);
rs = statement.executeQuery(sql);
outputResult(rs);
// 6.3 查询第1到第3条数据
sql = String.format("select * from %s.%s limit 3 offset 1", storageGroup, machineStr);
// 其中offset就是起始位置，limit就是实际取的条数
rs = statement.executeQuery(sql);
outputResult(rs);
// 6.4 查询当前machine的数据条数
sql=String.format("select count(*) from %s.%s",storageGroup, machineStr);
// 或者sql=String.format("select count(status) from %s.%s",storageGroup, machineStr);
rs = statement.executeQuery(sql);
outputResult(rs);
// 7. 删除数据
sql = String.format("delete from %s.%s where time = %s", storageGroup, machineStr,
format.format(machine1_2.getTimestamp())); // 删除时间搓为300的数据
int delete = statement.executeUpdate(sql);
System.out.println("当前删除的条数：" + delete);
System.out.println("删除后的数据=>");
sql = String.format("select * from %s.%s", storageGroup, machineStr);
rs = statement.executeQuery(sql);
outputResult(rs);
// 8. flush保存数据到磁盘
statement.execute(String.format("flush %s", storageGroup));
// 9. 删除machine这个时序
// delete timeseries machine
sql = String.format("delete timeseries %s.%s", storageGroup, machineStr);
int deleteTimeSeries = statement.executeUpdate(sql);
System.out.println("删除时序的个数为：" + deleteTimeSeries);
// 10. 删除root.test这个组
sql = String.format("delete storage group %s", storageGroup);
int deleteGroup = statement.executeUpdate(sql);
System.out.println("删除组的个数为：" + deleteGroup);
// 关闭rs
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
IOTdbJDBCUtils.close(conn);
}
private static void outputResult(ResultSet resultSet) throws SQLException {
if (resultSet != null) {
System.out.println("--------------------------");
final ResultSetMetaData metaData = resultSet.getMetaData();
final int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
System.out.print(metaData.getColumnLabel(i + 1) + ", ");
}
System.out.println();
while (resultSet.next()) {
for (int i = 1;; i++) {
System.out.print(resultSet.getString(i));
if (i < columnCount) {
System.out.print(", ");
} else {
System.out.println();
break;
}
}
}
System.out.println("--------------------------\n");
}
}
}
执行的结果：
打开连接成功!
当前创建组的结果为：0
--------------------------
storage group,
root.test
--------------------------
--------------------------
storage group,
root.test
--------------------------
--------------------------
count,
1
--------------------------
当前创建时序字段name的结果为：0
当前创建时序字段temperature的结果为：0
当前创建时序字段status的结果为：0
查询当前root.test.machine1的所有时序字段
========================>
--------------------------
timeseries, alias, storage group, dataType, encoding, compression, tags, attributes,
root.test.machine.name, null, root.test, TEXT, PLAIN, SNAPPY, null, null
root.test.machine.temperature, null, root.test, FLOAT, RLE, SNAPPY, null, null
root.test.machine.status, null, root.test, BOOLEAN, PLAIN, SNAPPY, null, null
--------------------------
<========================
添加数据结果：0
添加数据结果：0
添加数据结果：0
添加数据结果：0
--------------------------
Time, root.test.machine.name, root.test.machine.temperature, root.test.machine.status,
20210306102240201, 机器1, 400.5, true
20210306102240306, 机器1, 380.5, true
20210306102240413, 机器1, 420.5, true
20210306102240520, 机器1, 390.5, true
--------------------------
--------------------------
Time, root.test.machine.name, root.test.machine.temperature, root.test.machine.status,
20210306102240201, 机器1, 400.5, true
20210306102240306, 机器1, 380.5, true
--------------------------
--------------------------
Time, root.test.machine.name, root.test.machine.temperature, root.test.machine.status,
20210306102240306, 机器1, 380.5, true
20210306102240413, 机器1, 420.5, true
20210306102240520, 机器1, 390.5, true
--------------------------
--------------------------
count(root.test.machine.name), count(root.test.machine.temperature), count(root.test.machine.status),
4, 4, 4
--------------------------
当前删除的条数：0
删除后的数据=>
--------------------------
Time, root.test.machine.name, root.test.machine.temperature, root.test.machine.status,
20210306102240201, 机器1, 400.5, true
20210306102240413, 机器1, 420.5, true
20210306102240520, 机器1, 390.5, true
--------------------------
删除时序的个数为：0
删除组的个数为：0
这里需要小心的地方
timestamp不可以相同，否则只能视为一条数据，所以上面使用了Thread.sleepvalues后面只能接一条数据，不可像sql一样，否则报错
5.总结
1.基本使用步骤和当前的cli没有任何区别，与cli执行完全一致，需要注意一些细节问题
2337
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
