Jooq Postgres Varying Bit

Question

I am looking into Jooq and has struck something that I can find very little documentation for - none that really answers my question in all honesty.

What I want to do is to have a column in a table like permissions varying bit(2000) (Postgres 9.5+)

What Jooq however does with this, is to see it as a varchar... which obviously is incorrect even at generation. What is the shortest path to success on something like this.

Thanks for your question. I've removed the Kotlin part, as it's not really part of the question and not a good fit for Stack Overflow (which is a platform that avoids opinionated things). But feel free to re-raise the issue on the [mailing list](https://groups.google.com/forum/#!forum/jooq-user). We're very open to discussion! Meanwhile, I'll reply to your PostgreSQL `varying bit` question. — Lukas Eder, Nov 25 '16 at 18:27

score 2 · Answer 1 · answered Nov 25 '16 at 18:31

2

I'm not sure anymore what the design decision for mapping PostgreSQL's "bit string" types to java.lang.String was. I don't even exclude an oversight on the fact that even if they're called "bit strings", they're not really strings.

I suspect that the most fitting Java type to represent this is a BitSet. So, your ideal way forward would be to implement a data type binding of the form:

public class VaryingBitBinding extends Binding<String, BitSet> {
    // ...
}

And implement it at the JDBC level. You can then hook this binding into your code generator:

<forcedType>
    <userType>java.util.BitSet</userType>
    <binding>com.example.VaryingBitBinding</binding>
    <types>varying\ bit</types>
</forcedType>

answered Nov 25 '16 at 18:31

Lukas Eder

211,314
129
689
1,509

Thanks Lukas, do you think this is something that can/will be entertained with the shipped JOOQ distribution in future? – user1210708 Jan 02 '17 at 12:03
@user1210708: Yes, at some point: https://github.com/jOOQ/jOOQ/issues/5507 – Lukas Eder Jan 02 '17 at 15:28
Old post, but seems the only one dealing with postgresql bit data type and jOOQ. Using a ```BitSetConverter extends AbstractConverter```, I'm still running into _PSQLException: ERROR: operator does not exist: bit varying <> character varying_ – Simon Apr 27 '18 at 12:18
@Simon: My answer recommends to use a binding, not a converter, because you probably need to be in control of how the bind variable is rendered and bound. – Lukas Eder Apr 30 '18 at 07:20
1

Thanks @LukasEder, missed that the first time round. Taking the example from [your link](https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings/) and basically using '::bit' in the sql() method works like a charm. – Simon May 11 '18 at 12:02

score 0 · Answer 2 · answered May 11 '18 at 12:04

My class is work in progress, but with this I'm able to store a varying bit:

public class BitSetBinding implements Binding<String, BitSet> {

    private static final long serialVersionUID = 1L;

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

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

    @Override
    public final void set(final BindingSetStatementContext<BitSet> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
        // this most likely won't work: ctx.statement().setString(ctx.index(), castToBit(convert(ctx.convert(converter()).value(),
        // String.class)));
    }

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

    @Override
    public final void get(final BindingGetResultSetContext<BitSet> ctx) throws SQLException {
        // Get a result set, ie. convert something like '00000000' to a BitSet
        final String fromDatabase = ctx.resultSet().getString(ctx.index());
        ctx.convert(converter()).value(fromDatabase);
    }

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

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

    @Override
    public Converter<String, BitSet> converter() {
        return new BitSetConverter();
    }

    /**
     * Converter referenced by jOOQ
     */
    private static class BitSetConverter extends AbstractConverter<String, BitSet> {

        private static final long serialVersionUID = 1L;

        public BitSetConverter() {
            super(String.class, BitSet.class);
        }

        @Override
        public BitSet from(final String databaseObject) {
            if (databaseObject == null) {
                return null;
            }
            final BitSet bs = new BitSet();
            int pos = databaseObject.indexOf('1');
            while (pos != -1) {
                bs.set(pos);
                pos = databaseObject.indexOf('1', pos + 1);
            }
            return bs;
        }

        @Override
        public String to(final BitSet userObject) {
            if (userObject == null) {
                return null;
            }
            final StringBuilder sb = new StringBuilder();
            for (int k = 0; k < userObject.length(); k++) {
                sb.append(userObject.get(k) ? '1' : '0');
            }
            return sb.toString();
        }
    }
}

Jooq Postgres Varying Bit

2 Answers2