CODEWITHSUNDEEP
javajdbcjooq
A248
A248

Reputation: 804

JOOQ Batch Insert with Custom Binding

I'm trying to stream a BLOB parameter with JOOQ in the context of batch insert. Per stream contents of 1GB file to sqlite table under single column, a data type binding is the way to stream parameter arguments. Following its example, I implemented Binding#set:

    @Override
    public void set(BindingSetStatementContext<KitItem> ctx) throws SQLException {
        PreparedStatement statement = ctx.statement();
        Blob blob = statement.getConnection().createBlob();
        KitItem item = ctx.value(); // The object to write to the stream

        try (OutputStream output = blob.setBinaryStream(1)) {
            // Main serialization implementation
        } catch (IOException ex) {
            throw rethrow(ex);
        }
        statement.setBlob(ctx.index(), blob);
        ctx.autoFree(blob);
    }

This works fine for simple insert queries. However, when I use a batch insert, JOOQ uses the Binding's Converter#from. I have implemented Converter#from to throw UnsupportedOperationException.

        DataType<KitItem> itemType = KITPVP_KITS_CONTENTS.ITEM.getDataType().asConvertedDataType(new KitItemBinding());
        Field<KitItem> itemColumn = DSL.field(KITPVP_KITS_CONTENTS.ITEM.getQualifiedName(), itemType);
        if (true) { // Flipping this flag causes the Converter to be used
            for (ItemInSlot itemInSlot : contents) {
                context.insertInto(KITPVP_KITS_CONTENTS)
                        .columns(KITPVP_KITS_CONTENTS.KIT_ID, KITPVP_KITS_CONTENTS.SLOT, itemColumn)
                        .values(kitId, (byte) itemInSlot.slot(), itemInSlot.item())
                        .execute();
            }
        } else {
            BatchBindStep batch = context.batch(context
                    .insertInto(KITPVP_KITS_CONTENTS)
                    .columns(KITPVP_KITS_CONTENTS.KIT_ID, KITPVP_KITS_CONTENTS.SLOT, itemColumn)
                    .values((Field<Integer>) null, null, null));
            for (ItemInSlot itemInSlot : contents) {
                batch.bind(
                        kitId, itemInSlot.slot(), itemInSlot.item());
            }
            batch.execute();
        }

I suspect this is a mistake in my usage of JOOQ, since I would expect Converter#to to be called when setting a parameter, as opposed to Converter#from. I.e.:

@Override
public KitItem from(byte[] databaseObject) {
    // I DO NOT expect this method to be called when setting parameters
    throw new UnsupportedOperationException("Conversion should be streamed instead");
}

@Override
public byte[] to(KitItem userObject) {
    // I expect this method or Binding#set to be called when setting parameters
    throw new UnsupportedOperationException("Conversion should be streamed instead");
}

I'm not sure if this is possibly related to Jooq batch insert with custom field binding but I have checked that question (it's also unanswered). It's possible both behaviors are caused by some implementation bug in JOOQ. I don't know enough about JOOQ to say.

Upvotes: 0

Views: 921

Answers (1)

A248
A248

Reputation: 804

JOOQ calls the Converter to handle the initial values – namely, the initial arguments provided to values(null, null, null), NOT the bind variables given to bind(obj1, obj2, ...). So, simply pass-through these null values and it will work:

@Override
public KitItem from(byte[] databaseObject) {
    if (databaseObject == null) {
      return null;
    }
    throw new UnsupportedOperationException("Conversion should be streamed instead");
}

Secondly, don't try to execute an empty batch. Check if the data you are about to insert is empty before trying to execute the batch statement with JOOQ. If try to execute an empty batch, the initial values (usually null) will be executed as a single insert query.

Upvotes: 1

Related Questions