HQL multiple updates. Is there a better way?

Question

I have a Map, that I want to persist. The domain object is something like this:

public class Settings {
    private String key;
    private String value;

    public String getKey() { ... }
    public String getValue() { ... }
    public void setKey(String key) { ... }
    public void setValue(String value) { ... }
}

The standard approach is to generate a Setting for each pair, and saveOrUpdate() it. But it generates way too much queries, because I need to save lots of settings at a time, and it really affects perfomance. Is there a way to do this using one update query?

UPD: Okay, maybe there is a hql syntax for updating multiple rows? Something like

update Settings s1 set s1.value = :value1 where s1.key = :key1
                s2 set s2.value = :value2 where s2.key = :key2

Answer 1

If each setting is a row in a table then there needs to be as many update statements issued. But a few things to look at would be

1) Tweaking hibernate batch size to say 20 or so which means although hibernate issues 20 updates they are all sent as a batch (with hopefully one network round trip providing drivers permit batching)

2) Modifying your dao code/transaction logic to do things in batch

3) Having a stored procedure and passing array values (hibernate and storedprocs are not good friends I think)

Answer 2

I do not know any syntax that would allow having multiple where statement like the one you've written.

Be sure that you are using batching. If not try your updates with it activated.

If the value you want to use to update the Setting table are already in the database somewhere else, you should be able to do a single update. That might be faster.

If the data is not already in the database, you can insert them in a temporary table before doing a single update query, but here, you will need sql queries (which is possible with hibernate).

But that single update statement might still be too slow. the last solution (without rethinking completely the logic of the use case) i kown is a 'select for update' statement. That's raw jdbc, but with spring JdbcTemplate, it's not that painful. You select all the row you want to update (if it make the request faster, you might select more, but in that case, you will have a impact on network resource, getting more row out of the db than needed), and then update them using jdbc as you fetch them.