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_categoriesVALUES ('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 nameFROM age_categoriesWHERE 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_categoriesWHERE name IN ('Kid', 'Teenager', 'Senior'); |
… уступающий
|
1
2
3
|
int4range---------[2,90] |
А теперь давайте вернемся к извлечению всех категорий, которые находятся в этом диапазоне:
|
1
2
3
4
5
6
7
|
SELECT nameFROM age_categoriesWHERE 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--------KidTeenagerTweenAdultSenior |
Все это можно было бы реализовать с помощью значений, содержащихся в двух отдельных столбцах, но использование диапазонов намного более выразительно для арифметики диапазона.
Как использовать эти типы с 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 nameFROM age_categoriesWHERE 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 .
| Ссылка: | Лучший секрет PostgreSQL и как использовать его с jOOQ от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и AND JOOQ . |