TDengine数据库整合MyBatis实现SpringBoot项目CRUD
官网： https://docs.taosdata.com/
引入依赖
<!-- mybatis版本必须与druid版本兼容，否则无法创建DataSource --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.21</version></dependency><dependency><groupId>com.taosdata.jdbc</groupId><artifactId>taos-jdbcdriver</artifactId><version>3.0.0</version></dependency>
修改配置文件
spring.datasource.driver-class-name=com.taosdata.jdbc.rs.RestfulDriver
spring.datasource.url=jdbc:TAOS-RS://124.220.26.176:6041/power?charset=UTF-8&locale=en_US.UTF-8&timezone=UTC-8
spring.datasource.username=test
spring.datasource.password=123456spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=5
# max wait time for get connection, ms
spring.datasource.druid.max-wait=60000spring.datasource.druid.validation-query=select server_status();
spring.datasource.druid.validation-query-timeout=5000
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=600000
spring.datasource.druid.max-evictable-idle-time-millis=900000# mybatis
mybatis.mapper-locations=classpath:mapper/*.xml
创建普通表
CREATE TABLE power.orders (ts TIMESTAMP NOT NULL,id INT,name VARCHAR(64),email VARCHAR(64),age INT,CONSTRAINT ts PRIMARY KEY (ts)
);
实体类
@Data
public class Orders {@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss.SSS")Timestamp ts;Integer id;String name;Integer age;String email;}Controller
@RestController
public class UserController {@Autowiredprivate OrderService orderService;@GetMapping("/list")public List<Orders> list(){return orderService.list();}@GetMapping("/pageList")public List<Orders> pageList(int offset, int pageSize){return orderService.pageList(offset,pageSize);}/*** 时间戳一样的时候 只能插入一条数据* @return*/@PostMapping("/addOrder")public Integer addOrder(){return orderService.addOrder();}@PostMapping("/insertOne")public Integer insertOne(){return orderService.insertOne();}/***可选参数，指定删除数据的过滤条件，不指定过滤条件则为表中所有数据，请慎重使用。特别说明，这里的 where 条件中只支持对第一列时间列的过滤。* @param date* @return*/@GetMapping("/delete")public Integer delete(String date){return orderService.delete(date);}/*** 更新* 可以通过写入重复时间戳的一条数据来更新时序数据* @return*/@GetMapping("/update")public Integer update(){return orderService.update();}}service
public interface OrderService {Integer addOrder();Integer insertOne();List<Orders> list();List<Orders> pageList(int offset, int pageSize);Integer delete(String date);Integer update();
}
ServiceImpl
@Service
public class OrderServiceImpl implements OrderService{@Autowiredprivate OrderMapper orderMapper;@Overridepublic Integer addOrder() {Orders order=new Orders();order.setTs(new Timestamp(new Date().getTime()));order.setAge(11);order.setId(2);order.setName("张三");order.setEmail("12345");
// for (int i=0;i<10;i++){
// orderMapper.insert(order);
// }orderMapper.addOrder(order);return 1;}@Overridepublic Integer insertOne() {Orders order=new Orders();order.setTs(new Timestamp(new Date().getTime()));order.setAge(11);order.setId(2);order.setName("张三");order.setEmail("12345");
// for (int i=0;i<10;i++){
// orderMapper.insert(order);
// }orderMapper.insertOne(order);return 1;}@Overridepublic List<Orders> list() {return orderMapper.list();}@Overridepublic List<Orders> pageList(int offset, int pageSize) {return orderMapper.pageList(offset,pageSize);}@Overridepublic Integer delete(String date) {return orderMapper.delete(date);}@Overridepublic Integer update() {Orders order=new Orders();order.setTs(new Timestamp(new Date().getTime()));order.setAge(222);order.setId(333);order.setName("张3333");order.setEmail("12345");int update = orderMapper.update(order);return update;}
}OrderMapper
public interface OrderMapper {/*** 新增* @author BEJSON* @date 2024/10/22**/@Insert("insert into orders (ts,id, name,email,age) values(#{ts},#{id},#{name},#{email},#{age})")int insert(Orders order);void insertOne(Orders order);void addOrder(Orders order);/*** 刪除* @author BEJSON* @date 2024/10/22**/int delete(String date);/*** 更新* @author BEJSON* @date 2024/10/22**/int update(Orders order);/*** 查询 根据主键 id 查询* @author BEJSON* @date 2024/10/22**/Orders load(int id);/*** 查询 分页查询* @author BEJSON* @date 2024/10/22**/List<Orders> pageList(@Param("offset") int offset,@Param("pageSize") int pageSize);/*** 查询 分页查询 count* @author BEJSON* @date 2024/10/22**/int pageListCount(int offset,int pagesize);List<Orders> list();
}mapper.xml
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.OrderMapper"><resultMap id="BaseResultMap" type="com.example.domain.Orders" ><result column="id" property="id" /><result column="name" property="name" /><result column="age" property="age" /><result column="email" property="email" /></resultMap><sql id="Base_Column_List">ts,id,name,email,age</sql><insert id="insertOne" parameterType="com.example.domain.Orders">insert into orders (ts,id, name,email,age) values(#{ts},#{id},#{name},#{email},#{age})</insert><insert id="addOrder" parameterType="com.example.domain.Orders">insert into orders(<if test="ts != null ">ts,</if><if test="id != null ">id,</if><if test="name != null and name != ''">name,</if><if test="email != null and email != ''">email,</if><if test="age != null ">age</if>)values(<if test="ts != null ">#{ts},</if><if test="id != null ">#{id},</if><if test="name != null and name != ''">#{name},</if><if test="email != null and email != ''">#{email},</if><if test="age != null ">#{age}</if>)</insert><delete id="delete" >DELETE FROM ordersWHERE ts > #{date}</delete><update id="update" parameterType="com.example.domain.Orders">UPDATE orders<set><if test="null != name and '' != name">name = #{name},</if><if test="null != age ">age = #{age},</if><if test="null != email and '' != email">email = #{email}</if></set>WHERE id = #{id}</update><select id="load" resultMap="BaseResultMap">SELECT <include refid="Base_Column_List" />FROM ordersWHERE id = #{id}</select><select id="pageList" resultMap="BaseResultMap">SELECT <include refid="Base_Column_List" />FROM ordersLIMIT #{offset}, #{pageSize}</select><select id="list" resultMap="BaseResultMap">SELECT <include refid="Base_Column_List" />FROM orders</select><select id="pageListCount" resultType="java.lang.Integer">SELECT count(1)FROM orders</select></mapper>