SqlBatch with UPDATE, is it supposed to work?

[Render Wood]

All examples using @SqlBatch I have found from documentation and web seem to be inserts.

I tried:

@SqlBatch("update items set items_left = items_left - :orderAmount where id = :id and items_left >= :orderAmount")
protected abstract int consumeOrderItems(@BindBean Iterator<OrderItem> items)

this causes:

! java.lang.ClassCastException: [I cannot be cast to java.lang.Number
! at shop.db.OrderDAO$$EnhancerByCGLIB$$fa78546c.consumeOrderItems(<generated>)
Same SQL run as @SqlUpdate and iterated outside works as it should.

Are there even any performance benefits between iterating yourself and using SqlBatch?

Thanks,

Simo

[Steven Schlansker]

> On Mar 24, 2016, at 6:51 AM, Render Wood <rende...@gmail.com> wrote:
>
> All examples using @SqlBatch I have found from documentation and web seem to be inserts.
>
> I tried:
> @SqlBatch("update items set items_left = items_left - :orderAmount where id = :id and items_left >= :orderAmount")
> protected abstract int consumeOrderItems(@BindBean Iterator<OrderItem> items)
>
> this causes:
> ! java.lang.ClassCastException: [I cannot be cast to java.lang.Number
>
> ! at shop.db.OrderDAO$$EnhancerByCGLIB$$fa78546c.consumeOrderItems(<generated>)

This could be because the batch handler expects to return an int[]. Each batch is treated
as a separate operation that returns the number of rows modified by that individual part --
so it returns the array of all the individual parts' results.

>
> Same SQL run as @SqlUpdate and iterated outside works as it should.
>
> Are there even any performance benefits between iterating yourself and using SqlBatch?

Yes, you can prepare the entire batch in one go and send it to the server for processing.
If you iterate yourself, you have to wait for a round trip to the server for each statement
you execute.

Generally if your database is local, this cost is relatively small. You may not find
much real-world performance gain from using batching. I tend to avoid it personally
unless I can prove that the performance gain is worth the maintainability cost
(it is much harder to read errors out of batches, for example).