All Products
Search
Document Center

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

Last Updated:Dec 22, 2023

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) 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.

  • The endpoint of LindormTSDB is obtained. For more information, see View endpoints. View endpoints

Procedure

  1. 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. 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.1.9</version>
      </dependency>
      Note

      Specify the version of lindorm-all-client based on your business requirements.

  2. 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 one 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 LindormTSDB, see SQL syntax.