title: Convert types of PostgreSQL UUID arrays using jOOQ
body: <p>I am trying to bind a PostgreSQL (9.4) <code>UUID</code> array to an array of a wrapper class around each <code>UUID</code>. 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, <code>Record</code> and <code>Table</code> classes.</p>
  
  <p>The relevant part of the database schema I'm having trouble binding looks something like:</p>
  
  <pre class="lang-sql prettyprint-override"><code>create table foo (
      id uuid primary key,
      ids uuid[] not null
  );
  </code></pre>
  
  <p>I had then tried to convert the class using a <code>forcedType</code> 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]. </p>
  
  <p>To get around that I have tried to add a <code>Binding</code> [2] and a <code>Converter</code> [3] where the converter wraps the <code>UUID</code>'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 <code>Record</code> [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 <code>#get</code> and <code>#set</code> methods of the <code>Binding</code> class however I have found the docs a bit light in this area.</p>
  
  <p>So my question is, what is the correct way to bind a <code>UUID</code> array in jOOQ, with or without using a <code>Binding</code> class? Furthermore, is it possible to convert it to a T array in the process?</p>
  
  <ol>
  <li>The query (names changed)</li>
  </ol>
  
  <pre class="lang-java prettyprint-override"><code>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
  }
  </code></pre>
  
  <ol start="2">
  <li>The binding</li>
  </ol>
  
  <pre class="lang-java prettyprint-override"><code>public class FooIdsBinding extends DefaultBinding&lt;Object[], FooId[]&gt; {
      private static final long serialVersionUID = 0L;
  
      private static final UUIDConverter converter = new UUIDConverter();
  
      public FooIdsBinding() {
          super(new FooIdsConverter());
      }
  
      @Override
      public void sql(final BindingSQLContext&lt;FooId[]&gt; 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 &lt;= last; ++i) {
              render.visit(DSL.val(uuids[i])).sql("::uuid");
              if (i != last) {
                  render.sql(',');
              }
          }
          render.sql("]::uuid[]");
      }
  
      @Override
      public void register(final BindingRegisterContext&lt;FooId[]&gt; 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 -&gt; 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&lt;FooId[]&gt; toType() {
              return FooId[].class;
          }
      }
  
      private static class UUIDConverter extends BaseUUIDConverter implements Converter&lt;UUID[], FooId[]&gt; {
  
          @Override
          public FooId[] from(final UUID[] that) {
              return super.from(that);
          }
  
          @Override
          public Class&lt;UUID[]&gt; fromType() {
              return UUID[].class;
          }
      }
  }
  </code></pre>
  
  <ol start="3">
  <li>The converter. Has to be <code>Object[]</code> to make the generated <code>Table</code> compile</li>
  </ol>
  
  <pre class="lang-java prettyprint-override"><code>public class FooIdConverter extends FooIdsBinding.BaseUUIDConverter implements Converter&lt;Object[],FooId[]&gt; {
      private static final long serialVersionUID = 0L;
  
      @Override
      public Class&lt;Object[]&gt; fromType() {
          return (Class)UUID[].class;
      }
  }
  </code></pre>
  
  <ol start="4">
  <li>A query that works</li>
  </ol>
  
  <pre class="lang-java prettyprint-override"><code>    db.insertInto(FOO)
        .set(FOO.ID, new FooId())
        .set(FOO.IDS, new FooId[]{new FooId(),new FooId()})
        .execute();
  </code></pre>
  
  <ol start="5">
  <li>A query that doesn't</li>
  </ol>
  
  <pre class="lang-java prettyprint-override"><code>    foo = new FooRecord();
      foo.setId(new FooId());
      foo.setIds(new FooId[]{new FooId(),new FooId()});
      db.executeInsert(foo);
  </code></pre>
  
  <p><strong>Update</strong></p>
  
  <p>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 <code>Binding#register</code> to be ARRAY.</p>
  
  <ol>
  <li><p>Converter
  </p>
  
  <pre><code>public class FooIdConverter implements Converter&lt;Object[],FooId[]&gt; {
  
      private static final long serialVersionUID = 1L;
  
      @Override
      public FooId[] from(final Object[] from) {
          return from == null ? null : Arrays.stream(from)
                                             .map(that -&gt; 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&lt;Object[]&gt; fromType() {
          return (Class)UUID[].class;
      }
  
      @Override
      public Class&lt;FooId[]&gt; toType() {
          return FooId[].class;
      }
  }
  </code></pre></li>
  <li><p>Binding
  </p>
  
  <pre><code>public class FooIdBinding extends DefaultBinding&lt;Object[], FooId[]&gt; {
  
      private static final long serialVersionUID = 1L;
  
      public FooIdBinding() {
          super(new FooIdConverter());
      }
  
      @Override
      public void sql(final BindingSQLContext&lt;FooId[]&gt; ctx) {
          super.sql(ctx);
          ctx.render().sql("::uuid[]");
      }
  
      @Override
      public void register(final BindingRegisterContext&lt;FooId[]&gt; ctx) throws SQLException {
          ctx.statement().registerOutParameter(ctx.index(), Types.ARRAY);
      }
  
      @Override
      public void get(final BindingGetResultSetContext&lt;FooId[]&gt; ctx) throws SQLException {
          ctx.value(_convert(ctx.resultSet().getArray(ctx.index())));
      }
  
      @Override
      public void get(final BindingGetStatementContext&lt;FooId[]&gt; ctx) throws SQLException {
          ctx.value(_convert(ctx.statement().getArray(ctx.index())));
      }
  
      @Override
      public void get(final BindingGetSQLInputContext&lt;FooId[]&gt; ctx) throws SQLException {
          ctx.value(_convert(ctx.input().readArray()));
      }
  
      @Override
      public void set(final BindingSetStatementContext&lt;FooId[]&gt; ctx) throws SQLException {
          final PreparedStatement ps = ctx.statement();
          ps.setArray(ctx.index(), ps.getConnection().createArrayOf("uuid", ctx.value()));
      }
  
      @Override
      public void set(final BindingSetSQLOutputContext&lt;FooId[]&gt; 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()));
          }
      }
  }
  </code></pre></li>
  </ol>