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.

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

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

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. ↩