动态

详情 返回 返回

tio-boot使用mybatis-plus整合tdengine - 动态 详情

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;
  }
}

訪問測試

  • 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.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，用於驗證應用程序的功能。

  • http://localhost/stable1/selectList
  • http://localhost/stable2/selectList
  • http://localhost/stable3/selectList
  • http://localhost/stable4/selectList
  • http://localhost/stable5/selectList
tdengine , mysql優化 , JAVA
user avatar king_wenzhinan 头像 u_17513518 头像 journey_64224c9377fd5 头像 u_16502039 头像 debuginn 头像 seazhan 头像 AmbitionGarden 头像 u_16769727 头像 lenglingx 头像 u_15702012 头像 lvlaotou 头像 jkdataapi 头像
点赞 68 用户, 点赞了这篇动态!
点赞

Add a new 评论

Some HTML is okay.