Use the Druid connection pool to connect to LindormTSDB - Lindorm

This topic describes how to use the Druid connection pool to connect to LindormTSDB.

Background information

When you use Java to develop an application that stores data in LindormTSDB, we recommend that you configure the application to use the JDBC driver to connect to LindormTSDB. In this case, Connection objects are created to connect to LindormTSDB. If a new Connection object is created each time LindormTSDB is connected, a large number of resources are required. To reduce the resources required for connections, you can use a connection pool to create and manage connections.

Prerequisites

Java Development Kit (JDK) 1.8 or later is installed.
The IP address of your client is added to the whitelist of the Lindorm instance. For more information, see Configure a whitelist.

Procedure

The open source connection pool Alibaba Druid is used in this topic as an example. To download Alibaba Druid, visit GitHub.

Configure parameters for Druid. In the src/main/resources path of your Maven project, create the druid.properties file and add the following configurations to the file:

# Specify the name of the driver class. You can keep this configuration unchanged.
driverClassName=com.aliyun.lindorm.table.client.Driver
# You can replace the url, username, and password parameters to the actual values in your business. You can obtain the values of these parameters on the Lindorm console.
url=jdbc:lindorm:tsdb:url=http://ld-bp12pt80qr38p****-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242
username=root
password=root
# Replace **** with the database that you want to connect to.
connectionProperties=database=default

# Initialize the connection pool to create a connection. We recommend that you keep this configuration unchanged.
init=true
# Specify the number of connections that you want to create during initialization. You can configure this parameter based on your requirements.
initialSize=10
# Specify the minimum number of idle connections in the connection pool. You can configure this parameter based on your requirements.
minIdle=10
# Specify the maximum number of idle connections in the connection pool. You can configure this parameter based on your requirements.
maxActive=20
# Specify the maximum time that the client can wait to obtain a connection. Unit: ms. We recommend that you keep this configuration unchanged.
maxWait=30000

# Configure parameters related to connection keep-alive. We recommend that you keep these configurations unchanged. Otherwise, the connection may be unexpectedly disconnected.
# In this case, the ConnectionDisconnectedException exception is reported.
druid.keepAlive=true
druid.keepAliveBetweenTimeMillis=30000
minEvictableIdleTimeMillis=600000
maxEvictableIdleTimeMillis=900000
timeBetweenEvictionRunsMillis=5000

# Configure parameters that are required to verify connections. We recommend that you keep these configurations unchanged.
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false

# Configure parameters related to cache. In this example, cache is disabled. We recommend that you keep these configurations unchanged.
# Otherwise, the NoSuchStatement exception may be reported.
poolPreparedStatements=false
maxOpenPreparedStatements=-1
druid.maxPoolPreparedStatementPerConnectionSize=-1

Note

Set the url parameter to the JDBC URL that is used to connect to LindormTSDB. For information about the parameters in the URL, see URL of the JDBC driver.
The default values of the user and password parameters are root.

Add the dependency of Alibaba Druid to a Maven project. Start the client, create a Maven project, and add the following dependency to the pom.xml file: The following code provides an example on how to call Java API operations for LindormTable SQL to access Lindorm wide tables:
```
<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.6</version>
    </dependency>
```
You can use one of the following methods to install the JDBC driver for Lindorm:
- Manually install the JDBC driver
  Download the Lindorm-all-client JAR package to your client and install the JDBC driver. You can select the JDBC driver version that you want to install. For example, if you want to install the JDBC driver 2.1.5, download the lindorm-all-client-2.1.5.jar package.
- Use Maven to download the JDBC driver
  To integrate the JDBC driver into a Maven project, create a Maven project and add the following dependencies to the pom.xml file:
```
<dependency>
    <groupId>com.aliyun.lindorm</groupId>  
    <artifactId>lindorm-all-client</artifactId>
    <version>2.2.1.3</version>
</dependency>
```
  Note
  Specify the version of lindorm-all-client based on your business requirements.

Load the parameter configurations of Druid and initialize the connection pool.

// Load parameter configurations.
Properties properties = new Properties();
InputStream inputStream = tsdb.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(inputStream);
// Initialize the connection pool.
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

Use Druid to obtain connection information from JDBC and connect to LindormTSDB.

// 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);
   }
}
// Insert data to the table.
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();
  }
}
// Query data in the table.
// Query data by specifying parameters.
// Query data. We recommend that you specify a time range to reduce the amount of data that is 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);
         }
    }
  }
}

Sample code

Click here to view the complete sample

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 parameter configurations.
        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);
            }
        }

        // Insert data to the table.
        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();
            }
        }

        // Query data in the table.
        // Query data by specifying parameters.
        // Query data. We recommend that you specify a time range to reduce the amount of data that is 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);


                //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);
                    }
                }
            }
        }
    }
}

If the following sample response is returned, the execution is successful:

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