Jooq代码生成：能否自动转换select查询的tstzrange列格式？

核心方案：自定义Binding + 代码生成配置 #

你观察的没错，常规Binding默认仅处理插入/更新的绑定参数，但通过自定义Binding并结合代码生成器配置，可以让Jooq在所有select查询中自动对tstzrange字段生成你需要的转换SQL。

步骤1：实现自定义Binding #

创建针对tstzrange类型的Binding，重写sql()方法定义查询时的SQL转换逻辑，同时处理结果集到Java类型的映射（比如转成Pair<OffsetDateTime, OffsetDateTime>或自定义Range类）。

示例代码：

public class TstzrangeToJsonArrayBinding implements Binding<Range<OffsetDateTime>, Pair<OffsetDateTime, OffsetDateTime>> {

    // 定义查询时的SQL转换逻辑
    @Override
    public void sql(BindingSQLContext<Pair<OffsetDateTime, OffsetDateTime>> ctx) throws SQLException {
        // 生成json_build_array(lower(字段), upper(字段))的SQL片段
        ctx.render().visit(DSL.field(
            "json_build_array(lower({0}), upper({0}))", 
            JSON.class, 
            ctx.render().peek()
        ));
    }

    // 读取结果集，将JSON数组转成Java对象
    @Override
    public void get(BindingGetResultSetContext<Pair<OffsetDateTime, OffsetDateTime>> ctx) throws SQLException {
        JSON json = ctx.resultSet().getJSON(ctx.index());
        if (json == null) {
            ctx.value(null);
            return;
        }
        // 用Jackson解析JSON数组，可替换为其他JSON库
        ObjectMapper mapper = new ObjectMapper();
        OffsetDateTime[] range = mapper.readValue(json.toString(), OffsetDateTime[].class);
        ctx.value(Pair.of(range[0], range[1]));
    }

    // 处理插入/更新时的参数绑定（可选，根据需求实现）
    @Override
    public void set(BindingSetStatementContext<Pair<OffsetDateTime, OffsetDateTime>> ctx) throws SQLException {
        Pair<OffsetDateTime, OffsetDateTime> value = ctx.value();
        if (value == null) {
            ctx.statement().setNull(ctx.index(), Types.OTHER);
            return;
        }
        // 将Java Pair转成Postgres tstzrange格式
        String rangeStr = String.format("[%s, %s)", value.getLeft().toString(), value.getRight().toString());
        ctx.statement().setObject(ctx.index(), rangeStr, Types.OTHER);
    }

    // 省略其他默认实现的Binding方法（如register、convert等）
}

步骤2：配置代码生成器关联自定义Binding #

在Jooq代码生成配置文件（如jooq-codegen.xml）中，指定tstzrange类型使用上述Binding：

<generator>
  <database>
    <name>org.jooq.meta.postgres.PostgresDatabase</name>
    <!-- 其他数据库连接配置 -->
    <customTypes>
      <customType>
        <name>java.util.Map.Entry&lt;java.time.OffsetDateTime, java.time.OffsetDateTime&gt;</name>
        <type>java.util.Map.Entry&lt;java.time.OffsetDateTime, java.time.OffsetDateTime&gt;</type>
        <binding>com.yourpackage.TstzrangeToJsonArrayBinding</binding>
      </customType>
    </customTypes>
    <forcedTypes>
      <forcedType>
        <name>java.util.Map.Entry&lt;java.time.OffsetDateTime, java.time.OffsetDateTime&gt;</name>
        <types>tstzrange</types>
      </forcedType>
    </forcedTypes>
  </database>
  <!-- 其他生成器配置 -->
</generator>

步骤3：验证效果 #

重新运行代码生成器后，所有tstzrange类型的字段（如VALID_DATE_RANGE）都会关联自定义Binding。执行select(table.VALID_DATE_RANGE) from table这类查询时，Jooq会自动生成：

select json_build_array(lower(valid_date_range), upper(valid_date_range)) as valid_date_range from whatever

同时查询结果会自动映射为你指定的Java类型。

替代方案：全局SQL转换拦截 #

若不想修改Binding，也可通过SQLTransformer全局拦截SQL并替换tstzrange字段的查询逻辑，但这种方式易出现误匹配，灵活性较低：

public class TstzrangeTransformer implements SQLTransformer {
    @Override
    public String transform(String sql) {
        // 正则匹配并替换tstzrange字段，需根据实际表结构调整正则规则
        return sql.replaceAll(
            "(?<=SELECT|,\\s)(\\w+\\.?)valid_date_range", 
            "json_build_array(lower($1valid_date_range), upper($1valid_date_range)) as valid_date_range"
        );
    }
}

注册到Jooq配置：

DSLContext dsl = DSL.using(configuration.set(SQLTransformer.class, new TstzrangeTransformer()));

内容的提问来源于stack exchange，提问作者HelloWorld