全部產品
Search
文件中心

Lindorm:教程:通過JDBC Driver串連並訪問Lindorm時序引擎

更新時間:Sep 11, 2024

本文介紹通過Lindorm JDBC Driver串連並訪問時序引擎的具體操作。

前提條件

  • 已安裝Java環境,要求安裝JDK 1.8及以上版本。

  • 已將用戶端IP地址添加至Lindorm白名單,具體操作請參見設定白名單

  • 已擷取Lindorm時序引擎的串連地址,具體操作請參見查看串連地址查看地址頁面

操作步驟

  1. 通過以下兩種方式安裝Lindorm JDBC Driver依賴。

    • 手動安裝。

      在本地自行下載JAR包整合JDBC Driver,下載連結為:Lindorm-all-client。選擇需要安裝的版本,以2.1.5為例,下載lindorm-all-client-2.1.5.jar包。

    • 通過Maven下載。

      如果在Maven專案中整合JDBC Driver,建立Project並在pom.xml中添加以下依賴配置,具體內容如下:

      <dependency>
          <groupId>com.aliyun.lindorm</groupId>  
          <artifactId>lindorm-all-client</artifactId>
          <version>2.2.1.3</version>
      </dependency>
      說明

      lindorm-all-client的版本號碼根據需求填寫。

  2. 訪問Lindorm時序引擎。完整的程式碼範例如下。

    import java.sql.*;
    
    class Test {
        public static void main(String[] args) {
            // 此處填寫Lindorm時序引擎JDBC串連地址
            String url = "jdbc:lindorm:tsdb:url=http://ld-bp12pt80qr38p****-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242";
            Connection conn = null;
    
            try {
                conn = DriverManager.getConnection(url);
                try (Statement stmt = conn.createStatement()) {
                    //建立時序資料表,預設訪問 default database
                    stmt.execute("CREATE TABLE sensor1 (device_id VARCHAR TAG,region VARCHAR TAG,time TIMESTAMP,temperature DOUBLE,humidity DOUBLE,PRIMARY KEY(device_id))");
    
                    //批量寫入資料
                    stmt.addBatch("INSERT INTO sensor1(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45)");
                    stmt.addBatch("INSERT INTO sensor1(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:10',13.2,47)");
                    stmt.addBatch("INSERT INTO sensor1(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:20',10.6,46)");
                    stmt.addBatch("INSERT INTO sensor1(device_id, region, time, temperature, humidity) values('F07A1261','south-cn','2021-04-22 15:33:00',18.1,44)");
                    stmt.addBatch("INSERT INTO sensor1(device_id, region, time, temperature, humidity) values('F07A1261','south-cn','2021-04-22 15:33:10',19.7,44)");
                    stmt.executeBatch();
                    stmt.clearBatch();
                }
    
                // 使用綁定參數的方式查詢資料
                // 強烈建議指定時間範圍減少資料掃描
                try (PreparedStatement pstmt = conn.prepareStatement("SELECT device_id, region,time,temperature,humidity FROM sensor1 WHERE time >= ? and time <= ?")) {
                    Timestamp startTime =Timestamp.valueOf("2021-04-22 15:33:00");
                    Timestamp endTime = Timestamp.valueOf("2021-04-22 15:33:20");
                    pstmt.setTimestamp(1, startTime);
                    pstmt.setTimestamp(2, endTime);
                    try (ResultSet rs = pstmt.executeQuery()) {
                        while (rs.next()) {
                            String device_id = rs.getString("device_id");
                            String region = rs.getString("region");
                            Timestamp time = rs.getTimestamp("time");
                            Double temperature = rs.getDouble("temperature");
                            Double humidity = rs.getDouble("humidity");
                            System.out.printf("%s %s %s %f %f\n", device_id, region, time, temperature, humidity);
                        }
                    }
                }
            } catch (SQLException e) {
                // 異常處理需要結合實際商務邏輯編寫
                e.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    說明

    執行成功預計返回以下結果:

    F07A1261 south-cn 2021-04-22 15:33:00.0 18.100000 44.000000
    F07A1261 south-cn 2021-04-22 15:33:10.0 19.700000 44.000000
    F07A1260 north-cn 2021-04-22 15:33:00.0 12.100000 45.000000
    F07A1260 north-cn 2021-04-22 15:33:10.0 13.200000 47.000000
    F07A1260 north-cn 2021-04-22 15:33:20.0 10.600000 46.000000