Java 连接使用 时序数据库 TimescaleDB

目标

本快速入门指南旨在让 Java 开发人员启动并运行 TimescaleDB 作为他们的数据库。在本教程中，您将学习如何：

• [将 Java 连接到 TimescaleDB]
• [创建关系表]
• [生成超表]
• [将一批行插入 TimescaleDB]
• [在 TimescaleDB 上执行查询]

先决条件

要完成本教程，您需要粗略了解结构化查询语言 (SQL)。本教程将引导您完成每个 SQL 命令，但如果您以前看过 SQL，它会很有帮助。

首先，安装 TimescaleDB。安装完成后，您可以继续摄取或创建示例数据并完成教程。

您还需要安装Java 开发工具包 (JDK) 和PostgreSQL Java 数据库连接 (JDBC) 驱动程序。所有代码均针对 Java 16 及更高版本提供。如果您使用的是较旧的 JDK 版本，请使用旧的编码技术。

将 Java 连接到 TimescaleDB

步骤 1：创建一个新的 Java 应用程序

为简单起见，本教程使用单个文件中的应用程序作为示例。您可以使用任何您喜欢的构建工具，包括gradle和maven.

创建一个单独的目录并导航到它。在其中，创建一个带有名称和扩展名Main.java以及以下内容的文本文件：

package com.timescale.java;

public class Main {

    public static void main(String... args) {
        System.out.println("Hello, World!");
    }
}

从当前目录中的命令行，尝试使用以下命令运行应用程序：

java Main.java

复制

您应该会看到Hello, World!控制台的行输出。如果出现错误，请参阅文档并检查 JDK 是否安装正确。您不必./com/timescale/java在源文件中创建类似于包路径的目录结构。您应该只在空文件夹中创建一个 java 文件并java Main.java从中运行。

第二步：导入 Postgres JDBC 驱动

要使用PostgreSQL，您需要导入适当的JDBC Driver. 如果您使用依赖管理器，请将PostgreSQL JDBC 驱动程序包含为依赖项。在这种情况下，下载JDBC Driver 的 jar artifact并将其放在文件旁边Main.java。

现在您可以将其JDBC Driver导入 Java 应用程序并显示可用驱动程序列表以供检查：

package com.timescale.java;

import java.sql.DriverManager;

public class Main {

    public static void main(String... args) {
        DriverManager.drivers().forEach(System.out::println);
    }
}

使用此命令运行以下所有示例：

java -cp *.jar Main.java

你最终应该得到类似org.postgresql.Driver@7f77e91b. 这意味着您已准备好从 Java 连接到 TimescaleDB。

第 3 步：编写数据库连接字符串

找到您的 TimescaleDB 凭据。您需要这些来组成一个连接字符串，供 JDBC 用来连接到您的 TimescaleDB 实例。

您将需要这些凭据：

• 主持人
• 港口
• 数据库名称
• 用户名
• 密码

接下来，使用以下格式编写连接字符串变量：

var connUrl = "jdbc:postgresql://host:port/dbname?user=username&password=password";

有关连接字符串形成的完整文档 可以在 PostgreSQL JDBC 驱动程序的官方文档中找到。

警告

上述组成连接字符串的方法仅用于测试或开发目的，出于生产目的，请务必制作敏感细节，如密码、主机名和端口号环境变量。

第 4 步：使用 PostgreSQL JDBC 驱动程序连接到 TimescaleDB 实例

更改代码以连接到数据库服务器并验证所有设置是否正确：

package com.timescale.java;

import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {

    public static void main(String... args) throws SQLException {
        var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
        var conn = DriverManager.getConnection(connUrl);
        System.out.println(conn.getClientInfo());
    }
}

复制

使用命令运行，java -cp *.jar Main.java您应该会看到以下输出：{ApplicationName=PostgreSQL JDBC Driver}.

恭喜，您已使用 Java 成功连接到 TimescaleDB。

创建关系表

第 1 步：制定 SQL 语句

首先，编写一个字符串，其中包含您将用于创建关系表的 SQL 状态。在此示例中，我们创建一个名为 的表，其中sensors包含列id和：type``location

CREATE TABLE sensors (
    id SERIAL PRIMARY KEY,
    type TEXT NOT NULL,
    location TEXT NOT NULL
);

第 2 步：执行 SQL 语句并提交更改

接下来，CREATE TABLE通过创建语句执行查询，执行查询Step 1并检查表是用SELECT语句创建的：

package com.timescale.java;

import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {

    public static void main(String... args) throws SQLException {
        var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
        var conn = DriverManager.getConnection(connUrl);

        var createSensorTableQuery = """
                CREATE TABLE sensors (
                    id SERIAL PRIMARY KEY,
                    type TEXT NOT NULL,
                    location TEXT NOT NULL
                )
                """;
        try (var stmt = conn.createStatement()) {
            stmt.execute(createSensorTableQuery);
        }

        var showAllTablesQuery = "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'";
        try (var stmt = conn.createStatement();
             var rs = stmt.executeQuery(showAllTablesQuery)) {
            System.out.println("Tables in the current database: ");
            while (rs.next()) {
                System.out.println(rs.getString("tablename"));
            }
        }
    }
}

恭喜，您已经使用 Java 在 TimescaleDB 中成功创建了一个关系表。

生成超表

在TimescaleDB中，与数据交互的主要点是超表，即跨所有空间和时间间隔的单个连续表的抽象，因此可以通过标准 SQL 查询它。

几乎所有与 TimescaleDB 的用户交互都使用超表。创建表和索引、更改表、插入数据和选择数据，都可以（并且应该）在超表上执行。

超表由具有列名和类型的标准模式定义，至少有一列指定时间值。

步骤 1：创建传感器数据表

首先，我们CREATE TABLE为我们的超表创建 SQL 语句。请注意超表如何具有强制时间列：

CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER REFERENCES sensors (id),
    value DOUBLE PRECISION
);

第 2 步：为传感器数据创建超表

接下来，您可以制定SELECT将我们在步骤 1 中创建的表转换为超表的语句。create_hypertable请注意，您必须按照文档的要求，指定要转换为超表的表名及其时间列名作为两个参数：

SELECT create_hypertable('sensor_data', 'time');

第 3 步：从您的 Java 代码执行前面的步骤

现在，您可以通过执行第 1 步中的语句，然后执行第 2 步中的语句并将更改提交到数据库来将它们组合在一起：

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', 'time')");
        }
    }
}

恭喜，您已经使用 Java 在 TimescaleDB 数据库中成功创建了一个超表。

将一批行插入 TimescaleDB

这是一个典型的模式，您可以使用它来将一些数据插入到表中。在下面的示例中，将传感器列表中的关系数据插入到名为 的关系表中sensors。

首先，打开与数据库的连接，然后使用准备好的语句制定INSERTSQL 语句，然后执行该语句：

final List<Sensor> sensors = List.of(
        new Sensor("temperature", "bedroom"),
        new Sensor("temperature", "living room"),
        new Sensor("temperature", "outside"),
        new Sensor("humidity", "kitchen"),
        new Sensor("humidity", "outside"));
for (final var sensor : sensors) {
    try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
        stmt.setString(1, sensor.type());
        stmt.setString(2, sensor.location());
        stmt.executeUpdate();
    }
}

您可以通过几种不同的方式将一批行插入 TimescaleDB。让我们看看使用批处理机制插入许多行是什么样子的。为简单起见，我们将使用 PostgreSQL 生成一些示例时间序列数据，以便插入到sensor_data超表中：

final var sensorDataCount = 100;
final var insertBatchSize = 10;
try (var stmt = conn.prepareStatement("""
        INSERT INTO sensor_data (time, sensor_id, value)
        VALUES (
            generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
            floor(random() * 4 + 1)::INTEGER,
            random()
        )
        """)) {
    for (int i = 0; i < sensorDataCount; i++) {
        stmt.addBatch();

        if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
            stmt.executeBatch();
        }
    }
}

下面是应用程序的完整列表，从创建表格到填写数据：

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', 'time')");
        }
    }

    private static void insertData(final Connection conn) throws SQLException {
        final List<Sensor> sensors = List.of(
                new Sensor("temperature", "bedroom"),
                new Sensor("temperature", "living room"),
                new Sensor("temperature", "outside"),
                new Sensor("humidity", "kitchen"),
                new Sensor("humidity", "outside"));
        for (final var sensor : sensors) {
            try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
                stmt.setString(1, sensor.type());
                stmt.setString(2, sensor.location());
                stmt.executeUpdate();
            }
        }

        final var sensorDataCount = 100;
        final var insertBatchSize = 10;
        try (var stmt = conn.prepareStatement("""
                INSERT INTO sensor_data (time, sensor_id, value)
                VALUES (
                    generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
                    floor(random() * 4 + 1)::INTEGER,
                    random()
                )
                """)) {
            for (int i = 0; i < sensorDataCount; i++) {
                stmt.addBatch();

                if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
                    stmt.executeBatch();
                }
            }
        }
    }

    private record Sensor(String type, String location) {
    }
}

小费

如果您从 CSV 文件插入数据，我们推荐使用timescale-parallel-copy 工具，这是一个命令行程序，用于并行化 PostgreSQL 的内置COPY功能，用于将数据批量插入 TimescaleDB。

恭喜，您已成功使用 Java 将数据插入 TimescaleDB。

在 TimescaleDB 上执行查询

步骤 1：定义 SQL 查询

首先，定义要在数据库上运行的 SQL 查询。下面的示例包含一个结合了时间序列和关系数据的查询。它返回具有特定类型和位置的传感器每 15 分钟间隔的平均值。

SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
FROM sensor_data
JOIN sensors ON sensors.id = sensor_data.sensor_id
WHERE sensors.type = ? AND sensors.location = ?
GROUP BY bucket
ORDER BY bucket DESC;

注意传感器类型和位置的占位符的使用。

第 2 步：执行查询

现在您可以使用准备好的语句执行查询，并读出a位于以下位置的所有 -type 传感器的结果集floor：

try (var stmt = conn.prepareStatement("""
        SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
        FROM sensor_data
        JOIN sensors ON sensors.id = sensor_data.sensor_id
        WHERE sensors.type = ? AND sensors.location = ?
        GROUP BY bucket
        ORDER BY bucket DESC
        """)) {
    stmt.setString(1, "temperature");
    stmt.setString(2, "living room");

    try (var rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
        }
    }
}

执行语句后，您应该在控制台中看到如下内容：

2021-05-12 23:30:00.0: 0,508649
2021-05-12 23:15:00.0: 0,477852
2021-05-12 23:00:00.0: 0,462298
2021-05-12 22:45:00.0: 0,457006
2021-05-12 22:30:00.0: 0,568744
...

使用 TimescaleDB 的完整 Java 片段

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=postgres";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
            executeQueries(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', 'time')");
        }
    }

    private static void insertData(final Connection conn) throws SQLException {
        final List<Sensor> sensors = List.of(
                new Sensor("temperature", "bedroom"),
                new Sensor("temperature", "living room"),
                new Sensor("temperature", "outside"),
                new Sensor("humidity", "kitchen"),
                new Sensor("humidity", "outside"));
        for (final var sensor : sensors) {
            try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
                stmt.setString(1, sensor.type());
                stmt.setString(2, sensor.location());
                stmt.executeUpdate();
            }
        }

        final var sensorDataCount = 100;
        final var insertBatchSize = 10;
        try (var stmt = conn.prepareStatement("""
                INSERT INTO sensor_data (time, sensor_id, value)
                VALUES (
                    generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
                    floor(random() * 4 + 1)::INTEGER,
                    random()
                )
                """)) {
            for (int i = 0; i < sensorDataCount; i++) {
                stmt.addBatch();

                if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
                    stmt.executeBatch();
                }
            }
        }
    }

    private static void executeQueries(final Connection conn) throws SQLException {
        try (var stmt = conn.prepareStatement("""
                SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
                FROM sensor_data
                JOIN sensors ON sensors.id = sensor_data.sensor_id
                WHERE sensors.type = ? AND sensors.location = ?
                GROUP BY bucket
                ORDER BY bucket DESC
                """)) {
            stmt.setString(1, "temperature");
            stmt.setString(2, "living room");

            try (var rs = stmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
                }
            }
        }
    }

    private record Sensor(String type, String location) {
    }
}

恭喜🎉，您已经使用 Java 和 PostgreSQL JDBC 在 TimescaleDB 上成功执行了一个查询！