jOOQ does not use custom data binding

Question

I am trying to write a custom data type binding for PGInterval and Duration to use jOOQ together with TimescaleDB. Sadly jOOQ does not use it when generating functions for the database routines.

Here's my binding class:

import org.jooq.*
import org.jooq.conf.ParamType
import org.jooq.impl.DSL
import org.postgresql.util.PGInterval
import java.sql.SQLFeatureNotSupportedException
import java.sql.Types
import java.time.Duration
import java.util.*

class PostgresIntervalDurationBinding: Binding<Any, Duration> {
    override fun converter(): Converter<Any, Duration> {
        return object : Converter<Any, Duration> {
            override fun from(t: Any?): Duration {
                return if (t == null) Duration.ZERO else Duration.ofSeconds(pgIntervalToSeconds(t as PGInterval))
            }

            override fun to(u: Duration?): Any? {
                return if (u == null || u === Duration.ZERO) null else PGInterval().seconds = u.seconds.toDouble()
            }

            override fun fromType(): Class<Any> {
                return Any::class.java
            }

            override fun toType(): Class<Duration> {
                return Duration::class.java
            }
        }

    }

    override fun sql(ctx: BindingSQLContext<Duration>?) {
        if (ctx?.render()?.paramType() == ParamType.INLINED)
            ctx.render()?.visit(DSL.inline(ctx.convert(converter()).value()))?.sql("::interval")
        else
            ctx?.render()?.sql("?::interval")
    }

    override fun register(ctx: BindingRegisterContext<Duration>?) {
        ctx?.statement()?.registerOutParameter(ctx.index(), Types.VARCHAR)
    }

    override fun set(ctx: BindingSetStatementContext<Duration>?) {
        ctx?.statement()?.setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null))
    }

    override fun get(ctx: BindingGetResultSetContext<Duration>?) {
        ctx?.convert(converter())?.value(ctx.resultSet().getString(ctx.index()))
    }

    override fun get(ctx: BindingGetStatementContext<Duration>?) {
        ctx?.convert(converter())?.value(ctx.statement().getString(ctx.index()))
    }

    override fun set(ctx: BindingSetSQLOutputContext<Duration>?) {
        throw SQLFeatureNotSupportedException()
    }

    override fun get(ctx: BindingGetSQLInputContext<Duration>?) {
        throw SQLFeatureNotSupportedException()
    }

    companion object {
        fun pgIntervalToSeconds(t: PGInterval): Long {
            var seconds = 0L
            with(t){
                seconds += Duration.ofSeconds(this.seconds.toLong()).seconds
                seconds += Duration.ofMinutes(this.minutes.toLong()).seconds
                seconds += Duration.ofHours(this.hours.toLong()).seconds
                seconds += Duration.ofDays(this.days.toLong()).seconds
                if (months > 0 || years > 0) throw SQLFeatureNotSupportedException()
            }
            return seconds
        }
    }
}

This is my configuration in the pom:

<database>
<name>org.jooq.meta.postgres.PostgresDatabase</name>
<includes>.*</includes>
<inputSchema>public</inputSchema>
<excludes>set_adaptive_chunking
    | flyway_schema_history
</excludes>
<forcedTypes>
    <forcedType>
        <userType>java.time.Duration</userType>

        <binding>de.ninjaneers.dmc.jooq.databindings.PostgresIntervalDurationBinding
        </binding>
        <expression>.*interval.*</expression>

        <types>.*</types>
    </forcedType>
</forcedTypes>
</database>

For example I expect jOOQ to generate the routine

time_bucket(bucket_with interval, ts timestamp with time zone)

as

timeBucket(Field<Duration> bucketWidth, Field<Timestamp> ts)

but I get

timeBucket(Field<Object> bucketWidth, Field<Timestamp> ts)

Did you make it work? I have similar task and similar issue, and accepted answer does not work for me... — GRaAL, Jan 25 '21 at 08:06

score 0 · Answer 1 · answered Dec 13 '18 at 13:22

You are confusing the two configuration flags:

<expression>.*interval.*</expression>
<types>.*</types>

<expression> is a regex matching the identifier of the column/attribute/parameter whose type you would like to replace. <types> matches the data types. This is what you want:

<types>.*interval.*</types>
<expression>.*</expression>

jOOQ does not use custom data binding

1 Answers1