Convert types of PostgreSQL UUID arrays using jOOQ

Question

I am trying to bind a PostgreSQL (9.4) UUID array to an array of a wrapper class around each UUID. Please note that these wrappers are integrated throughout the application so removing them is not an option. I am using jOOQ and it's maven plugin (3.5.1) to generate PoJo's, Record and Table classes.

The relevant part of the database schema I'm having trouble binding looks something like:

create table foo (
    id uuid primary key,
    ids uuid[] not null
);

I had then tried to convert the class using a forcedType element however the classes it generated wouldn't compile. Giving up on that I was just going to leave them generated as UUID's until I experienced some issues where the array values are not cast to uuid's and PostgreSQL thinks the array is a text array in my query [1].

To get around that I have tried to add a Binding [2] and a Converter [3] where the converter wraps the UUID's with our wrapper and the binding adds cast expressions to the generated SQL. This works fine if I write a fluent query [4] however doesn't work correctly when inserting a Record [5]. As I am building the array query piece by piece the insert statement ends up with 'array.length - 1' parametized parts. I suspect I need to override #get and #set methods of the Binding class however I have found the docs a bit light in this area.

So my question is, what is the correct way to bind a UUID array in jOOQ, with or without using a Binding class? Furthermore, is it possible to convert it to a T array in the process?

1. The query (names changed)

public BazId getBazIdBySearchingFooIdsInReverse(
        @NotNull final OtherId otherId,
        @NotNull final SomethingId somethingId,
        @NotNull final String barTypeName,
        @NotNull final SomethingElseId somethingElseId) {
    final Bar bar = BAR.as("bar");
    final Foo foo = FOO.as("foo");
    return db.select(BAZ.ID)
             .from(BAZ)
             .join(bar)
             .on(BAZ.SOMETHING_ID.eq(bar.SOMETHING_ID))
             .join(foo)
             .on(bar.FOO_ID.eq(foo.ID))
             .join("lateral unnest(foo.ids) with ordinality as x (id,ord)")
             .on("x.id=foo.id")
             .join(BAR_TYPE)
             .on(bar.BAR_TYPE_ID.eq(BAR_TYPE.ID)
                                .and(BAR_TYPE.NAME.equalIgnoreCase(barTypeName)))
             .where(BAZ.SOMETHING_ID.eq(somethingId))
             .and(BAZ.SOMETHING_ELSE_ID.eq(somethingElseId))
             .and(bar.OTHER_ID.eq(otherId))
             .orderBy(DSL.field("x.ord").desc())
             .limit(1)
             .fetchOneInto(BazId.class); //Wraps a UUID
}

2. The binding

public class FooIdsBinding extends DefaultBinding<Object[], FooId[]> {
    private static final long serialVersionUID = 0L;

    private static final UUIDConverter converter = new UUIDConverter();

    public FooIdsBinding() {
        super(new FooIdsConverter());
    }

    @Override
    public void sql(final BindingSQLContext<FooId[]> ctx) {
        final RenderContext render = ctx.render();
        render.sql("array[");
        final UUID[] uuids = ctx.convert(converter).value();
        for (int i = 0, last = uuids.length - 1; i <= last; ++i) {
            render.visit(DSL.val(uuids[i])).sql("::uuid");
            if (i != last) {
                render.sql(',');
            }
        }
        render.sql("]::uuid[]");
    }

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

    static class BaseUUIDConverter {

        public FooId[] from(final Object[] from) {
            return from == null ? null : Arrays.stream(from)
                                               .map(that -> new FooId((UUID)that))
                                               .collect(Collectors.toList())
                                               .toArray(new FooId[from.length]);
        }

        public UUID[] to(final FooId[] from) {
            return from == null ? null : Arrays.stream(from)
                                               .map(FooId::getUuid)
                                               .collect(Collectors.toList())
                                               .toArray(new UUID[from.length]);
        }

        public Class<FooId[]> toType() {
            return FooId[].class;
        }
    }

    private static class UUIDConverter extends BaseUUIDConverter implements Converter<UUID[], FooId[]> {

        @Override
        public FooId[] from(final UUID[] that) {
            return super.from(that);
        }

        @Override
        public Class<UUID[]> fromType() {
            return UUID[].class;
        }
    }
}

3. The converter. Has to be Object[] to make the generated Table compile

public class FooIdConverter extends FooIdsBinding.BaseUUIDConverter implements Converter<Object[],FooId[]> {
    private static final long serialVersionUID = 0L;

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

4. A query that works

    db.insertInto(FOO)
      .set(FOO.ID, new FooId())
      .set(FOO.IDS, new FooId[]{new FooId(),new FooId()})
      .execute();

5. A query that doesn't

    foo = new FooRecord();
    foo.setId(new FooId());
    foo.setIds(new FooId[]{new FooId(),new FooId()});
    db.executeInsert(foo);

Update

I ended up with this for the binding and converter and it works OK. I had thought I needed to cast each element of the array to a uuid, my implementation of which was causing problems with jOOQ's sql generation, but I think I had only seen error's related to that when jOOQ before I overrode Binding#register to be ARRAY.

1. Converter

   public class FooIdConverter implements Converter<Object[],FooId[]> {
   
       private static final long serialVersionUID = 1L;
   
       @Override
       public FooId[] from(final Object[] from) {
           return from == null ? null : Arrays.stream(from)
                                              .map(that -> new FooId((UUID)that))
                                              .collect(Collectors.toList())
                                              .toArray(new FooId[from.length]);
       }
   
       @Override
       public UUID[] to(final FooId[] from) {
           return from == null ? null : Arrays.stream(from)
                                              .map(FooId::getUuid)
                                              .collect(Collectors.toList())
                                              .toArray(new UUID[from.length]);
       }
   
       @Override
       @SuppressWarnings("unchecked")
       public Class<Object[]> fromType() {
           return (Class)UUID[].class;
       }
   
       @Override
       public Class<FooId[]> toType() {
           return FooId[].class;
       }
   }
   

2. Binding

   public class FooIdBinding extends DefaultBinding<Object[], FooId[]> {
   
       private static final long serialVersionUID = 1L;
   
       public FooIdBinding() {
           super(new FooIdConverter());
       }
   
       @Override
       public void sql(final BindingSQLContext<FooId[]> ctx) {
           super.sql(ctx);
           ctx.render().sql("::uuid[]");
       }
   
       @Override
       public void register(final BindingRegisterContext<FooId[]> ctx) throws SQLException {
           ctx.statement().registerOutParameter(ctx.index(), Types.ARRAY);
       }
   
       @Override
       public void get(final BindingGetResultSetContext<FooId[]> ctx) throws SQLException {
           ctx.value(_convert(ctx.resultSet().getArray(ctx.index())));
       }
   
       @Override
       public void get(final BindingGetStatementContext<FooId[]> ctx) throws SQLException {
           ctx.value(_convert(ctx.statement().getArray(ctx.index())));
       }
   
       @Override
       public void get(final BindingGetSQLInputContext<FooId[]> ctx) throws SQLException {
           ctx.value(_convert(ctx.input().readArray()));
       }
   
       @Override
       public void set(final BindingSetStatementContext<FooId[]> ctx) throws SQLException {
           final PreparedStatement ps = ctx.statement();
           ps.setArray(ctx.index(), ps.getConnection().createArrayOf("uuid", ctx.value()));
       }
   
       @Override
       public void set(final BindingSetSQLOutputContext<FooId[]> ctx) throws SQLException {
           throw new UnsupportedOperationException();
       }
   
       protected FooId[] _convert(final Array array) throws SQLException {
           if (array == null) {
               return null;
           } else {
               return converter().from(((UUID[]) array.getArray()));
           }
       }
   }
   

Answer 1

It looks like there is a bug in the jOOQ code generator that prevents UUID[] types from being overridden: #4388