使用 mybatis-plus 整合 tdengine

介绍如何使用 tio-boot 结合 Mybatis-Plus 对 TDengine 进行整合。TDengine 支持 SQL 语言，允许我们利用 Mybatis-Plus 的强大功能进行数据操作。tio-boot 是一个高效的 Java Web 框架，用于构建基于事件驱动的应用程序。

添加依赖

• tio-boot: 用于构建基于 tio 的 web 应用。
• slf4j-api: 提供日志记录功能。
• lombok: 简化 Java 实体类的编写。
• taos-jdbcdriver: TDengine 的 JDBC 驱动。
• HikariCP: 高性能 JDBC 连接池。
• mybatis-plus-extension: MyBatis-Plus 扩展，用于简化数据库操作

<!--数据库驱动-->
<dependency>
  <groupId>com.taosdata.jdbc</groupId>
  <artifactId>taos-jdbcdriver</artifactId>
  <version>3.2.7</version>
</dependency>
<!--连接池-->
<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>HikariCP</artifactId>
  <version>4.0.3</version>
</dependency>

<!--mybatis-plus 无需spring依赖-->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-extension</artifactId>
  <version>3.3.0</version>
  <exclusions>
    <exclusion>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
    </exclusion>
  </exclusions>
</dependency>

启动类

HelloApp 类是应用程序的入口点，使用 TioApplicationWrapper.run 方法启动 tio 应用。

package com.litongjava.tio.web.hello;

import com.litongjava.hotswap.wrapper.tio.boot.TioApplicationWrapper;
import com.litongjava.jfinal.aop.annotation.AComponentScan;

@AComponentScan
public class HelloApp {
  public static void main(String[] args) {
    long start = System.currentTimeMillis();
    TioApplicationWrapper.run(HelloApp.class, args);
    long end = System.currentTimeMillis();
    System.out.println((end - start) + "ms");
  }
}

使用连接池整合 TDEngine

TDUtils 类是一个工具类，用于存储和提供数据源（DataSource）实例。

package com.litongjava.tio.web.hello.config;

import com.litongjava.jfinal.aop.annotation.ABean;
import com.litongjava.jfinal.aop.annotation.AConfiguration;
import com.litongjava.tio.web.hello.config.utils.TDUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;

@AConfiguration
public class TdEngineDataSourceConfig {
  @ABean(destroyMethod = "close", priority = 10)
  public DataSource hikariDataSource() {
    HikariConfig config = new HikariConfig();
    // jdbc properties
    String host = "192.168.3.9";
    int port = 6041;
    String user = "root";
    String pswd = "taosdata";
    String dbName = "test_ws_parabind";
    String driverClassName = "com.taosdata.jdbc.rs.RestfulDriver";
    // String driverClassName = "com.taosdata.jdbc.TSDBDriver";

    String jdbcUrl = getJdbcUrl(host, port, user, pswd, dbName);
    config.setJdbcUrl(jdbcUrl);
    config.setDriverClassName(driverClassName);
    // connection pool configurations
    config.setMinimumIdle(10); // minimum number of idle connection
    config.setMaximumPoolSize(10); // maximum number of connection in the pool
    config.setConnectionTimeout(30000); // maximum wait milliseconds for get connection from pool
    config.setMaxLifetime(0); // maximum life time for each connection
    config.setIdleTimeout(0); // max idle time for recycle idle connection
    config.setConnectionTestQuery("select server_status()"); // validation query

    HikariDataSource ds = new HikariDataSource(config); // create datasource
    TDUtils.setDataSource(ds);
    return ds;
  }

  private String getJdbcUrl(String host, int port, String user, String pswd, String dbName) {
    // 添加batchfetch=true属性后得到的Websocket连接
    return "jdbc:TAOS-RS://" + host + ":" + port + "/" + dbName + "?user=" + user + "&password=" + pswd
        + "&batchfetch=true";
  }
}

添加 Controller 创建数据 TbEngineTestController 类包含多个方法，用于创建数据表、插入数据以及执行查询操作。例如，

• init 方法创建了多个数据表，
• bindInteger 方法向表中插入整型数据。
• bindFloat 方法向表中插入浮点型数据。
• bindBoolean 方法向表中插入布尔型数据。
• bindBytes 方法向表中插入字节数组数据。
• bindString 方法向表中插入字符串数据。
• selectStable1 从表中查询数据。

package com.litongjava.tio.web.hello.controller;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;
import java.util.Random;

import com.litongjava.jfinal.plugin.activerecord.Record;
import com.litongjava.annotation.RequestPath;
import com.litongjava.tio.web.hello.config.utils.TDUtils;
import com.taosdata.jdbc.ws.TSWSPreparedStatement;

import lombok.Cleanup;

@RequestPath("/tdeingien/test")
public class TbEngineTestController {

  public String connection() throws SQLException {

    @Cleanup
    Connection connection = TDUtils.ds.getConnection();
    String string = connection.toString();
    return string;
  }

  /**
   * 创建表和数据库
   * @throws SQLException
   */
  public String init() throws SQLException {
    int BINARY_COLUMN_SIZE = 30;
    String[] schemaList = {
        "create table stable1(ts timestamp, f1 tinyint, f2 smallint, f3 int, f4 bigint) tags(t1 tinyint, t2 smallint, t3 int, t4 bigint)",
        "create table stable2(ts timestamp, f1 float, f2 double) tags(t1 float, t2 double)",
        "create table stable3(ts timestamp, f1 bool) tags(t1 bool)",
        "create table stable4(ts timestamp, f1 binary(" + BINARY_COLUMN_SIZE + ")) tags(t1 binary(" + BINARY_COLUMN_SIZE
            + "))",
        "create table stable5(ts timestamp, f1 nchar(" + BINARY_COLUMN_SIZE + ")) tags(t1 nchar(" + BINARY_COLUMN_SIZE
            + "))"
        //
    };

    @Cleanup
    Connection conn = TDUtils.ds.getConnection();

    try (Statement stmt = conn.createStatement()) {
      stmt.execute("drop database if exists test_ws_parabind");
      stmt.execute("create database if not exists test_ws_parabind");
      stmt.execute("use test_ws_parabind");
      for (int i = 0; i < schemaList.length; i++) {
        stmt.execute(schemaList[i]);
      }
    }
    return "success";
  }

  /**
   * init类型参数查询
   */
  public String bindInteger() throws SQLException {
    String sql = "insert into ? using stable1 tags(?,?,?,?) values(?,?,?,?,?)";
    int numOfSubTable = 10, numOfRow = 10;
    Random random = new Random(System.currentTimeMillis());
    @Cleanup
    Connection conn = TDUtils.ds.getConnection();

    try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {

      pstmt.execute("use test_ws_parabind");

      for (int i = 1; i <= numOfSubTable; i++) {
        // set table name
        pstmt.setTableName("t1_" + i);
        // set tags
        pstmt.setTagByte(1, Byte.parseByte(Integer.toString(random.nextInt(Byte.MAX_VALUE))));
        pstmt.setTagShort(2, Short.parseShort(Integer.toString(random.nextInt(Short.MAX_VALUE))));
        pstmt.setTagInt(3, random.nextInt(Integer.MAX_VALUE));
        pstmt.setTagLong(4, random.nextLong());
        // set columns
        long current = System.currentTimeMillis();
        for (int j = 0; j < numOfRow; j++) {
          pstmt.setTimestamp(1, new Timestamp(current + j));
          pstmt.setByte(2, Byte.parseByte(Integer.toString(random.nextInt(Byte.MAX_VALUE))));
          pstmt.setShort(3, Short.parseShort(Integer.toString(random.nextInt(Short.MAX_VALUE))));
          pstmt.setInt(4, random.nextInt(Integer.MAX_VALUE));
          pstmt.setLong(5, random.nextLong());
          pstmt.addBatch();
        }
        pstmt.executeBatch();
      }
    }
    return "success";
  }

  public String bindFloat() throws SQLException {
    String sql = "insert into ? using stable2 tags(?,?) values(?,?,?)";
    Random random = new Random(System.currentTimeMillis());
    @Cleanup
    Connection conn = TDUtils.ds.getConnection();

    int numOfSubTable = 10, numOfRow = 10;

    try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {

      pstmt.execute("use test_ws_parabind");

      for (int i = 1; i <= numOfSubTable; i++) {
        // set table name
        pstmt.setTableName("t2_" + i);
        // set tags
        pstmt.setTagFloat(1, random.nextFloat());
        pstmt.setTagDouble(2, random.nextDouble());
        // set columns
        long current = System.currentTimeMillis();
        for (int j = 0; j < numOfRow; j++) {
          pstmt.setTimestamp(1, new Timestamp(current + j));
          pstmt.setFloat(2, random.nextFloat());
          pstmt.setDouble(3, random.nextDouble());
          pstmt.addBatch();
        }
        pstmt.executeBatch();
      }
    }

    return "success";
  }

  public String bindBoolean() throws SQLException {
    String sql = "insert into ? using stable3 tags(?) values(?,?)";
    int numOfSubTable = 10, numOfRow = 10;
    Random random = new Random(System.currentTimeMillis());
    @Cleanup
    Connection conn = TDUtils.ds.getConnection();
    try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {
      for (int i = 1; i <= numOfSubTable; i++) {
        // set table name
        pstmt.setTableName("t3_" + i);
        // set tags
        pstmt.setTagBoolean(1, random.nextBoolean());
        // set columns
        long current = System.currentTimeMillis();
        for (int j = 0; j < numOfRow; j++) {
          pstmt.setTimestamp(1, new Timestamp(current + j));
          pstmt.setBoolean(2, random.nextBoolean());
          pstmt.addBatch();
        }
        pstmt.executeBatch();
      }
    }
    return "success";
  }

  public String bindBytes() throws SQLException {
    String sql = "insert into ? using stable4 tags(?) values(?,?)";
    int numOfSubTable = 10, numOfRow = 10;
    @Cleanup
    Connection conn = TDUtils.ds.getConnection();
    try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {

      for (int i = 1; i <= numOfSubTable; i++) {
        // set table name
        pstmt.setTableName("t4_" + i);
        // set tags
        pstmt.setTagString(1, new String("abc"));

        // set columns
        long current = System.currentTimeMillis();
        for (int j = 0; j < numOfRow; j++) {
          pstmt.setTimestamp(1, new Timestamp(current + j));
          pstmt.setString(2, "abc");
          pstmt.addBatch();
        }
        pstmt.executeBatch();
      }
    }
    return "success";
  }

  public String bindString() throws SQLException {
    String sql = "insert into ? using stable5 tags(?) values(?,?)";
    int numOfSubTable = 10, numOfRow = 10;
    @Cleanup
    Connection conn = TDUtils.ds.getConnection();

    try (TSWSPreparedStatement pstmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {

      for (int i = 1; i <= numOfSubTable; i++) {
        // set table name
        pstmt.setTableName("t5_" + i);
        // set tags
        pstmt.setTagNString(1, "California.SanFrancisco");

        // set columns
        long current = System.currentTimeMillis();
        for (int j = 0; j < numOfRow; j++) {
          pstmt.setTimestamp(0, new Timestamp(current + j));
          pstmt.setNString(1, "California.SanFrancisco");
          pstmt.addBatch();
        }
        pstmt.executeBatch();
      }
    }
    return "success";
  }

  public List<Record> selectStable1() throws SQLException {
    String sql = "select * from test.stable1";
    // List<Record> records = Db.find(sql);
    // return records;
    @Cleanup
    Connection conn = TDUtils.ds.getConnection();
    @Cleanup
    Statement stmt = conn.createStatement();
    stmt.execute("use test_ws_parabind");

    ResultSet resultSet = stmt.executeQuery(sql);

    return null;
  }
}

访问测试

• http://localhost/tdeingien/test/connection 返回如下 HikariProxyConnection@2084149084 wrapping com.taosdata.jdbc.rs.RestfulConnection@1a451d4d
• http://localhost/tdeingien/test/init
• http://localhost/tdeingien/test/bindInteger
• http://localhost/tdeingien/test/bindFloat
• http://localhost/tdeingien/test/bindBoolean
• http://localhost/tdeingien/test/bindBytes
• http://localhost/tdeingien/test/bindString

整合 mybatis-plus

MybatisPlusConfig 类配置了 MyBatis-Plus 以使用 TDengine 数据源。它创建了一个 SqlSessionFactory 实例，用于管理数据库会话。

package com.litongjava.tio.web.hello.config;

import javax.sql.DataSource;

import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionManager;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.MybatisSqlSessionFactoryBuilder;
import com.litongjava.jfinal.aop.annotation.AAutowired;
import com.litongjava.jfinal.aop.annotation.ABean;
import com.litongjava.jfinal.aop.annotation.AConfiguration;

@AConfiguration
public class MybatisPlusConfig {

  @AAutowired
  private DataSource dataSource;

  @ABean(destroyMethod = "close")
  public SqlSessionManager getSqlSession() {
    // environment
    Environment environment = new Environment("default", new JdbcTransactionFactory(), dataSource);
    // MybatisConfiguration
    org.apache.ibatis.session.Configuration configuration = new MybatisConfiguration();
    configuration.setEnvironment(environment);
    configuration.addMappers("com.litongjava.tio.web.hello.mapper");

    // MybatisSqlSessionFactoryBuilder
    MybatisSqlSessionFactoryBuilder builder = new MybatisSqlSessionFactoryBuilder();
    SqlSessionFactory sqlSessionFactory = builder.build(configuration);
    SqlSessionManager sqlSessionManager = SqlSessionManager.newInstance(sqlSessionFactory);
    return sqlSessionManager;
  }
}

这部分定义了与数据库表对应的 Java 实体类。例如，Stable1 类对应于数据库中的 stable1 表。

package com.litongjava.tio.web.hello.model;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Stable1 {
  // create table stable1(ts timestamp, f1 tinyint, f2 smallint, f3 int, f4 bigint) tags(t1 tinyint, t2 smallint, t3 int, t4 bigint)
  private java.sql.Timestamp ts;
  private java.lang.Byte f1;
  private java.lang.Short f2;
  private java.lang.Integer f3;
  private java.lang.Long f4;
}

package com.litongjava.tio.web.hello.model;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Stable2 {
  // create table stable2(ts timestamp, f1 float, f2 double) tags(t1 float, t2 double)
  private java.sql.Timestamp ts;
  private java.lang.Float f1;
  private java.lang.Double f2;
}

package com.litongjava.tio.web.hello.model;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Stable3 {
  // create table stable3(ts timestamp, f1 bool) tags(t1 bool)
  private java.sql.Timestamp ts;
  private java.lang.Boolean f1;
}

package com.litongjava.tio.web.hello.model;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Stable4 {
  //create table stable4(ts timestamp, f1 binary(30)) tags(t1 binary(30))
  private java.sql.Timestamp ts;
  private java.lang.Byte[] f1;
}

package com.litongjava.tio.web.hello.model;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Stable5 {
  // create table stable5(ts timestamp, f1 nchar(30)) tags(t1 nchar(30))
  private java.sql.Timestamp ts;
  private java.lang.String f1;
}

mapper 层,查询数据库

package com.litongjava.tio.web.hello.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.litongjava.tio.web.hello.model.Stable1;
public interface Stable1Mapper extends BaseMapper<Stable1> {
}

在 Service 层，我们定义了操作数据库的业务逻辑。例如，Stable1Service 类包含了查询 stable1 表的方法。

package com.litongjava.tio.web.hello.service;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionManager;

import com.litongjava.jfinal.aop.annotation.AAutowired;
import com.litongjava.tio.web.hello.mapper.Stable1Mapper;
import com.litongjava.tio.web.hello.model.Stable1;

import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class Stable1Service {

  @AAutowired
  private SqlSessionManager sqlSessionManager;
  public List<Stable1> selectList() {
    @Cleanup
    SqlSession sqlSession = sqlSessionManager.openSession(false);
    log.info("sqlSession:{}", sqlSession);
    Stable1Mapper userMapper = sqlSession.getMapper(Stable1Mapper.class);

    log.info("userMapper:{}", userMapper);
    List<Stable1> selectList = userMapper.selectList(null);
    return selectList;
  }
}

Stable1Controller 类处理来自客户端的请求，并调用 Service 层的方法。

package com.litongjava.tio.web.hello.controller;

import java.util.List;

import com.litongjava.jfinal.aop.annotation.AAutowired;
import com.litongjava.annotation.RequestPath;
import com.litongjava.tio.web.hello.model.Stable1;
import com.litongjava.tio.web.hello.service.Stable1Service;

@RequestPath("/stable1")
public class Stable1Controller {

  @AAutowired
  private Stable1Service stable1Service;

  public List<Stable1> selectList() {
    return stable1Service.selectList();
  }
}

其他 Controller 和 Service 略

测试

访问测试 URL，用于验证应用程序的功能。

• http://localhost/stable1/selectList
• http://localhost/stable2/selectList
• http://localhost/stable3/selectList
• http://localhost/stable4/selectList
• http://localhost/stable5/selectList