TimescaleDB + Spring Data JPA 整合

本文介绍了如何在 Spring Data JPA 中整合 TimescaleDB。

基础要求

您需要配置好 TimescaleDB 数据库，以及 Spring Boot 3 + Spring Data JPA。

关键问题

Spring Data JPA 会自动创建数据库表，但是无法自动创建 TimescaleDB 需要的超级表，因此需要手动创建。我们希望以编程方式让应用可以自动创建超级表。

解决方案

该方案的原始版本是我在 Stack Overflow 的回答：timescaledb with spring data jpa^[1]。

@TimescaleTable 注解

我们可以使用 @TimescaleTable 注解来标记 Timescale 表，然后在应用启动时，找到标记了 @TimescaleTable 注解的实体类，创建超级表。

import java.lang.annotation.Target;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface TimescaleTable {
    String tableName();

    String timeColumnName();
}

扫描并创建超级表

import java.util.Set;

import lombok.RequiredArgsConstructor;
import jakarta.annotation.PostConstruct;
import jakarta.persistence.EntityManager;
import jakarta.persistence.metamodel.EntityType;
import org.springframework.context.annotation.Configuration;

@Configuration
@RequiredArgsConstructor
public class TimescaleTableInitializer {
    private final EntityManager entityManager;

    private void createHypertable(String tableName, String timeColumnName) {
        entityManager
                .createNativeQuery(String.format(
                        "SELECT create_hypertable('%s','%s', if_not_exists => TRUE);",
                        tableName,
                        timeColumnName
                ))
                .getResultList();
    }

    @PostConstruct
    public void init() {
        // get all entities
        Set<EntityType<?>> entities = entityManager.getMetamodel().getEntities();

        // for each entity
        for (EntityType<?> entity : entities) {
            // get entity class
            Class<?> javaType = entity.getJavaType();

            // check of TimescaleTable annotation
            if (javaType.isAnnotationPresent(TimescaleTable.class)) {
                // get metadata from annotation
                TimescaleTable annotation = javaType.getAnnotation(TimescaleTable.class);
                String tableName = annotation.tableName();
                String timeColumnName = annotation.timeColumnName();

                // create hypertable
                createHypertable(tableName, timeColumnName);
            }
        }
    }
}

使用 @TimescaleTable 注解

import java.io.Serializable;
import java.time.LocalDateTime;

import lombok.*;
import jakarta.persistence.*;

import ***.app.config.timescaledb.TimescaleTable;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = SensorData.TABLE_NAME)
@TimescaleTable(tableName = SensorData.TABLE_NAME, timeColumnName = SensorData.TIME_COLUMN_NAME)
@IdClass(SensorData.SensorDataID.class)
public class SensorData {
    public static final String TABLE_NAME = "sensor_data";
    public static final String TIME_COLUMN_NAME = "time";

    @Data
    public static class SensorDataID implements Serializable {
        private Integer id;
        private LocalDateTime time;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Id
    @Column(name = TIME_COLUMN_NAME, nullable = false)
    private LocalDateTime time;

    @Column(nullable = false)
    private String sensor;

    @Column(nullable = false)
    private String data;
}

参考文献

1. Andy Zhang. timescaledb with spring data jpa. 2023-12-29. Archived on 2023-12-29. Retrieved 2023-12-29. ↩