PostgreSQL的最佳秘密，以及如何与jOOQ一起使用

PostgreSQL有很多秘密数据类型。 近年来，PostgreSQL的JSON和JSONB支持被大肆宣传为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不包含对这些高级数据类型的内置支持，但是它允许您将这些数据类型绑定到自己的自定义表示形式。

jOOλ的 org.jooq.lambda.tuple.Range类型可以很好地表示Java中PostgreSQL的范围类型，但是您也可以简单地将int[]或Map.Entry用于范围。 当我们使用jOOλ的Range类型时，其想法是能够使用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是要用Java编写的SQL，请安全键入。 为了能够编写以上内容，我们将需要实现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;
    }
}

…然后可以在Binding重新使用Converter ：

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)范围] [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')
));

OO

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手册 。

翻译自: https://www.javacodegeeks.com/2015/05/postgresqls-best-kept-secret-and-how-to-use-it-with-jooq.html