springboot整合TDengine全过程

        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-web</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>com.alibaba</groupid>
            <artifactid>druid</artifactid>
            <version>1.1.17</version>
        </dependency>

        <dependency>
            <groupid>com.github.pagehelper</groupid>
            <artifactid>pagehelper</artifactid>
            <version>4.1.0</version>
        </dependency>

        <dependency>
            <groupid>com.squareup.okhttp3</groupid>
            <artifactid>okhttp</artifactid>
            <version>3.8.1</version>
        </dependency>

        <dependency>
            <groupid>junit</groupid>
            <artifactid>junit</artifactid>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

@data
public class temperature {

    private timestamp ts;
    private float temperature;
    private string location;
    private int tbindex;
}

@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();

    @update("drop database if exists test")
    void dropdb();
}

@configuration
@enabletransactionmanagement
@mapperscan(basepackages = {"com.yolo.springboottdengine.mapper"}, sqlsessionfactoryref = "tdenginesqlsessionfactory")
public class tdengineconfiguration {

    @bean(name = "tdenginedatasource")
    public datasource tdenginedatasource() throws exception {

        // 125  tdengine测试环境
        string taoshost = "127.0.0.1";
        string taosport = "6030";
        string taosusername = "root";
        string taospassword = "root";
        string taosdb = "test";


        druiddatasource datasource = new druiddatasource();
        datasource.setdriverclassname("com.taosdata.jdbc.tsdbdriver");
        datasource.seturl("jdbc:taos://" + taoshost + ":" + taosport + "/" + taosdb
               + "?charset=utf-8&locale=zh_cn.utf-8&timezone=utc-8");

        datasource.setpassword(taospassword);
        datasource.setusername(taosusername);
        datasource.setinitialsize(5);
        datasource.setminidle(10);
        datasource.setmaxactive(100);
        datasource.setmaxwait(30000);
        datasource.setvalidationquery("select server_status()");
        return datasource;
    }


    @bean(name = "tdenginetransactionmanager")
    public datasourcetransactionmanager tdenginetransactionmanager() throws exception {
        return new datasourcetransactionmanager(tdenginedatasource());
    }

    @bean(name = "tdenginesqlsessionfactory")
    public sqlsessionfactory sqlsessionfactory(@qualifier("tdenginedatasource") datasource datasource, pagehelper pagehelper) throws exception {
        final sqlsessionfactorybean sessionfactory = new sqlsessionfactorybean();
        sessionfactory.setdatasource(datasource);
        sessionfactory.setplugins(pagehelper);
        return sessionfactory.getobject();
    }

    @bean
    public sqlsessiontemplate sqlsessiontemplate(@qualifier("tdenginesqlsessionfactory")sqlsessionfactory sqlsessionfactory) {
        return new sqlsessiontemplate(sqlsessionfactory);
    }

    @bean
    public pagehelper pagehelper() {
        pagehelper pagehelper = new pagehelper();
        properties p = new properties();
        p.setproperty("offsetaspagenum", "true");
        p.setproperty("rowboundswithcount", "true");
        p.setproperty("reasonable", "true");
        p.setproperty("dialect", "mysql");
        pagehelper.setproperties(p);
        return pagehelper;
    }

}

@springboottest
@runwith(springrunner.class)
public class temperaturetest {

    private static final random random = new random(system.currenttimemillis());
    private static final string[] locations = {"北京", "上海", "深圳", "广州", "杭州"};

    @autowired
    private temperaturemapper temperaturemapper;

    @test
    public void createdatabase(){
        temperaturemapper.dropdb();
        temperaturemapper.createdb();
    }

    @test
    public void init() {
        temperaturemapper.dropsupertable();
        // create table temperature
        temperaturemapper.createsupertable();
        // create table t_x using temperature
        for (int i = 0; i < 10; i++) {
            temperaturemapper.createtable("t" + i, locations[random.nextint(locations.length)], i);
        }
        // insert into table
        int affectrows = 0;
        // 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(system.currenttimemillis()));
                one.settemperature(random.nextfloat() * 50);
                one.setlocation("望京");
                one.settbindex(i);
                affectrows += temperaturemapper.insertone(one);
            }
        }
        assert.assertequals(50, affectrows);
    }

    /**
     * 根据名称查询
     */
    @test
    public void testselectbylocation() {
        list<temperature> temperaturelist = temperaturemapper.selecttemperaturebylocation("广州");
        system.out.println(temperaturelist);
    }

    /**
     * 查询所有
     */
    @test
    public void testselectall() {
        list<temperature> temperatures = temperaturemapper.selectall();
        system.out.println(temperatures.size());
    }

    /**
     * 插入数据
     */
    @test
    public void testinsert() {
        //时间一样的时候，数据不会发现改变   1604995200000
        temperature one = new temperature();
        one.setts(new timestamp(1604995222224l));
        one.settemperature(1.2f);
        int i = temperaturemapper.insertone(one);
        system.out.println(i);
    }

    /**
     * 查询数量
     */
    @test
    public void testselectcount() {
        int count = temperaturemapper.selectcount();
        system.out.println(count);
    }

    /**
     * 分页查询
     */
    @test
    public void testpage() {
        //查询之前，设置当前页和当前页的数量
        pagehelper.startpage(1, 2);
        list<temperature> temperaturelist = temperaturemapper.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);

    }
}

public class weather {
//    @jsonformat(pattern = "yyyy-mm-dd hh:mm:ss.sss", timezone = "gmt+8")
    private timestamp ts;
    private float temperature;
    private float humidity;
    private string location;
    private string note;
    private int groupid;
    
    //省略构造方法和get/set方法
}

public class tdenginerestfulinfo {

    private long  rows;
    private string status;
    private list<string> head;
    private list<list<string>> data;
    private list<list<string>> column_meta;
    //省略构造方法和get/set方法
}

app.td.rest.url=http://127.0.0.1:6041/rest/sql
app.td.rest.basic=basic cm9vd90
app.td.db=test

@springboottest
@runwith(springrunner.class)
public class weathertest {

    private static final logger logger = loggerfactory.getlogger(weathertest.class);

    @value("${app.td.rest.url}")
    @notblank
    private string tdresturl;

    @value("${app.td.rest.basic}")
    @notblank
    private string tdrestbasic;

    @value("${app.td.db}")
    @notblank
    private string db;

    private final random random = new random(system.currenttimemillis());
    private final string[] locations = {"北京", "上海", "广州", "深圳", "天津"};

    @test
    public void createdb() {
        string sql = "create database if not exists test";
        string url = tdresturl;
        tdenginerestful(url, sql);
    }

    @test
    public void dropdb() {
        string sql = "drop database if exists test";
        tdenginerestful2(tdresturl, sql);
    }

    @test
    public void createsupertable() {
        string sql = "create table if not exists test.weather (ts timestamp,temperature float,humidity float,note binary(64)) tags(location nchar(64), groupid int)";
        tdenginerestful2(tdresturl, sql);
    }

    @test
    public void createtable() {
        string url = tdresturl + "/" + db;
        long ts = system.currenttimemillis();
        long thirtysec = 1000 * 30;
        weather weather = new weather(new timestamp(ts + (thirtysec)), 30 * random.nextfloat(), random.nextint(100));
        weather.setlocation(locations[random.nextint(locations.length)]);
        weather.setgroupid(1);
        weather.setnote("note-" + 1);
        //create table if not exists test.t#{groupid} using test.weather tags(#{location},#{groupid})
        stringbuilder sb = new stringbuilder();
        sb.append("create table if not exists test.t")
                .append(weather.getgroupid()).append(" ")
                .append("using test.weather tags(")
                .append("'").append(weather.getlocation()).append("'").append(",")
                .append(weather.getgroupid()).append(")");
        string s = sb.tostring();

        tdenginerestful2(url, sb.tostring());
    }

    @test
    public void inserttable() {
        string url = tdresturl + "/" + db;
        long ts = system.currenttimemillis();
        long thirtysec = 1000 * 30;

        for (int i = 0; i < 5; i++) {
            weather weather = new weather(new timestamp(ts + (thirtysec * i)), 30 * random.nextfloat(), random.nextint(100));
            weather.setlocation(locations[random.nextint(locations.length)]);
            weather.setgroupid(1);
            weather.setnote("note-" + 1);
            //insert into test.t#{groupid} (ts, temperature, humidity, note)values (#{ts}, ${temperature}, ${humidity}, #{note})
            stringbuilder sb = new stringbuilder();
            sb.append("insert into test.t").append(weather.getgroupid()).append(" ")
                    .append("(ts, temperature, humidity, note)").append(" ")
                    .append("values (").append(weather.getts().gettime()).append(",")
                    .append(weather.gettemperature()).append(",")
                    .append(weather.gethumidity()).append(",")
                    .append("'").append(weather.getnote()).append("'")
                    .append(")");
            string sql = sb.tostring();
            tdenginerestful2(url,sql);
        }

    }

    @test
    public void selectcount(){
        string url = tdresturl + "/" + db;
        string sql = "select count(*) from test.weather";
        tdenginerestfulinfo tdenginerestfulinfo = tdenginerestful2(url, sql);
        system.out.println(tdenginerestfulinfo);
    }

    @test
    public void selectone(){
        string url = tdresturl + "/" + db;
        string sql = "select * from test.weather where humidity = 13";
        tdenginerestfulinfo tdenginerestfulinfo = tdenginerestful2(url, sql);
        system.out.println(tdenginerestfulinfo);

    }

    @test
    public void selecttbname(){
        string url = tdresturl + "/" + db;
        string sql = "select tbname from test.weather";
        tdenginerestfulinfo tdenginerestfulinfo = tdenginerestful2(url, sql);
        system.out.println(tdenginerestfulinfo);

    }

    @test
    public void selectlastone(){
        string url = tdresturl + "/" + db;
        string sql = "select last_row(*), location, groupid from test.weather";
        tdenginerestfulinfo tdenginerestfulinfo = tdenginerestful2(url, sql);
        system.out.println(tdenginerestfulinfo);
    }

    @test
    public void selectavg(){
        string url = tdresturl + "/" + db;
        string sql = "select avg(temperature), avg(humidity) from test.weather interval(1m)";
        tdenginerestfulinfo tdenginerestfulinfo = tdenginerestful2(url, sql);
        system.out.println(tdenginerestfulinfo);

    }

    @test
    public void selectlimit(){
        string url = tdresturl + "/" + db;
        string sql = "select * from test.weather order by ts desc limit 3";
        tdenginerestfulinfo tdenginerestfulinfo = tdenginerestful2(url, sql);
        system.out.println(tdenginerestfulinfo);
    }



    public tdenginerestfulinfo tdenginerestful2(string url, string sql) {

        tdenginerestfulinfo tdenginerestfulinfo = null;

        // 获取默认配置 的okhttpclient 对象
        okhttpclient httpclient = new okhttpclient.builder().build();

        mediatype mediatype = okhttp3.mediatype.parse("application/json; charset=utf-8");
        requestbody requestbody = requestbody.create(mediatype, sql);

        request request = new request.builder()
                .url(url)
                .addheader("authorization", tdrestbasic)
                .post(requestbody)
                .build();

        response response = null;
        try {
            response = httpclient.newcall(request).execute();
            if (response.code() == httpstatus.ok.value()) {
                if (response.body() != null) {
                    string s = response.body().string();
                    tdenginerestfulinfo = jsonutil.tobean(s, tdenginerestfulinfo.class);
                }
            } else {
                logger.error("tdenginerestful 查询状态码异常,状态码是：" + response.code() + " ,异常消息是：" + response.message());
            }
        } catch (exception e) {
            logger.error("tdenginerestful 查询出现错误：" + e);
        } finally {
            if (response != null) {
                response.close();
            }
        }
        return tdenginerestfulinfo;

    }
}