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 注解的实体类,创建超级表。

1
2
3
4
5
6
7
8
9
10
11
12
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();
}

扫描并创建超级表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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 注解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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. timescaledb with spring data jpa. Andy Zhang. 2023-12-29 [2023-12-29]. (原始内容存档于2023-12-29)

TimescaleDB + Spring Data JPA 整合
https://blog.zhanganzhi.com/zh-CN/2023/12/a11bfb4ae60b/
作者
Andy Zhang
发布于
2023年12月29日
许可协议