How do I use the JDBC driver for Lindorm to connect to and use LindormTSDB - Lindorm

This topic describes how to use the Java Database Connectivity (JDBC) driver for Lindorm to connect to and use the Lindorm time series engine (LindormTSDB).

Prerequisites

Java Development Kit (JDK) V1.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 whitelists.
The endpoint of LindormTSDB is obtained. For more information, see View endpoints.

Procedure

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 dependency to the pom.xml file:
```
<dependency>
    <groupId>com.aliyun.lindorm</groupId>  
    <artifactId>lindorm-all-client</artifactId>
    <version>2.2.0</version>
</dependency>
```
  Note
  Specify the version of lindorm-all-client based on your business requirements.

Access LindormTSDB. The following code block provides an example on how to access LindormTSDB:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

class Test {
    public static void main(String[] args) {

        String url = "jdbc:lindorm:tsdb:url=http://<host:port>";
        Connection conn = null;

        try {
            conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();

            // Create a time series table. By default, the time series table is created in the database named default.
            stmt.execute("CREATE TABLE sensor (device_id VARCHAR TAG,region VARCHAR TAG,time TIMESTAMP,temperature DOUBLE,humidity DOUBLE,PRIMARY KEY(device_id))");

            // Insert a single row.
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45)");
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:10',13.2,47)");
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn','2021-04-22 15:33:20',10.6,46)");
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn','2021-04-22 15:33:00',18.1,44)");
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn','2021-04-22 15:33:10',19.7,44)");

            // Insert multiple rows in a batch.
            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();
            stmt.clearBatch();

            // Query data. We recommend that you specify a time range to reduce the amount of data that is scanned.
            ResultSet rs = stmt.executeQuery("select device_id, region,time,temperature,humidity from sensor where time >= '2021-04-22 15:33:00' and time <= '2021-04-22 15:33:20'");
            while (rs.next()) {
                String device_id = rs.getString("device_id");
                String region = rs.getString("region");
                Long time = rs.getLong("time");
                Double temperature = rs.getDouble("temperature");
                Long humidity = rs.getLong("humidity");
                System.out.printf("%s %s %d %f %d\n", device_id, region, time, temperature, humidity);
            }
        } catch (SQLException e) {
            // Handle exceptions based on the business logic of your application.
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Note

For more information about the parameters that you can configure for the JDBC URL, see JDBC URLs.
For more information about the API operations and methods that are supported when you use the JDBC driver to access LindormTSDB, see Supported API operations and methods.
For more information about the SQL syntax supported by the LindormTSDB, see SQL syntax.