本文介紹通過Lindorm JDBC Driver串連並訪問時序引擎的具體操作。
前提條件
操作步驟
通過以下兩種方式安裝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的版本號碼根據需求填寫。
訪問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(); } } } }
說明JDBC串連地址相關參數說明,請參見JDBC串連地址說明。
JDBC Driver訪問時序引擎時支援的API介面和對應的方法請參見支援的API介面和方法。
Lindorm時序引擎支援的SQL文法請參見SQL文法。
執行成功預計返回以下結果:
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