阿里云首页 云原生多模数据库 Lindorm

通过JDBC Driver构建Lindorm时序引擎应用

本文介绍如何从管理控制台上获取连接串,并基于该连接串通过JDBC Driver构建一个简单的应用程序。

获取连接串

  1. 登录Lindorm控制台

  2. 实例列表单击目标实例ID,进入实例详情页面。

  3. 单击数据库连接 > 时序引擎进入时序引擎页面。

  4. 时序引擎页面查看私网地址或公网地址。

    说明 默认情况下,公网地址未开启,如果需要使用公网地址,请单击开通外网地址申请公网地址。
    TSQL CLient连接-1

构建JDBC连接字符串

连接字符串的示例如下:

jdbc:lindorm:tsdb:url=http://ld-xxxx-proxy-tsdb.lindorm.rds.aliyuncs.com:8242;
说明

JDBC Driver连接字符串中所支持的连接参数请参见 URL语法与连接参数

注意

服务端出于资源利用考虑,在连接空闲90s后,服务端会主动断开连接。如果此时复用该连接,则会出现com.aliyun.lindorm.client.shaded.org.apache.calcite.avatica.http.ConnectionDisconnectedException报错,重新建立连接即可解决。

访问示例

基于上文构建的连接字符串可以编写下述样例代码对Lindorm时序引擎进行建表、写数据以及简单查询的操作。关于如何在应用程序中安装并使用Lindorm JDBC,可以参见 在项目中集成并应用Lindorm JDBC Driver
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://ld-xxxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242";
        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 BIGINT,temperature DOUBLE,humidity DOUBLE,PRIMARY KEY(device_id))");

            //单条写入数据
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076780000,12.1,45)");
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076790000,13.2,47)");
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076800000,10.6,46)");
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn',1619076780000,18.1,44)");
            //stmt.execute("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn',1619076790000,19.7,44)");

            //批量写入数据
            stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076780000,12.1,45)");
            stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076790000,13.2,47)");
            stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1260','north-cn',1619076800000,10.6,46)");
            stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn',1619076780000,18.1,44)");
            stmt.addBatch("INSERT INTO sensor(device_id, region, time, temperature, humidity) values('F07A1261','south-cn',1619076790000,19.7,44)");
            stmt.executeBatch();
            stmt.clearBatch();

            //查询数据,强烈建议指定时间范围减少数据扫描
            ResultSet rs = stmt.executeQuery("select device_id, region,time,temperature,humidity from sensor where time >= 1619076780000 and time <= 1619076800000");
            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();
            }
        }
    }
}
说明
  1. 关于 Lindorm JDBC Driver 支持的API范围可参见 JDBC API Reference

  2. 关于Lindorm时序引擎支持的SQL语法可参见 时序引擎应用开发简介

首页 云原生多模数据库 Lindorm 时序引擎 快速入门 通过JDBC Driver构建Lindorm时序引擎应用