Use the Druid connection pool to connect to LindormTSDB - Lindorm: Central de Documentação do Alibaba Cloud

Use o Alibaba Druid como pool de conexões JDBC para gerencie conexões com o LindormTSDB de forma eficiente. O pool pré-crie um conjunto de conexões e as reutiliza nas operações do banco de dados, em vez de abrir e fechar uma conexão a cada gravação ou consulta. A chamada a connection.close() devolve a conexão ao pool, sem encerrá-la definitivamente.

Essa abordagem é particularmente útil para cargas de trabalho de IoT e séries temporais, em que os aplicativos gravam leituras de sensores com alta frequência e operações de curta duração. Reutilizar conexões reduz significativamente a latência e o consumo de recursos.

Pré-requisitos

Antes de começar, verifique se você tem:

Java Development Kit (JDK) 1.8 ou posterior instale
Endereço IP do cliente adicionado à lista de permissões da sua instância Lindorm. Consulte Configure a whitelist

Adicione dependências Maven

Adicione o pool de conexões Druid e o driver JDBC do Lindorm ao arquivo pom.xml:

<!-- Alibaba Druid connection pool -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>

<!-- Lindorm JDBC driver -->
<dependency>
    <groupId>com.aliyun.lindorm</groupId>
    <artifactId>lindorm-all-client</artifactId>
    <version>2.2.1.3</version>
</dependency>

Para instale o driver JDBC manualmente, baixe o pacote JAR do lindorm-all-client e adicione-o ao classpath do projeto. Por exemplo, para usar a versão 2.1.5, baixe o arquivo lindorm-all-client-2.1.5.jar .

Configure o Druid

No diretório src/main/resources do projeto Maven, crie um arquivo chamado druid.properties.

Parâmetros obrigatórios

Estes parâmetros conectam o Druid à instância LindormTSDB. Substitua os valores de exemplo pelos detalhes reais de conexão obtidos no console do Lindorm.

# Driver class — keep this value unchanged
driverClassName=com.aliyun.lindorm.table.client.Driver

# JDBC URL — get this from the Lindorm console
url=jdbc:lindorm:tsdb:url=http://ld-bp12pt80qr38p****-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242
username=root
password=root

# Database to connect to
connectionProperties=database=default

Para obter detalhes sobre o formato da URL JDBC e seus parâmetros, consulte URL of the JDBC driver . O nome de usuário e a senha padrão são ambos root .

Tamanho do pool e tempo limite

# Initialize the pool on startup
init=true

# Number of connections to create at startup
initialSize=10

# Minimum number of idle connections to maintain
minIdle=10

# Maximum number of active connections
maxActive=20

# Maximum time to wait for a connection, in milliseconds
maxWait=30000

Ajuste initialSize, minIdle e maxActive conforme a carga de trabalho. Mantenha maxWait em 30000 ms.

Configurações de keep-alive

Essas configurações impedem que o servidor descarte silenciosamente conexões ociosas. Mantenha todos os valores inalterados para evitar exceções ConnectionDisconnectedException.

druid.keepAlive=true
druid.keepAliveBetweenTimeMillis=30000
minEvictableIdleTimeMillis=600000
maxEvictableIdleTimeMillis=900000
timeBetweenEvictionRunsMillis=5000

Validação de conexão

validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false

Cache de instruções

Desative o cache de instruções preparadas. Ativá-lo causa exceções NoSuchStatement no LindormTSDB.

poolPreparedStatements=false
maxOpenPreparedStatements=-1
druid.maxPoolPreparedStatementPerConnectionSize=-1

Conecte-se e execute consultas

Inicialize o pool de conexões

Carregue o arquivo druid.properties e crie o DataSource:

// Load Druid configuration
Properties properties = new Properties();
InputStream inputStream = tsdb.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(inputStream);

// Initialize the connection pool
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

Crie uma tabela

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);
    }
} // connection.close() returns the connection to the pool

Insira dados

Use addBatch para enviar várias linhas em uma única ida e volta:

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();
    }
} // connection.close() returns the connection to the pool

Consulte dados

Sempre especifique um intervalo de tempo para limitar a quantidade de dados verificados:

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);
            }
        }
    }
} // connection.close() returns the connection to the pool

Código de exemplo completo

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 {
        // Load Druid configuration
        Properties properties = new Properties();
        InputStream inputStream = tsdb.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(inputStream);

        // Initialize the connection pool
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        // Create a table
        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);
            }
        } // connection.close() returns the connection to the pool

        // Insert data using batch for efficiency
        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();
            }
        } // connection.close() returns the connection to the pool

        // Query data — specify a time range to limit data scanned
        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);
                    }
                }
            }
        } // connection.close() returns the connection to the pool
    }
}

Saída esperada:

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