Общая информация
В своей практике мы можем сталкиваться с хранением и поиском информации задаваемой в интревальном формате. Например: срок действия акций, тарифы на доставку и прочее
Задача
Для рассмотрения особенностей реализации работы с интервальными типами данных реализуем хранение ставок транспортного налога для легковых автомобилей в 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);Заключение
Как видите подключение и использование интервальных типов данных не является чем-то сверхсложным и предоставляет дополнительный функционал разработчику, который был бы нереализуем или очень сложно реализуем через стандартные типы.
