TimescaleDB + Spring Data JPA Integration

This article introduces how to integrate TimescaleDB in Spring Data JPA.

Basic Requirements

You need to configure the TimescaleDB database, Spring Boot 3, and Spring Data JPA.

Key Issue

Spring Data JPA will automatically create database tables, but it cannot automatically create the hypertable required by TimescaleDB, so it needs to be created manually. We want to programmatically allow the application to automatically create hypertables.

Solution

The original version of this solution is my answer on Stack Overflow: timescaledb with spring data jpa[1].

@TimescaleTable Annotation

We can use the @TimescaleTable annotation to mark the Timescale table, and then find the entity class marked with the @TimescaleTable annotation when the application starts, and create the hypertable.

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();
}

Scan and Create Hypertables

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);
}
}
}
}

Use @TimescaleTable Annotation

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;
}

References

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

TimescaleDB + Spring Data JPA Integration
https://blog.zhanganzhi.com/en/2023/12/a11bfb4ae60b/
Author
Andy Zhang
Posted on
December 29, 2023
Licensed under