Статьи

Лучший секрет PostgreSQL и как его использовать с jOOQ

Ноябрь 4, 2017

PostgreSQL имеет много секретных типов данных. В последнее время поддержка JSON и JSONB в PostgreSQL была раскручена как секрет NoSQL on SQL (например, как рекламируется ToroDB), который позволяет вам использовать все преимущества обоих миров. Но есть много других полезных типов данных, среди которых тип диапазона.

Как работает тип диапазона?

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

CREATE TABLE age_categories (
  name VARCHAR(50),
  ages INT4RANGE
);

Теперь мы можем заполнить приведенную выше таблицу следующим образом:

INSERT INTO age_categories
VALUES ('Foetus',   int4range(-1, 0)),
       ('Newborn',  int4range(0, 1)),
       ('Infant',   int4range(1, 2)),
       ('Kid',      int4range(2, 12)),
       ('Teenager', int4range(12, 20)),
       ('Tween',    int4range(20, 30)),
       ('Adult',    int4range(30, 60)),
       ('Senior',   int4range(60, 90)),
       ('Ancient',  int4range(90, 999));

И запросить его, например, принимая мой возраст:

SELECT name
FROM age_categories
WHERE range_contains_elem(ages, 33);

… уступающий

name
-----
Adult

Есть много других полезных функций, связанных с вычислениями диапазона. Например, мы можем получить возрастной диапазон набора категорий. Давайте предположим, что мы хотим иметь возраст Kid, Teenager, Senior. Давайте запрос:

SELECT int4range(min(lower(ages)), max(upper(ages)))
FROM age_categories
WHERE name IN ('Kid', 'Teenager', 'Senior');

… уступающий

int4range
---------
[2,90]

А теперь давайте вернемся к извлечению всех категорий, которые находятся в этом диапазоне:

SELECT name
FROM age_categories
WHERE range_overlaps(ages, (
  SELECT int4range(min(lower(ages)), max(upper(ages)))
  FROM age_categories
  WHERE name IN ('Kid', 'Teenager', 'Senior')
));

… уступающий

name
--------
Kid
Teenager
Tween
Adult
Senior

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

Как использовать эти типы с JOOQ

jOOQ не включает встроенную поддержку для этих расширенных типов данных, но позволяет вам связать эти типы данных с вашим собственным, настраиваемым представлением.

Хорошим представлением типов диапазонов PostgreSQL в Java будет тип jOOλorg.jooq.lambda.tuple.Range , но вы также можете просто использовать int[]или Map.Entryдля диапазонов. Когда мы используем Rangeтип jOOλ , идея состоит в том, чтобы иметь возможность выполнять следующие операторы, используя jOOQ:

// Assuming this static import:
import static org.jooq.lambda.tuple.Tuple.*;

DSL.using(configuration)
   .insertInto(AGE_CATEGORIES)
   .columns(AGE_CATEGORIES.NAME, AGE_CATEGORIES.AGES)
   .values("Foetus",   range(-1, 0))
   .values("Newborn",  range(0, 1))
   .values("Infant",   range(1, 2))
   .values("Kid",      range(2, 12))
   .values("Teenager", range(12, 20))
   .values("Tween",    range(20, 30))
   .values("Adult",    range(30, 60))
   .values("Senior",   range(60, 90))
   .values("Ancient",  range(90, 999))
   .execute();

И запрос …

DSL.using(configuration)
   .select(AGE_CATEGORIES.NAME)
   .from(AGE_CATEGORIES)
   .where(rangeContainsElem(AGE_CATEGORIES.AGES, 33))
   .fetch();

DSL.using(configuration)
   .select(AGE_CATEGORIES.NAME)
   .where(rangeOverlaps(AGE_CATEGORIES.AGES,
      select(int4range(min(lower(AGE_CATEGORIES.AGES)),
                       max(upper(AGE_CATEGORIES.AGES))))
     .from(AGE_CATEGORIES)
     .where(AGE_CATEGORIES.NAME.in(
       "Kid", "Teenager", "Senior"
     ))
   ))
   .fetch();

Как всегда, идея с jOOQ заключается в том, что SQL, который вы хотите получить в качестве вывода, это SQL, который вы хотите написать на Java, введите безопасно. Чтобы можно было написать выше, нам нужно реализовать 1-2 недостающих фрагмента. Прежде всего, нам нужно создать привязку типа данных ( org.jooq.Binding), как описано в этом разделе руководства . Привязка может быть написана как таковая, используя следующее Converter:

public class Int4RangeConverter 
implements Converter<Object, Range<Integer>> {
    private static final Pattern PATTERN = 
        Pattern.compile("\\[(.*?),(.*?)\\)");

    @Override
    public Range<Integer> from(Object t) {
        if (t == null)
            return null;

        Matcher m = PATTERN.matcher("" + t);
        if (m.find())
            return Tuple.range(
                Integer.valueOf(m.group(1)), 
                Integer.valueOf(m.group(2)));

        throw new IllegalArgumentException(
            "Unsupported range : " + t);
    }

    @Override
    public Object to(Range<Integer> u) {
        return u == null
            ? null
            : "[" + u.v1 + "," + u.v2 + ")";
    }

    @Override
    public Class<Object> fromType() {
        return Object.class;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    @Override
    public Class<Range<Integer>> toType() {
        return (Class) Range.class;
    }
}

… и Converterзатем может быть повторно использован в Binding:

public class PostgresInt4RangeBinding 
implements Binding<Object, Range<Integer>> {

    @Override
    public Converter<Object, Range<Integer>> converter() {
        return new Int4RangeConverter();
    }

    @Override
    public void sql(BindingSQLContext<Range<Integer>> ctx) throws SQLException {
        ctx.render()
           .visit(DSL.val(ctx.convert(converter()).value()))
           .sql("::int4range");
    }

    // ...
}

Важным моментом в привязке является то, что каждая переменная связывания должна быть закодирована в строковом формате PostgreSQL для типов диапазонов (то есть [lower, upper)) и явно приведена к ?::int4range, вот и все.

Затем вы можете настроить генератор кода для использования этих типов, например, во всех вызываемых столбцах. [xxx]_RANGE

<customType>
    <name>com.example.PostgresInt4RangeBinding</name>
    <type>org.jooq.lambda.tuple.Range<Integer></type>
    <binding>com.example.PostgresInt4RangeBinding</binding>
</customType>
<forcedType>
    <name>com.example.PostgresInt4RangeBinding</name>
    <expression>.*?_RANGE</expression>
</forcedType>

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

  • rangeContainsElem()
  • rangeOverlaps()
  • int4range()
  • lower()
  • upper()

Они написаны быстро:

static <T extends Comparable<T>> Condition 
    rangeContainsElem(Field<Range<T>> f1, T e) {
    return DSL.condition("range_contains_elem({0}, {1})", f1, val(e));
}

static <T extends Comparable<T>> Condition 
    rangeOverlaps(Field<Range<T>> f1, Range<T> f2) {
    return DSL.condition("range_overlaps({0}, {1})", f1, val(f2, f1.getDataType()));
}

Вывод

Написание расширения для типов данных jOOQ занимает немного времени и усилий, но его действительно легко достичь и оно позволит вам писать очень мощные запросы безопасным способом. После того, как вы настроили все типы данных и привязки, ваш сгенерированный исходный код будет отражать фактический тип данных из вашей базы данных, и вы сможете писать мощные запросы безопасным способом прямо в Java. Давайте снова рассмотрим простой SQL и версию jOOQ:

SQL

SELECT name
FROM age_categories
WHERE range_overlaps(ages, (
  SELECT int4range(min(lower(ages)), max(upper(ages)))
  FROM age_categories
  WHERE name IN ('Kid', 'Teenager', 'Senior')
));

jOOQ

DSL.using(configuration)
   .select(AGE_CATEGORIES.NAME)
   .where(rangeOverlaps(AGE_CATEGORIES.AGES,
      select(int4range(min(lower(AGE_CATEGORIES.AGES)),
                       max(upper(AGE_CATEGORIES.AGES))))
     .from(AGE_CATEGORIES)
     .where(AGE_CATEGORIES.NAME.in(
       "Kid", "Teenager", "Senior"
     ))
   ))
   .fetch();

Более подробную информацию о привязках типов данных можно найти в руководстве по jOOQ .

34
Share: