对TDengine进行简单的操作

项目结构

pom文件

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
		<!-- 使用最新的版本-->
        <dependency>
            <groupId>com.taosdata.jdbc</groupId>
            <artifactId>taos-jdbcdriver</artifactId>
            <version>2.0.34</version>
        </dependency>

        <!-- MySQL的JDBC数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.34</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.17</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

配置文件

spring:
  datasource:
    tdengine-server:
      jdbc-url: jdbc:TAOS://192.168.31.100:6030/test?user=root&password=root&charset=UTF-8&locale=en_US.UTF-8&timezone=UTC-8
      username: root
      password: root
      type: com.zaxxer.hikari.HikariDataSource      # Hikari连接池的设置
      minimum-idle: 5                 #最小连接
      maximum-pool-size: 15        #最大连接
      auto-commit: true        #自动提交
      idle-timeout: 30000        #最大空闲时常
      pool-name: TDengineDruidCP        #连接池名
      max-lifetime: 1800000        #最大生命周期
      connection-timeout: 30000        #连接超时时间
      connection-test-query: show tables

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: false

配置类

package com.mye.mybatisplusdemo.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
public class TDengineServerConfig {
    @Bean(name = "tdengineDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.tdengine-server")
    public DataSource tdengineDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "tdengineSqlSessionFactory")
    public SqlSessionFactory tdengineSqlSessionFactory(@Qualifier("tdengineDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
// bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/tdengine/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "tdengineTransactionManager")
    public DataSourceTransactionManager tdengineTransactionManager(@Qualifier("tdengineDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "tdengineSqlSessionTemplate")
    public SqlSessionTemplate tdengineSqlSessionTemplate(@Qualifier("tdengineSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

实体类

package com.mye.mybatisplusdemo.domain;

import lombok.Data;

import java.sql.Timestamp;

@Data
public class Temperature {

    private Timestamp ts;
    private float temperature;
    private String location;
    private int tbIndex;
}

mapper层

package com.mye.mybatisplusdemo.mapper;

import com.mye.mybatisplusdemo.domain.Temperature;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface TemperatureMapper{

    @Update("CREATE TABLE if not exists temperature(ts timestamp, temperature float) tags(location nchar(64), tbIndex int)")
    int createSuperTable();

    @Update("create table #{tbName} using temperature tags( #{location}, #{tbindex})")
    int createTable(@Param("tbName") String tbName, @Param("location") String location, @Param("tbindex") int tbindex);

    @Update("drop table if exists temperature")
    void dropSuperTable();

    @Insert("insert into t${tbIndex}(ts, temperature) values(#{ts}, #{temperature})")
    int insertOne(Temperature one);

    @Select("select * from temperature where location = #{location}")
    List<Temperature> selectTemperatureByLocation(@Param("location") String location);

    @Select("select * from temperature")
    List<Temperature> selectAll();

    @Select("select count(*) from temperature where temperature = 0.5")
    int selectCount();

    @Update("create database if not exists test")
    void createDB();

}

启动类

package com.mye.mybatisplusdemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = {"com.mye.mybatisplusdemo.mapper"})
public class MybatisplusdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisplusdemoApplication.class, args);
    }

}

测试类

package com.mye.mybatisplusdemo;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.mye.mybatisplusdemo.domain.Temperature;
import com.mye.mybatisplusdemo.mapper.TemperatureMapper;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.sql.Timestamp;
import java.util.List;
import java.util.Random;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = MybatisplusdemoApplication.class)
public class TemperatureMapperTest {

    private static Random random = new Random(System.currentTimeMillis());
    private static String[] locations = {"北京", "上海", "深圳", "广州", "杭州"};

    @Autowired
    private TemperatureMapper mapper;

    @Before
    public void before() {
        //创建数据库
        mapper.createDB();
        //删除超级表
        mapper.dropSuperTable();
        // 创建超级表
        mapper.createSuperTable();
        // 使用超级表创建表格(实际上就是添加数据，一个表就是一个数据)
        for (int i = 0; i < 10; i++) {
            mapper.createTable("t" + i, locations[random.nextInt(locations.length)], i);
        }
        // insert into table
        // insert 10 tables
        for (int i = 0; i < 10; i++) {
            // each table insert 5 rows
            for (int j = 0; j < 5; j++) {
                Temperature one = new Temperature();
                one.setTs(new Timestamp(1605024000000l));
                one.setTemperature(random.nextFloat() * 50);
                one.setLocation("望京");
                one.setTbIndex(i);
                mapper.insertOne(one);
            }
        }
    }

// @After
// public void after() {
// mapper.dropSuperTable();
// }

    /** * 根据名称查询 */
    @Test
    public void testSelectByLocation(){
        List<Temperature> temperatureList = mapper.selectTemperatureByLocation("广州");
        System.out.println(temperatureList);
    }

    /** * 查询所有 */
    @Test
    public void testSelectAll(){
        List<Temperature> temperatures = mapper.selectAll();
        System.out.println(temperatures.size());
    }

    /** * 插入数据 */
    @Test
    public void testInsert() {
        //时间一样的时候，数据不会发现改变 1604995200000
        Temperature one = new Temperature();
        one.setTs(new Timestamp(1604995200000l));
        one.setTemperature(0.6f);
        one.setLocation("望京");
        int i = mapper.insertOne(one);
        System.out.println(i);
    }

    /** * 查询数量 */
    @Test
    public void testSelectCount(){
        int count = mapper.selectCount();
        System.out.println(count);
    }

    /** * 分页查询 */
    @Test
    public void testPage(){
        //查询之前，设置当前页和当前页的数量
        PageHelper.startPage(1, 2);
        List<Temperature> temperatureList = mapper.selectAll();
        //把查询结果放入到pageInfo对象中
        PageInfo<Temperature> pageInfo = new PageInfo<>(temperatureList);
        long total = pageInfo.getTotal();
        int pageNum = pageInfo.getPageNum();
        List<Temperature> list = pageInfo.getList();
        System.out.println("总数："+total);
        System.out.println("页数："+pageNum);
        System.out.println(list);

    }
}