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号'"
}
原文地址：https://blog.csdn.net/weixin_43564627/article/details/137860743
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！