Jooq array as collection

Question

I want to use Collections in java instead of arrays to serialize postgreSQL arrays. Such as int[], varchar(256)[] to java Collection and Collection.

I use jooq 3.6.2, java 8 and postgresql 9.2.9.

I tried to implement custom binding:

public class PostgresArrayBinding implements Binding<Object, Collection> {

    @Override
    public Converter<Object, Collection> converter() {
        return new Converter<Object, Collection>() {
            @Override
            public Collection from(final Object dbObj) {
                return dbObj == null ? null : new ArrayList<>(Arrays.asList((Object[])dbObj));
            }

            @Override
            public Object to(final Collection userObject) {
                return userObject == null ? null : "ARRAY[" + Joiner.on(',').join(userObject) + "]";
            }

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

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

    @Override
    public void sql(final BindingSQLContext<Collection> ctx) throws SQLException {
        ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::ARRAY");
    }

    @Override
    public void register(final BindingRegisterContext<Collection> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.ARRAY);
    }

    @Override
    public void set(final BindingSetStatementContext<Collection> ctx) throws SQLException {
        ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
    }

    @Override
    public void set(final BindingSetSQLOutputContext<Collection> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    @Override
    public void get(final BindingGetResultSetContext<Collection> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }

    @Override
    public void get(final BindingGetStatementContext<Collection> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

    @Override
    public void get(final BindingGetSQLInputContext<Collection> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
}

And jooq-config.xml:

<configuration>
...
<generator>
    <database>
        <name>org.jooq.util.postgres.PostgresDatabase</name>
        <includes>.*</includes>
        <excludes>schema_version</excludes>
        <inputSchema>public</inputSchema>            
        <customTypes>
            ...
            <customType>
                <name>ARRAY</name>
                <type>java.util.Collection</type>
                <binding>ru.fabit.contingent.configuration.persistence.PostgresArrayBinding</binding>
            </customType>                
        </customTypes>

        <forcedTypes>
            ...
            <forcedType>
                <name>ARRAY</name>
                <expression>.*_ARRAY</expression>
                <types>.*</types>
            </forcedType>                
        </forcedTypes>
    </database>
    <generate>
        <records>true</records>
        <interfaces>true</interfaces>
        <relations>true</relations>
        <validationAnnotations>true</validationAnnotations>
    </generate>
    <target>
        <packageName>ru.fabit.contingent.models.generated</packageName>
        <directory>src/main/java/</directory>
    </target>
</generator>
</configuration>

SQL:

CREATE TABLE array_tests(string_array varchar(256)[]);

And I had error in generated class:

public final TableField<ArrayTestsRecord, Collection[]> STRING_ARRAY = createField("string_array", org.jooq.impl.DefaultDataType.getDefaultDataType("java.util.Collection").getArrayDataType(), this, "", new PostgresArrayBinding());

no suitable method found for createField(String,DataType,ArrayTests,String,PostgresArrayBinding)

Any ideas?

Answer 1

This is due to a bug in the code generator, which is fixed for jOOQ 3.8: https://github.com/jOOQ/jOOQ/issues/4388

There's no workaround for jOOQ 3.7 or less, I'm afraid, short of manually patching the erroneous generated code.