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://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。