sis/web/adapter-td-hb/src/main/java/com/gyee/adapter/init/RestInsertExample.java

package com.gyee.adapter.init;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
@author   谢生杰
@date   2022/8/6-16:25
*/
public class RestInsertExample {
    private static Connection getConnection() throws SQLException {
        String jdbcUrl = "jdbc:TAOS-RS://10.81.3.164:6041?user=root&password=taosdata";
        return DriverManager.getConnection(jdbcUrl);
    }

    private static Connection getPgConnection() throws SQLException {
        Connection connection = null;
        try {
            String url = "jdbc:postgresql://10.81.3.151:5432/wisdom";
            String user = "gdprod";
            String password = "gd123";
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return connection;
    }

    private static String getSQL(String stable,String database,String table,String name) {
//        INSERT INTO d1001 USING jnsx TAGS ("d1001","风速") VALUES (now, 0.0);
//        INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31) (1538548695000, 12.6, 218, 0.33)
//        d1002 VALUES (1538548696800, 12.3, 221, 0.31);
        StringBuilder sb = new StringBuilder();
        String replace = table.replace(".", "_");
        sb.append("INSERT INTO ").append(database).append(".").append(replace).append(" USING ").append(database).append(".").append(stable);
        sb.append(" TAGS (\"").append(table).append("\",\"");
        sb.append(name).append("\") VALUES (now, 0.0)");
        return String.valueOf(sb);
    }

    /**
     * 创建超级表
     * @param database   TD数据库名
     * @param stable    TD超级表
     * @throws SQLException
     */
    public static void createStable(String database,String stable) throws SQLException {
//     create stable IF NOT EXISTS wisdom.stag_double ( point_time timestamp, value_double double ) tags ( tag_id binary(192),tag_name nchar(500));
        StringBuilder sb = new StringBuilder();
        sb.append("create stable IF NOT EXISTS ");
        sb.append(database).append(".").append(stable);
        sb.append(" ( point_time timestamp, value_double double ) tags ( tag_id binary(192),tag_name nchar(500))");
        try (Connection conn = getConnection()) {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(String.valueOf(sb));
            }
        }
    }

    /**
     * @param pgtable   pg表名
     * @param realtimeid    筛选pg点
     * @param stable    TD超级表名
     * @param database  TD数据库名
     * @throws SQLException
     */
    public static void initPoint(String pgtable,String realtimeid,String stable,String database) throws SQLException {
        Connection pgConnection = getPgConnection();
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ").append(pgtable).append(" where realtimeid='").append(realtimeid).append("'");

        Statement statement = pgConnection.createStatement();
        ResultSet resultSet = statement.executeQuery(String.valueOf(sb));

        while(resultSet.next()){
            String code = resultSet.getString("code");
            String name = resultSet.getString("name");
            String sql = getSQL(stable, database, code,name);
            Connection connection = getConnection();
            Statement statement1 = connection.createStatement();
            statement1.executeUpdate(sql);
            connection.close();
            statement1.close();
        }
        pgConnection.close();
        statement.close();
        resultSet.close();
    }

    public static void main(String[] args) throws SQLException {
        createStable("wisdom","JSFW");
        initPoint("windpowerstationpointnew","JSFW","JSFW","wisdom");
    }
}