tio-boot使用mybatis-plus整合tdengine

头像
李通
     美国
    阅读 16 分钟

    tio-boot 使用 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.litongjava</groupId>
  <artifactId>tio-boot</artifactId>
  <version>${tio.boot.version}</version>
</dependency>
<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-api</artifactId>
  <version>1.7.25</version>
</dependency>

<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <version>${lombok-version}</version>
  <optional>true</optional>
  <scope>provided</scope>
</dependency>

<!--数据库驱动-->
<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>
<dependency>
  <groupId>com.litongjava</groupId>
  <artifactId>table-to-json</artifactId>
  <version>1.2.4</version>
</dependency>

<dependency>
  <groupId>com.litongjava</groupId>
  <artifactId>hotswap-classloader</artifactId>
  <version>${hotswap-classloader.version}</version>
</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.utils;

import javax.sql.DataSource;

public class TDUtils {

  public static DataSource ds;

  public static void setDataSource(DataSource ds) {
    TDUtils.ds = ds;
  }
}

    TdEngineDataSourceConfig
    TdEngineDataSourceConfig 类配置了 HikariCP 连接池，并连接到 TDengine 数据库。它设置了数据库连接属性，并创建了 HikariDataSource 实例。

    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 driverClassName = "com.taosdata.jdbc.rs.RestfulDriver";
    //String driverClassName = "com.taosdata.jdbc.TSDBDriver";

    // 添加batchfetch=true属性后得到的Websocket连接
    String jdbcUrl = getJdbcUrl(host, port, user, pswd);
    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 jdbcUrl = "jdbc:TAOS-RS://" + host + ":" + port + "/test_ws_parabind?user=" + user + "&password=" + pswd + "&batchfetch=true";
    return jdbcUrl;
  }
}

    添加 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.tio.http.server.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;
  }
}

    访问测试

    整合 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.tio.http.server.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，用于验证应用程序的功能。

    javatdenginemysql优化
    李通
    215 声望0 粉丝
    « 上一篇
    Tio Boot 与 Spring Boot Starter 的集成
    下一篇 »
    tio-boot 使用 jfinal-plugin 整合 TDEngine

    引用和评论

    推荐阅读
    头像
    tio-boot 整合hotswap-classloader 实现热加载

    李通阅读 332

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 1.9k

    头像
    Jerry和您聊聊Chrome开发者工具

    注销赞 9阅读 4.5k评论 3

    头像
    Bitmap 和 布隆过滤器傻傻分不清？你这不应该啊

    程序员小富赞 8阅读 1.6k评论 1

    头像
    Just for fun——迅速写完快速排序

    ufdf赞 3阅读 2.6k

    头像
    Spring 实现 3 种异步流式接口，干掉接口超时烦恼

    程序员小富赞 5阅读 1.7k

    头像
    💢线上高延迟请求排查

    crossoverJie赞 6阅读 802评论 5

    0 条评论
    得票最新