如何正确实现jOOQ Binding以渲染合法的PostgreSQL Interval语法

问题描述 #

在使用jOOQ为PostgreSQL的Interval类型绑定自定义类型时，渲染生成的SQL语句中，Interval值被多余的括号包裹，导致语句无法执行。例如生成的无效SQL：

insert into "XX"."YY" ([...], "my_interval") 
values ([...], interval ('0 years 0 mons 1 days 1 hours 0 mins 0.0 secs'))

调试时发现ctx.query()中的正确格式应为interval '0 years 0 mons 1 days 1 hours 0 mins 0.0 secs'，多余的括号是问题根源。

问题根源 #

问题出在IntervalBinding#sql方法的实现上，原代码使用DSL.val()生成值，该方法在部分渲染上下文会为字符串添加括号，导致PostgreSQL无法识别的语法：

@Override
public void sql(final BindingSQLContext<Interval> ctx) {
    ctx.render().sql("interval ").visit(DSL.val(ctx.convert(converter).value()));
}

解决方案 #

方案1：使用DSL.inline()生成无括号字面量（推荐，安全合规） #

DSL.inline()会生成不带括号的字面量，同时自动处理字符串转义，避免SQL注入风险：

@Override
public void sql(final BindingSQLContext<Interval> ctx) {
    ctx.render().sql("interval ").visit(DSL.inline(ctx.convert(converter).value()));
}

方案2：直接拼接字面量（仅当转换后的字符串安全时使用） #

如果确定转换后的Interval字符串不会存在SQL注入风险，也可以直接拼接成PostgreSQL支持的格式：

@Override
public void sql(final BindingSQLContext<Interval> ctx) {
    String intervalStr = ctx.convert(converter).value();
    if (intervalStr == null) {
        ctx.render().sql("null");
    } else {
        ctx.render().sql("interval '").sql(intervalStr).sql("'");
    }
}

额外优化：完善Converter双向转换逻辑 #

原converter()方法返回的是空实现，建议修改为复用已定义的IntervalConverter，保证数据库YearToSecond类型与自定义Interval类型的双向转换正常工作：

@Override
public Converter<YearToSecond, Interval> converter() {
    return new Converter<>() {
        @Override
        public Interval from(final YearToSecond databaseObject) {
            if (databaseObject == null) {
                return null;
            }
            return converter.from(databaseObject.toString());
        }

        @Override
        public YearToSecond to(final Interval userObject) {
            if (userObject == null) {
                return null;
            }
            return YearToSecond.valueOf(converter.to(userObject));
        }

        @Override
        public Class<YearToSecond> fromType() {
            return YearToSecond.class;
        }

        @Override
        public Class<Interval> toType() {
            return Interval.class;
        }
    };
}

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