说明：项目需使用时序数据库，后经调研选用了IoTDB数据库，因此需要在SpringBoot框架内集成该数据库，来进行数据查询、插入操作。根据官方文档Java可采用多种方式访问数据库，分别是Java原生接口、JDBC(不推荐)和REST API，下面选用Java原生接口和MyBatis的方式操作数据库。

实际应用中需要高性能吞吐则使用Java原生接口的方式，但一些小量级的增删查可以使用MyBatis的方式。

前提：已部署安装IoTDB数据库，具体安装部署流程可查看官网文档。

2、IotDB-JDBC-MyBatis
2.1、pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>springboot-iotdb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <parent>
        <artifactId>spring-boot-starter-parent</artifactId>
        <groupId>org.springframework.boot</groupId>
        <version>2.1.5.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.28</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- LOG4J -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.16</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--mybatis-plus依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.iotdb/iotdb-jdbc -->
        <dependency>
            <groupId>org.apache.iotdb</groupId>
            <artifactId>iotdb-jdbc</artifactId>
            <version>1.3.1</version>
        </dependency>
    </dependencies>

</project>
2.2、application.yml
spring:
  mybatis-plus:
    configuration:
      #在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射
      map-underscore-to-camel-case: true
      mapper-locations: classpath*:mapper/*.xml
  datasource:
    username: root
    password: root
    driver-class-name: org.apache.iotdb.jdbc.IoTDBDriver
    url: jdbc:iotdb://100.100.100.100:6667/

server:
  port: 8080
2.3、IotDbMybatisService
package com.example.service;

import com.example.dto.InsertEntityDTO;
import com.example.entity.DataEntity;

import java.util.List;

public interface IotDbMybatisService {
    Integer selectCount(String deviceId);

    Integer insertEntity(InsertEntityDTO dataEntity);

    List<DataEntity> pageResultData(String deviceId, Float temperature, String hardware, Boolean status, Integer page, Integer pageSize);

    void deleteEntity(String deviceId, Long endTime);
}
2.4、IotDbMybatisServiceImpl
package com.example.service.impl;

import com.example.dao.IotDbMapper;
import com.example.dto.InsertEntityDTO;
import com.example.entity.DataEntity;
import com.example.service.IotDbMybatisService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class IotDbMybatisServiceImpl implements IotDbMybatisService {

    @Autowired
    private IotDbMapper iotDbMapper;

    @Override
    public Integer selectCount(String deviceId) {
        return iotDbMapper.selectCount(deviceId);
    }

    @Override
    public Integer insertEntity(InsertEntityDTO dataEntity) {
        return iotDbMapper.insertEntity(dataEntity);
    }

    @Override
    public List<DataEntity> pageResultData(String deviceId, Float temperature, String hardware, Boolean status, Integer page, Integer pageSize) {
        if (page == null || page < 1) {
            page = 1;
        }
        if (pageSize == null || pageSize < 1) {
            pageSize = 10;
        }
        int offset = (page - 1) * pageSize;
        List<DataEntity> entityList = iotDbMapper.pageResultData(deviceId, temperature, hardware, status, pageSize, offset);
        return entityList;
    }

    @Override
    public void deleteEntity(String deviceId, Long endTime) {
        iotDbMapper.deleteEntity(deviceId, endTime);
    }
}
2.5、IotDbMapper
package com.example.dao;

import com.example.dto.InsertEntityDTO;
import com.example.entity.DataEntity;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface IotDbMapper {
    Integer selectCount(String deviceId);

    Integer insertEntity(InsertEntityDTO dataEntity);

    List<DataEntity> pageResultData(String deviceId, Float temperature, String hardware, Boolean status, Integer pageSize, Integer offset);

    void deleteEntity(String deviceId, Long endTime);
}
2.6、IotDbMapper.xml

在使用resultMap时，返回字段需做别名操作temperature as temperature，与设置的resultMap匹配，否则数据映射出错拿不到查询的数据。因为除了Time之外其他的查询返回因是全路径的，比如查询的是status，实际返回结果为root.ln.wf01.wt01.status。

下面使用${deviceId}和#{deviceId}是方便共用一个xml，如果设备较少，可写入实际路径root.ln.wf01.wt02，使用多个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.example.dao.IotDbMapper">
    <resultMap id="BaseResultMap" type="com.example.entity.DataEntity">
        <result column="Time" property="timestamp"/>
        <result column="temperature" property="temperature"/>
        <result column="status" property="status"/>
        <result column="hardware" property="hardware"/>
    </resultMap>
    <select id="selectCount" resultType="Integer" parameterType="string">
        select count(*)
        from #{deviceId}
    </select>

    <insert id="insertEntity" parameterType="com.example.dto.InsertEntityDTO">
        insert into
        <choose>
            <when test="deviceId != null">
                ${deviceId}
            </when>
            <otherwise>
                root.ln.wf01.wt02
            </otherwise>
        </choose>
        (temperature, status, hardware)
        values (#{temperature}, #{status}, #{hardware})
    </insert>

    <select id="pageResultData" resultMap="BaseResultMap">
        select temperature as temperature,status as status,hardware as hardware
        from #{deviceId}
        <where>
            <if test="temperature != null">
                AND temperature = #{temperature}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
            <if test="hardware != null">
                AND hardware = #{hardware}
            </if>
        </where>
        limit #{pageSize}
        offset #{offset}
    </select>

    <delete id="deleteEntity">
        delete
        from #{deviceId}.*
        where timestamp = #{endTime}
    </delete>
</mapper>
2.7、IotDbMybatisTest
package com.example;

import com.example.dto.InsertEntityDTO;
import com.example.entity.DataEntity;
import com.example.service.IotDbMybatisService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@SpringBootTest
@RunWith(SpringRunner.class)
public class IotDbMybatisTest {

    @Autowired
    private IotDbMybatisService iotDbMybatisService;
    private static final String ROOT_LN_WF01_WT01 = "root.ln.wf01.wt01";


    @Test
    public void testSelectCount() {
        String deviceId = ROOT_LN_WF01_WT01;
        Integer count = iotDbMybatisService.selectCount(deviceId);
        System.out.println(count);
    }

    @Test
    public void testInsertEntity() {
        String deviceId = ROOT_LN_WF01_WT01;
        InsertEntityDTO dataEntity = new InsertEntityDTO();
        long currentTime = System.currentTimeMillis();
        dataEntity.setTimestamp(currentTime);
        dataEntity.setHardware("insert");
        dataEntity.setStatus(true);
        dataEntity.setTemperature(21F);
        dataEntity.setDeviceId(deviceId);
        Integer integer = iotDbMybatisService.insertEntity(dataEntity);
        System.out.println(integer);
    }

    @Test
    public void testPageResultData() {
        String deviceId = ROOT_LN_WF01_WT01;
        // string类型数据需加单引号，"test"->"'test'"
        List<DataEntity> dataEntities = iotDbMybatisService.pageResultData(deviceId, null, null, true, 1, 20);
        System.out.println(dataEntities.size());
        dataEntities.stream().forEach(System.out::println);
    }

    @Test
    public void testDeleteEntity() {
        String deviceId = ROOT_LN_WF01_WT01;
        long endTime = 1719303073560L;
        iotDbMybatisService.deleteEntity(deviceId, endTime);
    }
}
2.8、Entity & DTO
package com.example.entity;

import lombok.Data;

/**
 * mybatis对应返回数据
 *
 */
@Data
public class DataEntity {
    /**
     * 固定对应Time字段,必须添加
     */
    private Long timestamp;
    public Float temperature;
    public String hardware;
    public Boolean status;
}
package com.example.dto;

import lombok.Data;

@Data
public class InsertEntityDTO {
    /**
     * 固定对应Time字段,必须添加
     */
    private Long timestamp;
    public Float temperature;
    public String hardware;
    public Boolean status;
    public String deviceId;
}

这里使用的是Mybatis的方式来访问数据库，进行新增、查询、删除，相较于java原生接口的方式更容易使用，适用于少量数据处理场景。同时与之前使用过的有些不同，如数据类型方面，字符串型数据作为条件查询时需添加单引号，否则无效，等等其他地方。

# spring boot # 时序数据库 # iotdb # java # mybatis # 后端 # 数据库
Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

avatar 开放原子开发者工作坊

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

avatar 开放原子开发者工作坊

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

avatar 开放原子开发者工作坊