全部產品
Search
文件中心

Lindorm:教程:通過Druid串連池串連並訪問時序引擎

更新時間:Sep 11, 2024

本文介紹通過Druid串連池串連並訪問時序引擎的方法。

背景資訊

使用Java開發訪問時序引擎的應用程式時,通常建議通過JDBC Driver串連Lindorm時序引擎。JDBC訪問邏輯的基礎是需要建立Connection對象,如果每次串連時序引擎都建立一個新的Connection對象,會帶來很大的開銷。為了降低開銷,可以使用串連池來執行串連的建立和管理。

前提條件

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

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

操作步驟

以開源串連池Alibaba Druid為例。

  1. 配置串連池Druid的參數。在Maven專案的src/main/resources目錄中建立druid.properties檔案,並在檔案中添加以下內容。

    # 驅動類名,無需替換
    driverClassName=com.aliyun.lindorm.table.client.Driver
    # url、username、password需要替換為業務實際的內容,可以在Lindorm控制台上擷取
    url=jdbc:lindorm:tsdb:url=http://ld-bp12pt80qr38p****-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242
    username=root
    password=root
    # 串連屬性,指定要串連的database,需根據實際情況將****替換為實際的內容
    connectionProperties=database=default
    
    # 初始化串連池即建立串連,建議保持不變
    init=true
    # 初始化串連池時建立串連的個數,可以根據實際情況調整
    initialSize=10
    # 串連池中允許的最小空閑串連數量,可以根據實際情況調整
    minIdle=10
    # 串連池中允許的最大串連數量,可以根據實際情況調整
    maxActive=20
    # 擷取串連最大等待時間,單位毫秒,建議保持不變
    maxWait=30000
    
    # 串連保活配置項,建議保持不變,否則可能出現串連斷開
    # 異常ConnectionDisconnectedException
    druid.keepAlive=true
    druid.keepAliveBetweenTimeMillis=30000
    minEvictableIdleTimeMillis=600000
    maxEvictableIdleTimeMillis=900000
    timeBetweenEvictionRunsMillis=5000
    
    # 串連驗證配置項,建議保持不變
    validationQuery=SELECT 1
    testWhileIdle=true
    testOnBorrow=false
    testOnReturn=false
    
    # PreparedStatement緩衝配置項,這裡配置為關閉緩衝,建議保持不變
    # 否則運行時可能會出現NoSuchStatement異常
    poolPreparedStatements=false
    maxOpenPreparedStatements=-1
    druid.maxPoolPreparedStatementPerConnectionSize=-1
    說明
    • url為時序引擎的JDBC串連地址,相關參數說明,請參見JDBC串連地址說明

    • 預設使用者名及密碼均為root

  2. 在Maven專案中添加Druid串連池的依賴。開啟用戶端,建立Project並在pom.xml中配置Maven依賴。範例程式碼如下:

    <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid</artifactId>
          <version>1.2.6</version>
        </dependency>
  3. 通過以下兩種方式安裝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的版本號碼根據需求填寫。

  4. 載入串連池Druid的參數並初始化串連池Druid。

    // 載入參數
    Properties properties = new Properties();
    InputStream inputStream = tsdb.class.getClassLoader().getResourceAsStream("druid.properties");
    properties.load(inputStream);
    // 初始化串連池
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
  5. 通過串連池Druid擷取JDBC的串連資訊並訪問Lindorm時序引擎。

    // 建立表
    try (Connection connection = dataSource.getConnection()) {
      try (Statement statement = connection.createStatement()) {
          String sql = "CREATE TABLE sensor (device_id VARCHAR TAG,region VARCHAR TAG,time TIMESTAMP,temperature DOUBLE,humidity DOUBLE,PRIMARY KEY(device_id))";
          int ret = statement.executeUpdate(sql);
          System.out.println(ret);
       }
    }
    // 插入資料
    try (Connection connection = dataSource.getConnection()) {
      try (Statement stmt = connection.createStatement()) {
         stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45)");
         stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:10',13.2,47)");
         stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:20',10.6,46)");
         stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn','2021-04-22 15:33:00',18.1,44)");
         stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn','2021-04-22 15:33:10',19.7,44)");
         stmt.executeBatch();
      }
    }
    // 查詢資料
    // 使用綁定參數的方式查詢資料
    // 強烈建議指定時間範圍減少資料掃描
    try (Connection connection = dataSource.getConnection()) {
        try (PreparedStatement pstmt = connection.prepareStatement("SELECT device_id, region,time,temperature,humidity FROM sensor 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);
             }
        }
      }
    }

完整樣本

單擊查看完整樣本

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class tsdb{
    public static void main(String[] args) throws Exception {
        // 載入參數
        Properties properties = new Properties();
        InputStream inputStream = tsdb.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(inputStream);
        // 初始化串連池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);


        // 建立表
        try (Connection connection = dataSource.getConnection()) {
            try (Statement statement = connection.createStatement()) {
                String sql = "CREATE TABLE sensor (device_id VARCHAR TAG,region VARCHAR TAG,time TIMESTAMP,temperature DOUBLE,humidity DOUBLE,PRIMARY KEY(device_id))";
                int ret = statement.executeUpdate(sql);
                System.out.println(ret);
            }
        }

        // 插入資料
        try (Connection connection = dataSource.getConnection()) {
            try (Statement stmt = connection.createStatement()) {
                stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45)");
                stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:10',13.2,47)");
                stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:20',10.6,46)");
                stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn','2021-04-22 15:33:00',18.1,44)");
                stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn','2021-04-22 15:33:10',19.7,44)");
                stmt.executeBatch();
            }
        }

        // 查詢資料
        // 使用綁定參數的方式查詢資料
        // 強烈建議指定時間範圍減少資料掃描
        try (Connection connection = dataSource.getConnection()) {
            try (PreparedStatement pstmt = connection.prepareStatement("SELECT device_id, region,time,temperature,humidity FROM sensor 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);


                //pstmt.setString(1, "2021-04-22 15:33:00");
               // pstmt.setString(2, "2021-04-22 15:33:20");
                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);
                    }
                }
            }
        }
    }
}

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

0
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
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