- 本文主要描述了使用JDBC连接IoTDB完成CPU数据的实时采集,并提供了REST API查询和Grafana可视化查看的过程
- 项目代码地址:https://github.com/stormbroken/Spring-Boot-IoTDB
- 操作系统:Windows 10
- JDK版本:1.8
- Spring Boot:2.5.2
- IoTDB 版本:0.12.1,启动端口6667
- Grafana 版本:8.0.4,启动端口8081
- /test/health:检查应用心跳
- /test/jdbcCreateTest:创建测试用存储组
- /test/jdbcInsertTest:创建测试插入数据
- /test/jdbcQueryTest:创建测试查询接口
- /test/jdbcDeleteTest:删除测试用存储组
- 上述接口在测试过程中可以按照字面顺序执行查看返回结果和报错。
- /system/init:初始化创建系统采样用存储组
- /system/findAll:查询所有的采样结果数据
- /system/delete:删除系统采样用存储组
- 核心配置文件为pom.xml文件,请进入Github项目查看具体配置。
- 其中需要注意iotdb-jdbc不要使用0.13版本,会报错失败,本项目使用的为0.12.1版本。
<dependency>
<groupId>org.apache.iotdb</groupId>
<artifactId>iotdb-jdbc</artifactId>
<version>0.12.1</version>
</dependency>
@Data
public class MySystemInfo {
// 内存使用率
Double memoryUsage;
// CPU使用率
Double cpuUsage;
// 磁盘使用率
Double diskUsage;
// 采样时间 ms
Long time;
}
- 该服务主要负责将Spring应用通过JDBC与IoTDB进行连接
- 遇到的问题
- Query的结果集输出报错SQLException(400):请检查是否在输出之前将Statement或Connection关闭
- 请在使用完成ResultSet后将其关闭,避免过多未关闭连接降低查询性能。
/**
* @Author stormbroken
* Create by 2021/07/07
* @Version 1.0
**/
@Service
public class IoTDBService {
@Value("${iotdb.url}")
String url;
@Value("${iotdb.username}")
String username;
@Value("${iotdb.password}")
String password;
/**
* 建立连接
*/
Connection connection;
public void getConnection() throws ClassNotFoundException, SQLException {
if(connection == null){
synchronized (this){
if(connection == null){
Class.forName("org.apache.iotdb.jdbc.IoTDBDriver");
connection = DriverManager.getConnection(url, username, password);
}
}
}
}
/**
* 新建Statement后执行语句
* @param statementStr
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public boolean executeStatement(String statementStr)
throws ClassNotFoundException, SQLException{
Statement statement = getStatement();
return statement.execute(statementStr);
}
/**
* 新建Statement后执行一批语句
* @param statements
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public void executeStatements(List<String> statements)
throws ClassNotFoundException, SQLException{
Statement statement = getStatement();
for(String statementStr: statements){
statement.addBatch(statementStr);
}
statement.executeBatch();
}
/**
* 按照查询语句进行查询
* @param query
* @param fetchSize
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public ResultSet executeQuery(String query, Integer fetchSize)
throws ClassNotFoundException, SQLException{
Statement statement = getStatement();
statement.setFetchSize(fetchSize);
return statement.executeQuery(query);
}
/**
* 插入一条系统数据
* @param statement
* @param mySystemInfo
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public boolean insertSystemInfo(String statement, MySystemInfo mySystemInfo)
throws ClassNotFoundException, SQLException{
PreparedStatement preparedStatement = getPreparedStatement(statement);
preparedStatement.setLong(1, mySystemInfo.getTime());
preparedStatement.setDouble(2, mySystemInfo.getMemoryUsage());
preparedStatement.setDouble(3, mySystemInfo.getCpuUsage());
preparedStatement.setDouble(4, mySystemInfo.getDiskUsage());
return preparedStatement.execute();
}
/**
* 输出结果集
* @param resultSet
* @throws SQLException
*/
public String outputResult(ResultSet resultSet) throws SQLException {
StringBuilder stringBuilder = new StringBuilder();
if (resultSet != null) {
stringBuilder.append("--------------------------");
final ResultSetMetaData metaData = resultSet.getMetaData();
final int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
stringBuilder.append(metaData.getColumnLabel(i + 1));
stringBuilder.append(" ");
}
stringBuilder.append("\n");
while (resultSet. next()) {
StringBuilder builder = new StringBuilder();
for (int i = 1; ; i++) {
builder.append(resultSet.getString(i));
if (i < columnCount) {
builder.append(", ");
} else {
builder.append("\n");
break;
}
}
stringBuilder.append(builder);
}
stringBuilder.append("--------------------------\n\n");
}
return stringBuilder.toString();
}
/**
* 获取 Statement 进行执行
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
private Statement getStatement()
throws ClassNotFoundException, SQLException{
getConnection();
return connection.createStatement();
}
/**
* 获取 PreparedStatement 进行执行
* @param statement
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
private PreparedStatement getPreparedStatement(String statement)
throws ClassNotFoundException, SQLException{
getConnection();
return connection.prepareStatement(statement);
}
}
- 本部分使用了定时任务,1秒钟采样一次。
- 采样结束后进行写库。
@Scheduled(cron = "* * * * * *")
public void updateInfo() throws ClassNotFoundException, SQLException {
MySystemInfo mySystemInfo = new MySystemInfo();
mySystemInfo.setCpuUsage(getCpuUsage());
mySystemInfo.setDiskUsage(getDiskUsage());
mySystemInfo.setMemoryUsage(getMemoryUsage());
mySystemInfo.setTime(System.currentTimeMillis());
systemService.insert(mySystemInfo);
}