Статьи

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

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

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

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

1
2
3
4
CREATE TABLE age_categories (
  name VARCHAR(50),
  ages INT4RANGE
);

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

01
02
03
04
05
06
07
08
09
10
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));

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

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

… уступающий

1
2
3
name
-----
Adult

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

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

… уступающий

1
2
3
int4range
---------
[2,90]

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

1
2
3
4
5
6
7
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')
));

… уступающий

1
2
3
4
5
6
7
name
--------
Kid
Teenager
Tween
Adult
Senior

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

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

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

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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
// 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();

И запрос …

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
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 :

01
02
03
04
05
06
07
08
09
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
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 :

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
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

1
2
3
4
5
6
7
8
9
<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()

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

1
2
3
4
5
6
7
8
9
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

1
2
3
4
5
6
7
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

01
02
03
04
05
06
07
08
09
10
11
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 .