通过JDBC Driver连接并访问Lindorm时序引擎

本文介绍通过Lindorm JDBC Driver连接并访问时序引擎的具体操作。

前提条件

  • 已安装Java环境,要求安装JDK 1.8及以上版本。

  • 已将客户端IP地址添加至Lindorm白名单,具体操作请参见设置白名单

  • 已获取云原生多模数据库Lindorm时序引擎的连接地址,具体操作请参见查看连接地址查看地址页面

操作步骤

  1. 通过以下两种方式安装Lindorm JDBC Driver依赖。

    • 手动安装。

      在本地自行下载JAR包集成JDBC Driver,下载链接为:Lindorm-all-client。选择需要安装的版本,以2.1.2为例,下载lindorm-all-client-2.1.2.jar包。

    • 通过Maven下载。

      如果在Maven项目中集成JDBC Driver,创建Project并在pom.xml中添加以下依赖配置,具体内容如下:

      <dependency>
          <groupId>com.aliyun.lindorm</groupId>  
          <artifactId>lindorm-all-client</artifactId>
          <version>2.1.2</version>
      </dependency>
      说明

      lindorm-all-client的版本号根据需求填写。

  2. 访问Lindorm时序引擎。完整的代码示例如下。

    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();
    
                //创建时序数据表,默认访问 default database
                stmt.execute("CREATE TABLE sensor (device_id VARCHAR TAG,region VARCHAR TAG,time TIMESTAMP,temperature DOUBLE,humidity DOUBLE,PRIMARY KEY(device_id))");
    
                //单条写入数据
                //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)");
    
                //批量写入数据
                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();
    
                //查询数据,强烈建议指定时间范围减少数据扫描
                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) {
                // 异常处理需要结合实际业务逻辑编写
                e.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    说明
    • host:port:Lindorm时序引擎的连接地址和端口,例如:ld-bp17j28j2y7pm****-proxy-tsdb.lindorm.rds.aliyuncs.com:8242

    • jdbc:lindorm:tsdb:url=http://<host:port>:表示JDBC的连接地址,具体语法和连接参数说明请参见JDBC的连接地址说明

    • JDBC Driver访问时序引擎时支持的API接口和对应的方法请参见支持的API接口和方法

    • Lindorm时序引擎支持的SQL语法请参见SQL语法

阿里云首页 云原生多模数据库 Lindorm 相关技术圈