在postgres中为Jooq绑定“timestamp with time zone”类型

Jooq currently does not support JSR 310 types and support will not come until v3.8.

Jooq目前不支持JSR 310类型，直到v3.8才会支持。

Using simple converters generally works, except for certain types, such as postgres' TIMESTAMP WITH TIME ZONE, which requires a custom binding. So I have tried to write one but the generated XxxRecord classes still use a Timestamp data type for the TIMESTAMP WITH TIME ZONE fields in my DB.

使用简单的转换器通常可以工作，除了某些类型，例如postgres'TIMESTAMP WITH TIME ZONE，它需要自定义绑定。所以我试着写一个，但生成的XxxRecord类仍然在我的数据库中使用TIMESTAMP WITH TIME ZONE字段的Timestamp数据类型。

What do I need to change in my code below to see postgres' TIMESTAMP WITH TIME ZONE as an Instantin jooq's generated classes?

我需要在下面的代码中更改postgres的TIMESTAMP WITH TIME ZONE作为Instantin jooq生成的类吗？

Converter

public class TimestampConverter implements Converter<Timestamp, Instant> {
  @Override public Instant from(Timestamp ts) {
    return ts == null ? null : ts.toInstant();
  }
  @Override public Timestamp to(Instant instant) {
    return instant == null ? null : Timestamp.from(instant);
  }
  @Override public Class<Timestamp> fromType() { return Timestamp.class; }
  @Override public Class<Instant> toType() { return Instant.class; }
}

Custom binding

public class TimestampBinding implements Binding<Timestamp, Instant> {

  private static final Converter<Timestamp, Instant> converter = new TimestampConverter();

  private final DefaultBinding<Timestamp, Instant> delegate = 
                                                       new DefaultBinding<> (converter());

  @Override public Converter<Timestamp, Instant> converter() { return converter; }

  @Override public void sql(BindingSQLContext<Instant> ctx) throws SQLException {
    delegate.sql(ctx);
  }

  //etc. same for all other overriden methods.
}

pom.xml (extracts)

<customType>
  <name>java.time.Instant</name>
  <type>java.time.Instant</type>
  <binding>xxx.TimestampBinding</binding>
</customType>

...

<forcedType>
  <name>java.time.Instant</name>
  <types>timestamp with time zone</types>
</forcedType>

1 个解决方案

#1

One way would be to escape the spaces in <types> with backslashes, as follows:

一种方法是使用反斜杠转义中的空格，如下所示：

<types>timestamp\ with\ time\ zone</types>

You can't just have regular spaces in <types> because by default, org.jooq.util.AbstractDatabase will parse regular expressions in COMMENTS mode which makes the created Pattern object ignore whitespace in your regex. You could also do something like <types>timestamp.*zone</types>, or specify your own <regexFlags>.

您不能只在中有常规空格，因为默认情况下，org.jooq.util.AbstractDatabase将在COMMENTS模式下解析正则表达式，这使得创建的Pattern对象忽略正则表达式中的空格。您还可以执行类似 timestamp。* zone 的操作，或指定您自己的。

The following is the full Maven jooq-codegen-maven plugin tag that works for me. I also found the <binding> to be unnecessary.

以下是适用于我的完整Maven jooq-codegen-maven插件标记。我还发现是不必要的。

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>3.7.0</version>

    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>

    <configuration>

        <jdbc>
            <driver>org.postgresql.Driver</driver>
            <url>jdbc:postgresql:postgres</url>
            <user>postgres</user>
            <password>mypass</password>
        </jdbc>

        <generator>
            <database>
                <customTypes>
                    <customType>
                        <name>Instant</name>
                        <type>java.time.Instant</type>
                        <converter>xxx.TimestampConverter</converter>
                    </customType>
                </customTypes>

                <forcedTypes>
                    <forcedType>
                        <name>Instant</name>
                        <types>timestamp\ with\ time\ zone</types>
                    </forcedType>
                </forcedTypes>

                <name>org.jooq.util.postgres.PostgresDatabase</name>
                <includes>author</includes>
                <excludes/>
                <inputSchema>public</inputSchema>
            </database>
            <target>
                <packageName>xxx.table</packageName>
                <directory>target/generated-sources/jooq</directory>
            </target>
        </generator>
    </configuration>
</plugin>

#1