IoTDB数据库整合MyBatis实现SpringBoot项目CRUD

遇到的问题：

1.启动项目提示：testWhileIdle is true, validationQuery not set。
2023-04-26 14:05:39.282 ERROR 13864 --- [           main] com.alibaba.druid.pool.DruidDataSource   : testWhileIdle is true, validationQuery not set

原因：没有在配置文件中设置（最先的作者可能没遇到这个，不过报错也能用，可以不管）

druid:
validation-query: 连接测试语句

2.查询一直报错

  2.1错误一：mismatched input ‘<EOF>,’ excepting ‘{FROM,INTO}‘

反复检查：是因为validation-query:的连接测试语句不能写成网络上大家正对mysql设置的select

2.2错误二：java.lang.NullPointerException

原因：返回结果集的时候类型不能是jdbcType,改成JavaType问题解决，但是…

  2.3错误三：Error attempting to get column “Time” from result set

原因：不管你需不需要Time字段（需要也不能在select后面写），查询结果都会返回Time字段的值，这是iotdb特有的主键，但是本人试了LONG，VARCHAR，DATE等能想到的类型，都无济于事，但是可以通过Java.lang.String接收到返回的Long类型时间值，通过resultMapper就是无法实现。

最终解决：通过Map<String,Object>类型将所有数据接收出来，然后再处理（目前我只能这样操作，希望有更好办法的兄弟交流学习）

 3.删除操作，提示成功，但是去库里查询，还在！！！

我这就无语了，没删除就没删除呗，有错你就提呗，你来个成功，然后结果未实现。

原因：删除的时候，比如要删除某个时间段的一组数据，必须在设备id后面添加.*，如：

delete from root.XXX.XXX.demo20130424_4.* where time > XXXXXX

最后总算成功通过mybatis实现了自己想要的功能，不过还是有很多不足，还希望有兴趣的同学一起研究。

接下来上代码：

1.主要依赖：

<dependency>
			<groupId>org.apache.iotdb</groupId>
			<artifactId>iotdb-jdbc</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.5.3.1</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.2.16</version>
		</dependency>

2.数据库配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: org.apache.iotdb.jdbc.IoTDBDriver
      url: jdbc:iotdb://127.0.0.1:6667/
      username: root
      password: root
      initial-size: 5
      max-active: 20
      min-idle: 1
      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-plus:
  mapper-locations: classpath:/mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

WeatherEntity

package com.example.wys.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 添加说明
 *
 * @author kit
 * @version 1.0
 * @date 2023/11/7 17:02
 */
@Data
@Builder
public class WeatherEntity {
    /**
     * 固定对应Time字段
     */
    private long timestamp;

    /**
     * 采样时间时间戳
     */
    private String samplingTime;

    /**
     * 采样时间字符
     */
    private String samplingTimeStr;

    /**
     * 城市编码
     */
    private Integer cityKey;

    /**
     * 城市
     */
    private String city;

    /**
     * 温度 ℃
     */
    private float temperature;

    /**
     * 湿度 %
     */
    private float humidity;

    /**
     * pm10
     */
    private float pm10;

    /**
     * pm10
     */
    private float pm25;

    /**
     * 空气质量
     */
    private String quality;

    /**
     * 天气描述
     */
    private String remark;


}

Rst

package com.example.wys.utils;

import java.io.Serializable;
import java.util.LinkedHashMap;

/**
 * 返回结果
 *
 * @author kit
 * @version 1.0
 * @date 2023/11/9 11:23
 */
public class Rst extends LinkedHashMap<String, Object> implements Serializable {

    private static final long serialVersionUID = 1L;    // 序列化版本号
    public static final int CODE_SUCCESS = 200;
    public static final int CODE_ERROR = 500;

    public Rst() {
    }

    public Rst(int code, String msg, Object data) {
        this.setCode(code);
        this.setMsg(msg);
        this.setData(data);
    }

    /**
     * 获取code
     *
     * @return code
     */
    public Integer getCode() {
        return (Integer) this.get("code");
    }

    /**
     * 获取msg
     *
     * @return msg
     */
    public String getMsg() {
        return (String) this.get("msg");
    }

    /**
     * 获取data
     *
     * @return data
     */
    public Object getData() {
        return (Object) this.get("data");
    }

    /**
     * 给code赋值，连缀风格
     *
     * @param code code
     * @return 对象自身
     */
    public Rst setCode(int code) {
        this.put("code", code);
        return this;
    }

    /**
     * 给msg赋值，连缀风格
     *
     * @param msg msg
     * @return 对象自身
     */
    public Rst setMsg(String msg) {
        this.put("msg", msg);
        return this;
    }

    /**
     * 给data赋值，连缀风格
     *
     * @param data data
     * @return 对象自身
     */
    public Rst setData(Object data) {
        this.put("data", data);
        return this;
    }

    // 构建成功
    public static Rst ok() {
        return new Rst(CODE_SUCCESS, "ok", null);
    }

    public static Rst ok(String msg) {
        return new Rst(CODE_SUCCESS, msg, null);
    }

    public static Rst code(int code) {
        return new Rst(code, null, null);
    }

    public static Rst data(Object data) {
        return new Rst(CODE_SUCCESS, "ok", data);
    }

    // 构建失败
    public static Rst error() {
        return new Rst(CODE_ERROR, "error", null);
    }

    public static Rst error(String msg) {
        return new Rst(CODE_ERROR, msg, null);
    }

    @Override
    public String toString() {
        return "{"
                + "\"code\": " + this.getCode()
                + ", \"msg\": " + transValue(this.getMsg())
                + ", \"data\": " + transValue(this.getData())
                + "}";
    }

    private String transValue(Object value) {
        if (value instanceof String) {
            return "\"" + value + "\"";
        }
        return String.valueOf(value);
    }
}

3.controller，

package com.example.wys.controller;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;

import com.example.wys.entity.WeatherEntity;
import com.example.wys.service.WeatherService;
import com.example.wys.utils.Constant;
import com.example.wys.utils.Rst;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.math.RoundingMode;
import java.util.Date;

/**
 * 添加说明
 *
 * @author kit
 * @version 1.0
 * @date 2023/11/7 16:50
 */
@RestController
@RequestMapping("/weather")
public class WeatherController {

    @Resource
    WeatherService weatherService;

    /**
     * 新增
     *
     * @return
     */
    @GetMapping("add")
    public Rst add() {
        Date date = new Date();
        Long dateTime = date.getTime();
        // 模拟数据
        // 此处涉及到的字符串的，必须前后加',如下面的city字段，quality字段， remark字段
        WeatherEntity testEntity = WeatherEntity.builder()
                .samplingTime(dateTime.toString())
                .samplingTimeStr("'" + DateUtil.format(date, "yyyy-MM-dd HH:mm:ss") + "'")
                .cityKey(101190101)
                .city("'南京'")
                .temperature(NumberUtil.parseFloat(StrUtil.toString(RandomUtil.randomDouble(-18.2, 30.5, 1, RoundingMode.HALF_UP))))
                .humidity(NumberUtil.parseFloat(StrUtil.toString(RandomUtil.randomDouble(1, 100, 1, RoundingMode.HALF_UP))))
                .pm10(NumberUtil.parseFloat(StrUtil.toString(RandomUtil.randomDouble(0, 300, 0, RoundingMode.HALF_UP))))
                .pm25(NumberUtil.parseFloat(StrUtil.toString(RandomUtil.randomDouble(0, 300, 1, RoundingMode.HALF_UP))))
                .quality("'" + Constant.QUALITY_OPTIONS[RandomUtil.randomInt(0, 3)] + "'")
                .remark("'模拟插入'").build();
        return Rst.data(weatherService.addWeather(testEntity));
    }

    /**
     * 分页
     *
     * @param page     第几页
     * @param pageSize 每页多少条
     * @return
     */
    @GetMapping("page")
    public Rst page(Integer page, Integer pageSize) {
        return Rst.data(weatherService.pageWeather(page, pageSize));
    }

    /**
     * 删除数据
     * 对于delete语句，其中子句只能包含时间表达式，目前不支持值筛选
     *
     * @param startTime 需要固定格式为yyyy-MM-dd HH:mm:ss
     * @param endTime   需要固定格式为yyyy-MM-dd HH:mm:ss
     * @return
     */
    @GetMapping("delete")
    public Rst delete(String startTime, String endTime) {
        // 官方对于delete语句，其中子句只能包含时间表达式，目前不支持其他值筛选
        return Rst.data(weatherService.deleteWeather(startTime, endTime));
    }
}

4.service

package com.example.wys.service;



import com.example.wys.entity.WeatherEntity;

import java.util.List;

/**
 * 添加说明
 *
 * @author kit
 * @version 1.0
 * @date 2023/11/7 16:50
 */
public interface WeatherService {

    Integer addWeather(WeatherEntity weatherEntity);

    List<WeatherEntity> pageWeather(Integer page, Integer pageSize);

    Integer deleteWeather(String startTime, String endTime);
}

4.2实现类

package com.example.wys.service.impl;


import com.example.wys.entity.WeatherEntity;
import com.example.wys.mapper.WeatherMapper;
import com.example.wys.service.WeatherService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * 添加说明
 *
 * @author kit
 * @version 1.0
 * @date 2023/11/7 16:50
 */
@Service
public class WeatherServiceImpl implements WeatherService {

    @Resource
    WeatherMapper weatherMapper;

    @Override
    public Integer addWeather(WeatherEntity testEntity) {
        return weatherMapper.addWeather(testEntity);
    }

    @Override
    public List<WeatherEntity> pageWeather(Integer page, Integer pageSize) {
        if (page == null || page < 1) {
            page = 1;
        }
        if (pageSize == null || pageSize < 1) {
            pageSize = 10;
        }
        int offset = (page - 1) * pageSize;

        List<WeatherEntity> entityList = weatherMapper.pageWeather(pageSize, offset);

        System.out.println(entityList);
        return entityList;
    }

    @Override
    public Integer deleteWeather(String startTime, String endTime) {
        return weatherMapper.deleteWeather(startTime, endTime);
    }
}

5.mapper

package com.example.wys.mapper;

import com.example.wys.entity.WeatherEntity;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 添加说明
 *
 * @author kit
 * @version 1.0
 * @date 2023/11/7 16:52
 */
@Mapper
public interface WeatherMapper {

    Integer addWeather(WeatherEntity weatherEntity);

    List<WeatherEntity> pageWeather(@Param("pageSize") Integer pageSize, @Param("offset") Integer offset);

    Integer deleteWeather(@Param("startTime") String startTime, @Param("endTime") String endTime);
}

6.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.example.wys.mapper.WeatherMapper">

    <resultMap id="BaseResultMap" type="com.example.wys.entity.WeatherEntity">
        <result column="Time" property="timestamp" />
        <result column="root.ln.weather.samplingTime" property="samplingTime"/>
        <result column="root.ln.weather.samplingTimeStr" property="samplingTimeStr"/>
        <result column="root.ln.weather.cityKey" property="cityKey"/>
        <result column="root.ln.weather.city" property="city"/>
        <result column="root.ln.weather.temperature" property="temperature"/>
        <result column="root.ln.weather.humidity" property="humidity"/>
        <result column="root.ln.weather.pm10" property="pm10"/>
        <result column="root.ln.weather.pm25" property="pm25"/>
        <result column="root.ln.weather.quality" property="quality"/>
        <result column="root.ln.weather.remark" property="remark"/>
    </resultMap>

    <insert id="addWeather" parameterType="com.example.wys.entity.WeatherEntity">
        insert into root.ln.weather(samplingTime, samplingTimeStr, cityKey, city, temperature, humidity, pm10, pm25,
                                    quality, remark)
        values (#{samplingTime}, #{samplingTimeStr}, #{cityKey}, #{city}, #{temperature}, #{humidity}, #{pm10}, #{pm25},
                #{quality}, #{remark})
    </insert>

    <select id="pageWeather" resultMap="BaseResultMap">
        select amplingTime, samplingTimeStr,city, temperature, humidity, pm10, pm25,quality, remark from root.ln.weather
    </select>

    <delete id="deleteWeather" parameterType="java.lang.String">
        delete
        from root.ln.weather.*
        where time <![CDATA[ >= ]]> ${startTime}
          and time <![CDATA[ <= ]]> ${endTime};
    </delete>

</mapper>

postman 测试

127.0.0.1:8083/weather/page

注意：利用postman测试时，传入的string类型的属性值和整合mysql的用法有区别：

  平时传入的json如下：

{

    "timestamp":"2023-05-04T18:28:20.689+08:00",

    "name":"小明",

    "age":20,

    "sex":"男",

    "address":"西安108号"

}

    但是这里json传参需要给String类型加上单引号' '，否则，运行会报sql错误

{

    "timestamp":"2023-05-04T18:28:20.689+08:00",

    "name":"'小明'",

    "age":20,

    "sex":"'男'",

    "address":"'西安108号'"

}