Lindorm:Tutorial: Use the JDBC driver for Lindorm to connect to and use LindormTSDB

Prerequisites

Before you begin, ensure that you have:

• JDK V1.8 or later installed

• Your client IP address added to the Lindorm instance whitelist — see Configure whitelists

• The LindormTSDB endpoint — see View endpoints

Step 1: Install the driver

Choose one of the following installation methods.

<dependency>
    <groupId>com.aliyun.lindorm</groupId>
    <artifactId>lindorm-all-client</artifactId>
    <version>2.2.1.3</version>
</dependency>

Step 2: Connect to LindormTSDB

The JDBC URL format for LindormTSDB is:

jdbc:lindorm:tsdb:url=http://<endpoint>:<port>

For the full list of URL parameters, see URL of the JDBC driver.

Step 3: Create a table, insert data, and run a query

The following example connects to LindormTSDB, creates a time series table in the default database, inserts rows in a batch, and queries data using a time range.

import java.sql.*;

class Test {
    public static void main(String[] args) {
        // Replace with your LindormTSDB endpoint and port.
        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()) {
                // Create a time series table in the default database.
                // device_id and region are TAG columns (dimensions for filtering).
                stmt.execute("CREATE TABLE sensor1 (device_id VARCHAR TAG, region VARCHAR TAG, time TIMESTAMP, temperature DOUBLE, humidity DOUBLE, PRIMARY KEY(device_id))");

                // Insert multiple rows in a single batch to reduce round trips.
                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();
            }

            // Always specify a time range to limit the amount of data scanned.
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "SELECT device_id, region, time, temperature, humidity FROM sensor1 WHERE time >= ? AND time <= ?")) {
                pstmt.setTimestamp(1, Timestamp.valueOf("2021-04-22 15:33:00"));
                pstmt.setTimestamp(2, Timestamp.valueOf("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);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

If the query succeeds, the output is similar to:

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

Data type mapping

LindormTSDB SQL types map to Java types as follows:

Troubleshooting

Connection fails

1. Verify that your client IP is in the instance whitelist.

2. Confirm the endpoint and port are correct in the JDBC URL.

3. Check that the Lindorm instance is running and the time series engine is enabled.

Query returns no data

1. Confirm the time range in your WHERE clause matches the timestamps of the inserted data.

2. Verify the table name and column names are correct.

executeBatch() is slow

Adjust the batch size based on your workload and data volume.

What's next

• URL of the JDBC driver — full list of JDBC URL parameters

• Supported API operations and methods — JDBC API coverage for LindormTSDB

• SQL reference — full LindormTSDB SQL syntax