@OrderColumn - Too many updates

Hibernate ORM
1

My entity KDKap forms a tree structure in which the respective child elements must maintain their order.

I user the @OrderColumn annotation in order to maintain the order of the respective child entries - which works fine.

See the use of the @OrderColumn annotation:

    private List<KDKap> children;
    @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @OrderColumn(name = "sortnr")
    public List<KDKap> getChildren() { if (null == children) { children = new ArrayList<>(); } return children; }
    public void setChildren(List<KDKap> aKdKaps) { this.children = aKdKaps; }

However I noticed that always all the childrens rows are updated whenever any of the child rows change their order in the sequence, even if the change is right at the end of the sequence.

See the updates generated by Hibernate:

2019-12-10 08:45:17,365 DEBUG [org.hibernate.SQL] (default task-2) update db2inst1.kd_kap set sortnr=? where kd_kap_id=?
2019-12-10 08:45:17,366 DEBUG [org.hibernate.SQL] (default task-2) update db2inst1.kd_kap set sortnr=? where kd_kap_id=?
2019-12-10 08:45:17,366 DEBUG [org.hibernate.SQL] (default task-2) update db2inst1.kd_kap set sortnr=? where kd_kap_id=?
2019-12-10 08:45:17,366 DEBUG [org.hibernate.SQL] (default task-2) update db2inst1.kd_kap set sortnr=? where kd_kap_id=?
2019-12-10 08:45:17,366 DEBUG [org.hibernate.SQL] (default task-2) update db2inst1.kd_kap set sortnr=? where kd_kap_id=?

In my use case this is very bad performance wise because there may be thousands of children and I need to have triggers on the table which make it even worse.

Therefore I am looking for a way to limit the updates to those rows where the order column actually changes - perhaps like this:

update db2inst1.kd_kap set sortnr=? where kd_kap_id=? and sortnr <> ?

Is there a way to specify which update statement is used or is there any other way of limiting the updates to just the rows where the order column really needs to change?

2

I could use a stored procedure in order to use the parameters passed to the prepared statement. However I do not know how to return the row count which is checked by hibernate. So I’ am still stuck.
IBM DB2 JDBC Driver CallableStatement update count

3

I thought to replace the default statement

update db2inst1.kd_kap set sortnr=? where kd_kap_id=?

by

merge into db2inst1.kd_kap as p
using table (values (?, ?)) AS x(sortnrNeu,kd_kap_fk)
on p.kd_kap_id = x.kd_kap_fk and p.sortnr <> x.sortnrNeu
when matched then 
update set p.sortnr = x.sortnrNeu

But it doesn’t work either because in that case the row count returned by preparedSTatement.executeUpdate is 0

4

I finally solved it by implementing a org.hibernate.engine.jdbc.connections.spi.ConnectionProvider which returns dynamic proxies for java.sql.Connection and for java.sql.PreparedStatement of all the statements which are to be enhanced by adding the and sortnr <> ? clause and adding the first parameter value as a third parameter.