时序数据库IoTDB中的JDBC连接

注意：导入的maven 要与自己安装的lotdb版本一致，否则容易出现问题！！！！
本文安装的lotdb版本为0.11.2
导入的maven为

		<dependency>
            <groupId>org.apache.iotdb</groupId>
            <artifactId>iotdb-jdbc</artifactId>
            <version>0.11.2</version>
        </dependency>

demo

package com.hadwinling.lot.demo;
import java.sql.*;
import org.apache.iotdb.jdbc.IoTDBSQLException;
/**
 * @description:
 * @author: hadwinling
 * @time: 2021/2/4 下午9:41
 */


public class JDBCExample {
    /**
     * Before executing a SQL statement with a Statement object, you need to create a Statement object using the createStatement() method of the Connection object.
     * After creating a Statement object, you can use its execute() method to execute a SQL statement
     * Finally, remember to close the 'statement' and 'connection' objects by using their close() method
     * For statements with query results, we can use the getResultSet() method of the Statement object to get the result set.
     */
    public static void main(String[] args) throws Exception{
        Connection connection = getConnection();
        if (connection == null) {
            System.out.println("get connection defeat");
            return;
        }
        Statement statement = connection.createStatement();
        //创建存储组，如果已经存在报错
        try {
            statement.execute("SET STORAGE GROUP TO root.test");
        }catch (IoTDBSQLException e){
            //如果存储组已经存在org.apache.iotdb.db.exception.metadata.StorageGroupAlreadySetException: root.demo has already been set to storage group
            System.out.println(e.getMessage());
        }


        //显示多少个存储组
        statement.execute("SHOW STORAGE GROUP");
        outputResult(statement.getResultSet());

        //创建时间序列
        try {
            statement.execute("CREATE TIMESERIES root.test.wf01.wt01.s0 WITH DATATYPE=INT32,ENCODING=RLE;");
        }catch (IoTDBSQLException e){
            System.out.println(e.getMessage());
        }
        //显示时间序列
        statement.execute("SHOW TIMESERIES root.test.wf01.wt01.s0");
        outputResult(statement.getResultSet());

        //显示设备
        statement.execute("SHOW DEVICES");
        outputResult(statement.getResultSet());
        //计数时间序列
        statement.execute("COUNT TIMESERIES root");
        outputResult(statement.getResultSet());
        //统计给定级别的节点数
        statement.execute("COUNT NODES root LEVEL=3");
        outputResult(statement.getResultSet());
        //列出这个级别下的节点，并统计count
        /**
         * column                   count
         * root.test.wf01.wt01      3
         * root.ln.wf01.wt01        2
         */
        statement.execute("COUNT TIMESERIES root GROUP BY LEVEL=3");
        outputResult(statement.getResultSet());


        //批量插入
        statement.addBatch("insert into root.test.wf01.wt01(timestamp,s0) values(1,1);");
        statement.addBatch("insert into root.test.wf01.wt01(timestamp,s0) values(1,1);");
        statement.addBatch("insert into root.test.wf01.wt01(timestamp,s0) values(2,15);");
        statement.addBatch("insert into root.test.wf01.wt01(timestamp,s0) values(2,17);");
        statement.addBatch("insert into root.test.wf01.wt01(timestamp,s0) values(4,12);");
        statement.executeBatch();
        statement.clearBatch();

        //完整查询
        /**
         * sql: select * from root.test.wf01.wt01
         * Time                               root.test.wf01.wt01.temperature    root.test.wf01.wt01.status         root.test.wf01.wt01.s0
         * 1                                  null                               null                               1
         * 2                                  null                               null                               17
         * 4                                  null                               null                               12
         * 1607410720627                      24.69                              true                               null
         * 1607413500875                      24.69                              false                              null
         * 1656410720627                      24.69                              true                               null
         */
        String sql = "select * from root.test.wf01.wt01";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("sql: " + sql);
        outputResult(resultSet);

        //精确查询
        /**
         * sql: select s0 from root.test.wf01.wt01 where time = 4;
         * Time                               root.test.wf01.wt01.s0
         * 4                                  12
         */
        sql = "select s0 from root.test.wf01.wt01 where time = 4;";
        resultSet= statement.executeQuery(sql);
        System.out.println("sql: " + sql);
        outputResult(resultSet);

        //时间范围查询
        sql = "select s0 from root.test.wf01.wt01 where time >= 2 and time < 5;";
        resultSet = statement.executeQuery(sql);
        System.out.println("sql: " + sql);
        outputResult(resultSet);

        //聚合查询
        sql = "select count(s0) from root.test.wf01.wt01;";
        resultSet = statement.executeQuery(sql);
        System.out.println("sql: " + sql);
        outputResult(resultSet);

        //删除时间序列
        statement.execute("delete timeseries root.test.wf01.wt01.s0");

        //关闭资源
        statement.close();
        connection.close();
    }

    public static Connection getConnection() {
        // JDBC driver name and database URL
        String driver = "org.apache.iotdb.jdbc.IoTDBDriver";
        String url = "jdbc:iotdb://127.0.0.1:6667/";

        // Database credentials
        String username = "root";
        String password = "root";

        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * This is an example of outputting the results in the ResultSet
     */
    private static void outputResult(ResultSet resultSet) throws SQLException {
        if (resultSet != null) {
            System.out.println("--------------------------");
            final ResultSetMetaData metaData = resultSet.getMetaData();
            final int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                System.out.print(metaData.getColumnLabel(i + 1) + " ");
            }
            System.out.println();
            while (resultSet.next()) {
                for (int i = 1; ; i++) {
                    System.out.print(resultSet.getString(i));
                    if (i < columnCount) {
                        System.out.print(", ");
                    } else {
                        System.out.println();
                        break;
                    }
                }
            }
            System.out.println("--------------------------\n");
        }
    }
}