PostgreSQL. Интервальный тип данных. Интеграция с Spring Data JPA

Общая информация

В своей практике мы можем сталкиваться с хранением и поиском информации задаваемой в интревальном формате. Например: срок действия акций, тарифы на доставку и прочее

Задача

Для рассмотрения особенностей реализации работы с интервальными типами данных реализуем хранение ставок транспортного налога для легковых автомобилей в 1 регионе. Ставки налогообложения для простоты возьмьем с сайта https://www.voaspb.ru/servisy/transportnyj-nalog

Для реализациий классической схемы в Spring JPA нам потребуется:

• Таблица для хранения ставок

• Entity

• Repository

В наших примерах будем рассматривать получение данных следущими способами

• SQL-запрос

• Specification

Реализация без интервалов

Таблица БД

Простая реализация таблицы для хранения тарифов в БД будет выглядеть приблизительно так:

 CREATE TABLE tax_rate_simple
(
     id          bigserial NOT NULL,
     lower_bound integer   NOT NULL,
     upper_bound integer,
     rate        integer   NOT NULL,
     CONSTRAINT tax_rate_simple_pk PRIMARY KEY (id),
     CONSTRAINT tax_rate_simple_upper_bound_unq UNIQUE (lower_bound),
     CONSTRAINT rate_chk CHECK (rate > 0),
     CONSTRAINT upper_bound_chk CHECK (upper_bound IS NULL OR upper_bound > lower_bound)
);

Entity

Соответственно Entity у нас достаточно примитивная, поскольку мы используем стандарные решения:

@Getter
@Setter
@Entity
@Table(name = "tax_rate_simple")
public class TaxRateSimple {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @NotNull
    @Column(name = "lower_bound", nullable = false)
    private Integer lowerBound;

    @Column(name = "upper_bound")
    private Integer upperBound;

    @NotNull
    @Positive
    @Column(name = "rate", nullable = false)
    private Integer rate;

}

SQL-запрос и Repository

Составим наш SQL-запрос для поиска ставки:

SELECT * FROM tax_rate_simple
WHERE lower_bound< :power
AND (upper_bound IS NULL OR upper_bound>= :power)

Добавим этот запрос для получения данных через repository :

public interface TaxRateSimpleRepository extends Repository<TaxRateSimple, Long>, JpaSpecificationExecutor<TaxRateSimple> {

    @Query(nativeQuery = true,
            value = "SELECT * FROM tax_rate_simple WHERE lower_bound< :power AND (upper_bound IS NULL OR upper_bound>= :power)")
    Optional<TaxRateSimple> findByPower(@Param("power") Integer power);
}

Как видим здесь все прозрачно и понятно.

Specification

Если вы до сих пор решаете вопрос с динамическими фильтрами путем игрищ со строками, то, возможно, вам стоит посмотреть в сторону Spring Data JPA Specifications. Это мощный инструмент для динамического построения запросов к БД. Основной идеей которого является просто создание правильного предиката или набора предикатов

Сформируем наш предикат, который будет идентичен sql-запросу:

public final class TaxRateSimplePredicateUtils {
    private TaxRateSimplePredicateUtils() {
    }

    public static Predicate getPowerPredicate(From<?, TaxRateSimple> from, CriteriaBuilder builder, int power) {
        return builder.and(
                builder.lessThan(from.get("lowerBound"), power),
                builder.or(
                        builder.isNull(from.get("upperBound")),
                        builder.greaterThanOrEqualTo(from.get("upperBound"), power)
                )
        );
    }
}

Получение данных через Specification будет выглядить следующим образом:

Specification<TaxRateSimple> specification = (root, criteriaQuery, criteriaBuilder) ->
                TaxRateSimplePredicateUtils.getPowerPredicate(root,criteriaBuilder, 150);
Optional<TaxRateSimple> result = repository.findOne(specification);

Реализация на интервальных типах данных

В этом случае все несколько сложнее по реализации и требует чуть большего кругозора и знаний от разработчика. Однако, почти все необходимые знания есть у современного java-разработчика с опытом от 5 лет.

Таблица БД

Таблица для хранения ставок будет реализовываться с использованием такого типа танных как int4range:

CREATE TABLE tax_rate_range
(
    id    bigserial NOT NULL,
    range int4range NOT NULL,
    rate  integer   NOT NULL,
    CONSTRAINT tax_rate_range_pk PRIMARY KEY (id),
    CONSTRAINT tax_rate_range_rate_chk CHECK (rate > 0),
    CONSTRAINT tax_rate_range_range_unq EXCLUDE USING GIST (range WITH &&)
);

Обратите внимание на ограничение tax_rate_range_range_unq. Оно позволяет нам валидировать интервалы на пересечение. Подобное ограничение при реализации в "варианте 1" было бы весьма хлопотно.

Entity

Для работы с нестандартными типами данных обычно используют готовую библиотеку от Vlad Mihalcea 'hypersistence-utils-hibernate' :

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-63</artifactId>
    <version>3.13.2</version>
</dependency>

Наш класс будет иметь вид:

@Accessors(chain=true)
@Getter
@Setter
@Entity
@Table(name = "tax_rate_range")
public class TaxRateRange {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotNull
    @Column(name = "range", columnDefinition = "int4range", nullable = false)
    private Range<Integer> range;

    @NotNull
    @Column(name = "rate", nullable = false)
    private Integer rate;

}

Как видим тут все выглядит волне традиционно

SQL-запрос и Repository

В отличии от предыдущей реализации в случае использования интервальных типов данных запрос будет выглядить гораздо более стройно:

SELECT * FROM tax_rate_range WHERE range @> :power;

Для репозитория практически ничего не изменится, поменяется только запрос:

public interface TaxRateRangeRepository extends JpaRepository<TaxRateRange, Long>, JpaSpecificationExecutor<TaxRateRange> {

    @Query(nativeQuery = true,
            value = "SELECT * FROM tax_rate_range WHERE range @> :power")
    Optional<TaxRateRange> findByPower(@Param("power") Integer power);
}

Specification

А вот тут, как раз начинается самая интересная часть. Как вы, наверное, догадались нам   нужно создать предикат, а стандартное решение jakarta.persistence.criteria.CriteriaBuilder ничего прямого к использованию не предлагает.

Что же нам делать? Все просто, нам нужно создать и зарегистрировать новую функцию.

Существует несколько вариантов объявления и регистрации функции, но я сделаю самый примитивный и простой вариант - через расширение диалекта:

public class PGDialect extends PostgresPlusDialect {

    public static final String INT4RANGE_CONTAIN = "int4range_contain";

    public PGDialect() {
        super();
    }

    @Override
    public void initializeFunctionRegistry(FunctionContributions functionContributions) {
        super.initializeFunctionRegistry(functionContributions);
        SqmFunctionRegistry functionRegistry = functionContributions.getFunctionRegistry();
        BasicTypeRegistry basicTypeRegistry = functionContributions.getTypeConfiguration().getBasicTypeRegistry();
        BasicType<Boolean> booleanType = basicTypeRegistry.resolve(StandardBasicTypes.BOOLEAN);

        functionRegistry.registerPattern(INT4RANGE_CONTAIN, "( ?1::int4range @> ?2::integer)", booleanType);


    }
}

Подключим наш диалект к Hibernate.

spring:
  jpa:
    database: postgresql
    open-in-view: false
    show-sql: true
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        dialect: org.cafejava.demo.range.dialect.PGDialect

Итак, полдела сделано. Будем теперь создавать предикат:

public final class TaxRateRangePredicateUtils {
    private TaxRateRangePredicateUtils() {
    }

    public static Predicate getPowerPredicate(From<?, TaxRateRange> from, CriteriaBuilder builder, int power) {
        return builder.isTrue(
                builder.function(INT4RANGE_CONTAIN, Boolean.class, from.get("range"), builder.literal(power))
        );
    }
}

А вот получение данных через specification у нас практически идентичное, что и вслучае без интервалов :

Specification<TaxRateRange> specification = (root, criteriaQuery, criteriaBuilder) ->
        TaxRateRangePredicateUtils.getPowerPredicate(root,criteriaBuilder,150);
Optional<TaxRateRange> result = repository.findOne(specification);

Заключение

Как видите подключение и использование интервальных типов данных не является чем-то сверхсложным и предоставляет дополнительный функционал разработчику, который был бы нереализуем или очень сложно реализуем через стандартные типы.

Ссылка на проект