亲测可用——IoTDB部署及操作指南
最近用到IoTDB数据库,经过对一些相关文档的搜集,大概了解到了该数据库的主要应用场景和使用方法,本篇就讲一下如何利用IoTDB并结合SpringBoott和Mybatis进行项目整合。经过一番查找全网都没有一篇完整的关于该数据库采用Mybatis做持久化框架的文章,那么就由我来开辟一下先河。
概述:
IoTDB数据库官网:http://iotdb.apache.org/
Apache IoTDB(物联网数据库)是一个物联网原生数据库,具有高性能的数据管理和分析能力,可部署在边缘和云端。Apache IoTDB以其轻量级的架构、高性能和丰富的特性集以及与Apache Hadoop、Spark和Flink的深度集成,可以满足物联网海量数据存储、高速数据摄取和复杂数据分析的需求工业领域。
同时该数据库只支持以下几种数据类型 BOOLEAN、INT32 (int)、INT64 (long)、FLOAT、DOUBLE 、TEXT (String) 具体的使用说明可以参照官网上地址,写的很全:http://iotdb.apache.org/UserGuide/Master/QuickStart/QuickStart.html 实战:
SpringBoot项目相关配置 首先创建一个SpringBoot项目 这里就不过多赘述了,然后pom.xml引入iotdb依赖。
<dependency>
<groupId>org.apache.iotdb</groupId>
<artifactId>iotdb-jdbc</artifactId>
<version>0.12.1</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.9</version>
</dependency>
application.yml文件配置
server:
port: 8080
#iotdb
spring:
datasource:
username: root
password: root
driver-class-name: org.apache.iotdb.jdbc.IoTDBDriver
url: jdbc:iotdb://192.168.80.200:6667/
initial-size: 5
min-idle: 10
max-active: 50
max-wait: 60000
remove-abandoned: true
remove-abandoned-timeout: 30
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
test-while-idle: false
test-on-borrow: false
test-on-return: false
#mybatis
mybatis:
mapper-locations: classpath*:/mappers/*.xml
这是iotdb数据库的jdbc操作,很多文章中都是采用手写工具类去操作,都没有结合Mybatis,这可能和官网提出的不建议使用该方式有关。我参考了一下别人的JDBC工具类并重构了一下:
package com.iotdb.zjc.demo.util;
import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Author: zjc
* @ClassName IotDBConfig
* @Description TODO IoTDB-JDBC操作工具
* @date 2021/11/24 14:31
* @Version 1.0
*/
@Component
@Configuration
public class IotDbConfig {
private static final Logger log = LoggerFactory.getLogger(IotDbConfig.class);
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driver-class-name}")
private String driverName;
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.initial-size}")
private int initialSize;
@Value("${spring.datasource.min-idle}")
private int minIdle;
@Value("${spring.datasource.max-active}")
private int maxActive;
@Value("${spring.datasource.max-wait}")
private int maxWait;
@Value("${spring.datasource.remove-abandoned}")
private boolean removeAbandoned;
@Value("${spring.datasource.remove-abandoned-timeout}")
private int removeAbandonedTimeout;
@Value("${spring.datasource.time-between-eviction-runs-millis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.min-evictable-idle-time-millis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.test-while-idle}")
private boolean testWhileIdle;
@Value("${spring.datasource.test-on-borrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.test-on-return}")
private boolean testOnReturn;
private static DruidDataSource iotDbDataSource;
/**
* 使用阿里的druid连接池
*
* @return
*/
public Connection getConnection() {
if (iotDbDataSource == null) {
iotDbDataSource = new DruidDataSource();
//设置连接参数
iotDbDataSource.setUrl(url);
iotDbDataSource.setDriverClassName(driverName);
iotDbDataSource.setUsername(username);
iotDbDataSource.setPassword(password);
//配置初始化大小、最小、最大
iotDbDataSource.setInitialSize(initialSize);
iotDbDataSource.setMinIdle(minIdle);
iotDbDataSource.setMaxActive(maxActive);
//配置获取连接等待超时的时间
iotDbDataSource.setMaxWait(maxWait);
//连接泄漏监测
iotDbDataSource.setRemoveAbandoned(removeAbandoned);
iotDbDataSource.setRemoveAbandonedTimeout(removeAbandonedTimeout);
//配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
//关注公众号【可为编程】回复【面试】领取2023年最新面试题!!!
iotDbDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
iotDbDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
//防止过期
iotDbDataSource.setTestWhileIdle(testWhileIdle);
iotDbDataSource.setTestOnBorrow(testOnBorrow);
iotDbDataSource.setTestOnReturn(testOnReturn);
}
Connection connection = null;
try {
connection = iotDbDataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
log.error("iotDB getConnection失败: error={}", e.getMessage());
}
return connection;
}
/**
* 执行单条数据操作
*
* @param sql
*/
public boolean execute(String sql) {
log.info("iotDB execute sql={}", sql);
Connection connection = getConnection();
PreparedStatement preparedStatement = null;
boolean flag = false;
try {
if (connection != null) {
preparedStatement = connection.prepareStatement(sql);
long systemTime = System.currentTimeMillis();
flag = preparedStatement.execute();
log.info("执行IotDb的sql----[{}],时间:[{}]ms", sql, System.currentTimeMillis() - systemTime);
}
} catch (SQLException e) {
log.error("iotDB insert失败: error={}", e.getMessage());
} finally {
close(preparedStatement, connection);
}
return flag;
}
/**
* 执行批量数据操作
*
* @param sqls
* @return
*/
public Integer executeBatch(List<String> sqls) {
Connection connection = getConnection();
PreparedStatement preparedStatement = null;
int[] flag = null;
try {
if (connection != null) {
for (String sql : sqls) {
log.info("iotDB executeBatch sql={}", sql);
preparedStatement = connection.prepareStatement(sql);
long systemTime = System.currentTimeMillis();
preparedStatement.addBatch(sql);
log.info("执行IotDb的sql----[{}],时间:[{}]ms", sql, System.currentTimeMillis() - systemTime);
}
flag = preparedStatement.executeBatch();
}
} catch (SQLException e) {
log.error("iotDB 执行失败: error={}", e.getMessage());
} finally {
close(preparedStatement, connection);
}
return flag.length;
}
/**
* 查询操作
*
* @param sql
* @return
*/
public List<Map<String, Object>> executeQuery(String sql) {
log.info("iotDB executeQuery sql={}", sql);
Connection connection = getConnection();
PreparedStatement preparedStatement = null;
List<Map<String, Object>> resultList = null;
ResultSet resultSet = null;
try {
if (connection != null) {
preparedStatement = connection.prepareStatement(sql);
long systemTime = System.currentTimeMillis();
resultSet = preparedStatement.executeQuery();
log.info("查询IotDb的sql----[{}],时间:[{}]ms", sql, System.currentTimeMillis() - systemTime);
resultList = outputResult(resultSet);
}
} catch (SQLException e) {
e.printStackTrace();
log.error("iotDB query失败: error={}", e.getMessage());
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
log.error("iotDB resultSet关闭异常: error={}", e.getMessage());
}
close(preparedStatement, connection);
}
return resultList;
}
/**
* 关注公众号【可为编程】回复【面试】领取2023年最新面试题!!!
* 输出结果集
*
* @param resultSet
* @return
* @throws SQLException
*/
private List<Map<String, Object>> outputResult(ResultSet resultSet) throws SQLException {
List<Map<String, Object>> resultList = new ArrayList<>();
if (resultSet != null) {
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
Map resultMap = new HashMap<>(16);
for (int i = 1; i <= columnCount; i++) {
String colunmName = metaData.getColumnLabel(i);
if (colunmName.indexOf('.') >= 0) {
colunmName = colunmName.substring(colunmName.lastIndexOf('.') + 1);
}
//过滤 函数()括号
if (colunmName.indexOf(')') >= 0) {
colunmName = colunmName.substring(0, colunmName.lastIndexOf(')'));
}
//时序库自带的时间格式转换
if (colunmName.equals("Time")) {
Long timeStamp = Long.parseLong(resultSet.getString(i));
if (timeStamp > 0) {
Date d = new Date(timeStamp);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
resultMap.put("Time", sf.format(d));
}
} else {
resultMap.put(colunmName, resultSet.getString(i));
}
}
resultList.add(resultMap);
}
}
return resultList;
}
/**
* 关闭连接
*
* @param statement
* @param connection
*/
private void close(Statement statement, Connection connection) {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (Exception e) {
log.error("iotDB close失败: error={}", e.getMessage());
e.printStackTrace();
}
}
}
创建controller、service、mapper文件 controller:
package com.iotdb.zjc.demo.controller;
import com.iotdb.zjc.demo.service.OrderService;
import com.iotdb.zjc.demo.constant.MyResponse;
import com.iotdb.zjc.demo.data.OrderInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.Date;
import java.util.List;
/**
* @Author: zjc
* @ClassName OrderController
* @Description TODO
* @date 2021/11/23 17:16
* @Version 1.0
*/
@RestController
public class OrderController {
private static final Logger log = LoggerFactory.getLogger(OrderController.class);
@Autowired
OrderService orderService;
@RequestMapping(value = "/createOrder", method = RequestMethod.POST)
// @Transactional(rollbackFor = Exception.class)
public MyResponse createOrder(@RequestBody OrderInfo orderInfo) {
try {
orderInfo.setTimestamp(System.currentTimeMillis());
orderInfo.setCreateTime(new Date());
Integer v = orderService.createOrder(orderInfo);
if (v == -1) {
return MyResponse.ok(v);
} else {
return MyResponse.checkForbidden(false);
}
} catch (Exception e) {
log.info("创建订单失败!" + e);
return new MyResponse(false);
}
}
/**
* 更新操作 其实也是插入操作 时间戳相同 只和时间戳相关
* @param orderInfo
* @return
*/
@RequestMapping(value = "/updateOrder", method = RequestMethod.POST)
public MyResponse updateOrder(@RequestBody OrderInfo orderInfo) {
try {
orderInfo.setTimestamp(System.currentTimeMillis());
orderInfo.setCreateTime(new Date());
Integer v = orderService.updateOrder(orderInfo);
if (v == -1) {
return MyResponse.ok(v);
} else {
return MyResponse.checkForbidden(false);
}
} catch (Exception e) {
log.info("更新订单失败!" + e);
return new MyResponse(false);
}
}
/**
* 删除操作 要将时间戳的加号变成%2B
* @param timestamp
* @return
*/
@RequestMapping(value = "/deleteOrder", method = RequestMethod.GET)
public MyResponse deleteOrder(String timestamp) {
try {
Integer v = orderService.deleteOrder(timestamp);
if (v == -1) {
return MyResponse.ok(v);
} else {
return MyResponse.checkForbidden(false);
}
} catch (Exception e) {
log.info("删除订单失败!" + e);
return new MyResponse(false);
}
}
/**
* 创建组 也就是相当于数据库
* @return
*/
@RequestMapping(value = "/createOrderGroup", method = RequestMethod.POST)
public MyResponse createOrderGroup() {
try {
Integer v = orderService.createOrderGroup();
if (v > 0) {
return new MyResponse(v);
} else {
return new MyResponse(false);
}
} catch (Exception e) {
log.info("创建订单组失败!" + e);
return new MyResponse(false);
}
}
/**
* 查询所有订单数据
* @return
*/
@RequestMapping(value = "/queryOrder", method = RequestMethod.GET)
public MyResponse queryOrder() {
try {
List<OrderInfo> v = orderService.queryOrder();
if (v.size() > 0) {
v.forEach(x -> {
System.out.println(x.toString());
});
return MyResponse.ok(v);
} else {
return new MyResponse(false);
}
} catch (Exception e) {
log.info("查询订单组失败!" + e);
return new MyResponse(false);
}
}
/**
* 查看数据库有多少组
*
* @return
*/
@RequestMapping(value = "/selectStorageGroup", method = RequestMethod.GET)
public MyResponse selectStorageGroup() {
try {
List<String> v = orderService.selectStorageGroup();
if (v.size() > 0) {
v.forEach(x -> {
System.out.println("group------------------" + x.toString());
});
return MyResponse.ok(v);
} else {
return new MyResponse(false);
}
} catch (Exception e) {
log.info("查询组失败!" + e);
return new MyResponse(false);
}
}
}
serviceImpl文件
package com.iotdb.zjc.demo.serviceimpl;
import com.iotdb.zjc.demo.service.OrderService;
import com.iotdb.zjc.demo.data.OrderInfo;
import com.iotdb.zjc.demo.mapper.OrderMapper;
import com.iotdb.zjc.demo.util.IotDbConfig;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @Author: zjc
* @ClassName OrderServiceImpl
* @Description TODO
* @date 2021/11/23 17:32
* @Version 1.0
*/
@Service
public class OrderServiceImpl implements OrderService {
@Autowired
IotDbConfig iotDbConfig;
@Autowired
OrderMapper orderMapper;
@Override
public Integer createOrder(OrderInfo orderInfo) {
return orderMapper.createOrder(orderInfo);
}
@Override
public Integer updateOrder(OrderInfo orderInfo) {
return orderMapper.updateOrder(orderInfo);
}
@Override
public Integer createOrderGroup() {
try {
// List<String> statements = new ArrayList<>();
// statements.add("set storage group to root.order");
// statements.add("CREATE TIMESERIES root.order.orderdetail.order_id WITH DATATYPE=INT64, ENCODING=PLAIN, COMPRESSOR=SNAPPY");
// statements.add("CREATE TIMESERIES root.order.orderdetail.order_num WITH DATATYPE=INT32, ENCODING=PLAIN, COMPRESSOR=SNAPPY");
// statements.add("CREATE TIMESERIES root.order.orderdetail.order_name WITH DATATYPE=TEXT, ENCODING=PLAIN, COMPRESSOR=SNAPPY");
// statements.add("CREATE TIMESERIES root.order.orderdetail.create_time WITH DATATYPE=TEXT, ENCODING=PLAIN, COMPRESSOR=SNAPPY");
// Integer flag = iotDbConfig.executeBatch(statements);
Integer flag = orderMapper.createOrderGroup();
Integer flagEle = orderMapper.createOrderGroupElement();
System.out.println("\n\t执行sql数量为{}" + flagEle);
return flagEle;
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
@Override
public List<OrderInfo> queryOrder() {
return orderMapper.queryOrder();
}
@Override
public List<String> selectStorageGroup() {
return orderMapper.selectStorageGroup();
}
@Override
public Integer deleteOrder(String timestamp) {
return orderMapper.deleteOrder(timestamp);
}
}
mapper文件
package com.iotdb.zjc.demo.mapper;
import com.iotdb.zjc.demo.data.OrderInfo;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @Author: zjc
* @ClassName OrderMapper
* @Description TODO
* @date 2021/11/24 10:09
* @Version 1.0
*/
public interface OrderMapper {
Integer createOrder(OrderInfo orderInfo);
List<OrderInfo> queryOrder();
Integer createOrderGroup();
Integer createOrderGroupElement();
List<String> selectStorageGroup();
Integer updateOrder(OrderInfo orderInfo);
Integer deleteOrder(@Param("timestamp") String timestamp);
}
mapper.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.iotdb.zjc.demo.mapper.OrderMapper">
<resultMap id="BaseResultMap" type="com.iotdb.zjc.demo.data.OrderInfo">
<result column="root.order2.orderdetail.order_id" property="orderId"/>
<result column="root.order2.orderdetail.order_num" property="orderNum"/>
<result column="root.order2.orderdetail.order_name" property="orderName"/>
<result column="root.order2.orderdetail.create_time" property="createTime"/>
</resultMap>
<insert id="createOrder" parameterType="com.iotdb.zjc.demo.data.OrderInfo">
insert into root.order2.orderdetail(timestamp, order_id, order_num, order_name,create_time) values(#{timestamp},#{orderId},#{orderNum},#{orderName},#{createTime});
</insert>
<select id="queryOrder" resultMap="BaseResultMap">
select * from root.order2.orderdetail limit 3 offset 0
</select>
<select id="selectStorageGroup" resultType="java.lang.String">
show storage group limit 3 offset 0
</select>
<delete id="deleteOrder" parameterType="java.lang.String">
delete from root.order2.orderdetail where timestamp = ${timestamp};
</delete>
<insert id="updateOrder">
insert into root.order2.orderdetail(timestamp, order_id, order_num, order_name,create_time) values(2021-11-24T18:28:20.689+08:00,#{orderId},#{orderNum},#{orderName},#{createTime});
</insert>
<update id="createOrderGroup">
SET STORAGE GROUP TO root.order2
</update>
<update id="createOrderGroupElement">
-- CREATE TIMESERIES root.order2.orderdetail.create_time WITH DATATYPE=TEXT, ENCODING=PLAIN, COMPRESSOR=SNAPPY;
CREATE TIMESERIES root.order2.orderdetail.order_num WITH DATATYPE=INT32, ENCODING=PLAIN, COMPRESSOR=SNAPPY;
</update>
</mapper>
注意几点:
1、一定要注意映射关系要写成这样root.order2.orderdetail.order_id 组名的形式,不然映射不到对应实体的字段。 2、添加时要增加时间戳,时间戳就是主键,删除也是删除对应的时间戳。3、修改操作就是新增操作,只是对应的时间戳不变,其他的都可以变。 4、创建组和创建时间序列,当我们创建一个时间序列时,我们应该定义它的数据类型和编码方案
IoTDB常用的SQL语句大全 操作相关
//显示版本
show version
//新建存储组
SET STORAGE GROUP TO root.test
//显示已经创建的存储组
SHOW STORAGE GROUP
//删除存储组
delete storage group root.test
//创建时间序列
create timeseries root.test.wf01.wt01.s0(elec_meter) with datatype=FLOAT, encoding=RLE, compression=SNAPPY,MAX_POINT_NUMBER=3 tags(tag1=t1, tag2=t2) attributes(attr1=a1, attr2=a2)
//删除时间序列
delete timeseries root.test.wf01.wt01.s0
delete timeseries root.test.wf01.wt01.*
//更改时间序列
alter timeseries root.test.wf01.wt01.s0 DROP tag1, tag2
alter timeseries root.test.wf01.wt01.s0 SET tag1=t3, attr1=a3
alter timeseries root.test.wf01.wt01.s0 ADD TAGS tag3=t3, tag4=t4
//显示存储组下时间序列
show timeseries root.test
//模糊查询设备下的序列
show timeseries root.test.*.wt01
//加条件的查询时间序列
show timeseries root.test where tag1=t1
//统计时间序列
count timeseries root.test.*.wt01
//根据层级分组,统计出每组下的时间序列个数
count timeseries root.test group by level=3
//统计存储组对应的层级下有几个节点
count nodes root.test level=3
//统计存储组下几个设备
show devices root.test
//显示某一层下的子路径
show child paths root.test.wf01
CRUD
//插入记录
insert into root.test.wf01.wt02(timestamp,elec_meter) values (1,23.896)
//更新记录
insert同时间戳覆盖即可
//删除记录
delete from root.test.wf01.wt02.s0,root.test.wf01.wt02.s1 where time < now()
//查询记录
select temperature from root.test.wf01.wt01 where time >0 and time <5
select count(s0) from root.test.wf01.wt02 WHERE root.test.wf01.wt02.s0 < 25
select max_value(s0) from root.test.wf01.wt02 where time < now() - 5h
select min_time(s0) from root.test.wf01.wt02
//排序 0.11
select * from root.test.wf01.wt01 where time > 1 order by time desc limit 10;
//分组应用
//统计这段时间区间内(前包,后不包)每隔5分钟统计s0个数
select count(s0) from root.test.wf01.wt02 group by ([1,259222222),5m)
//统计这段时间内每隔5小时,前2个小时有数据个数
select count(s0) from root.test.wf01.wt02 group by ([86400000,259222222),2h,5h)
//分页
//时间戳升序,首条下标为0,取3条
select * from root.test.wf01.wt02 limit 3 offset 0
//筛选最后一条记录
select last temperature from root.test.wf01.wt02
界面清洁神句 align by device
COUNT函数返回由SELECT语句选择的时间序列(一个或多个)非空值的值数
FIRST_VALUE函数返回所选时间序列的第一个点值
LAST_VALUE函数返回所选时间序列的最后一个点值
MAX_TIME函数返回所选时间序列(一个或多个)的最大时间戳
MAX_VALUE函数返回所选时间序列(一个或多个)的最大值
AVG函数返回指定时间段内所选时间序列的算术平均值
MIN_TIME函数返回所选时间序列的最小时间戳
MIN_VALUE函数返回所选时间序列(一个或多个)的最小值
NOW函数返回当前时间戳
SUM函数返回指定时间段内所选时间序列的总和
IoTDB安装操作请查看如下链接: 亲测可用——IoTDB部署及操作指南