Hibernate hql, execute multiple update statements in same query

Question

I want to execute multiple update statements in the same query in hibernate Hql. like below:

hql = " update Table1 set prob1=null where id=:id1; "
                + " delete from Table2 where id =:id2 ";
...
query.executeUpdate();

in the same executeUpdate call I want to update records in Table1 and delete records from Table2.

Is that possible?

Solution 1

In short, what you are looking is something like batching in JDBC. Thich is not provided by Hibernate for Bulk Update query, and I doubt if it will ever be considered for Hibernate.

From my past experience, Batching feature for HQL is rarely useful in real life. It may sound strange that something being useful in SQL+JDBC but not in HQL. I will try to explain.

Usually when we work with Hibernate (or other similar ORM), we work against entities. Hibernate will be responsible to synchronize our entities' state with DB, which is most of the cases that JDBC batching can help in improving performance. However, in Hibernate we do not change individual entity's state by Bulk Update query.

Just give an example, in pseudo-code:

In JDBC, you may do something like (I am trying to mimic what you show in your example):

List<Order> orders = findOrderByUserId(userName);
for (Order order: orders) {
    if (order outstanding quantity is 0) {
        dbConn.addBatch("update ORDER set STATE='C' where ID=:id", order.id);
    } else if (order is after expriation time) {
        dbConn.addBatch("delete ORDER where ID=:id", order.id);
    }
}
dbConn.executeBatch();

Naive translation from JDBC logic to Hibernate may give you something like this:

List<Order> orders = findOrderByUserId(userName);
for (Order order: orders) {
    if (order outstanding quantity is 0) {
        q = session.createQuery("update Order set state='C' where id=:id");
        q.setParameter("id", order.id);
        q.executeUpdate();
    } else if (order is after expriation time) {
        q = session.createQuery("delete Order where id=:id");
        q.setParameter("id", order.id);
        q.executeUpdate();
    }
}

I suspect you think you need the batching feature because you are doing something similar (based on your example, which you use bulk update for individual record). However it is NOT how thing should be done in Hibernate/JPA

(Actually it is better to wrap the persistence layer access through a repository, here I am just simplifying the picture)

List<Order> orders = findOrderByUserId(userName);
for (Order order: orders) {
    if (order.anyOutstanding()) {
        order.complete();    // which internally update the state
    } else if (order.expired) {
        session.delete(order);
    }
}

session.flush();   // or you may simply leave it to flush automatically before txn commit

By doing so, Hibernate is intelligent enough to detect changed/deleted/inserted entities, and make use of JDBC batch to do the DB CUD operations at flush(). More important, this is the whole purpose for ORM: we want to provide behavioral-rich entities to work with, for which internal state change of entities can be "transparently" reflected in persistent storage.

HQL Bulk Update aims for other usage, which is something like one bulk update to DB to affect a lot of records, e.g.:

q = session.createQuery("update Order set state='C' " 
                        + " where user.id=:user_id "
                        + " and outstandingQty = 0 and state != 'C' ");
q.setParameter("user_id", userId);
q.executeUpdate();

There is seldom need for executing a lot of queries in such kind of usage scenario, therefore, overhead of DB round-trip is insignificant, and hence, benefit for and batch processing support for bulk update query is seldom significant.

I cannot omit that there are cases that you really need to issue a lot of update queries which is not appropriate to be done by meaningful entity behavior. In such case, you may want to reconsider if Hibernate is the right tool to be used. You may consider using pure JDBC in such use case so that you have control on how queries are issued.

Solution 2

in the same executeUpdate call I want to update records in Table1 and delete records from Table2.

Is that possible?

executeUpdate() executes a single update query. So, no you cannot do it. You have to execute as many update queries as tables to update/delete. Besides, it makes a code cleaner if you separate queries :

• queries are easier to read and parameters setting is readable and not error-prone.
• to debug query execution, it would be easier to understand if queries are handled one by one in their own executeUpdate()

It doesn't mean that queries must mandatory be transmited one by one.
Batch Processing is a feature provided by Hibernate to improve performance when you want to execute multiples queries. You have to enable the feature to use it. hibernate.jdbc.batch_size property must be set with a suitable value.

If you are undertaking batch processing you will need to enable the use of JDBC batching. This is absolutely essential if you want to achieve optimal performance. Set the JDBC batch size to a reasonable number (10-50, for example):

hibernate.jdbc.batch_size 20 Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.

Besides from official documentation :

Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.

Nevertheless, in your case, it will be useless because as Dragan Bozanovic explained you update/delete different tables in your queries. So, it would create as many batch executions as queried tables.
So, you should execute each query individually. Just commit() the transaction when you deem it should be :

hql = "update Table1 set prob1=null where id=:id1;"
...
query.setParameter("id1",...);
query.executeUpdate();
hql = "delete from Table2 where id =:id2";
...
query.executeUpdate();
query.setParameter("id2",...);
..
tx.commit();

Solution 3

No, it is not possible, because Hibernate usesPreparedStatements for this (which is good because of bind variables), and PreparedStatements do not support batches consisting of multiple different statements.

PreparedStatement can only batch different combinations of bind variables for one statement, which Hibernate uses for batch inserts/updates when flushing changes in the persistence context (session).

Solution 4

The SQL generated by JPA bulk updates/deletes, i.e. calls to javax.persistence.Query.executeUpdate() cannot be batched by Hibernate when passed through to JDBC. @DraganBozanovic and @AdrianShum have already explained this, but to add to their comments: executeUpdate() returns an int (the number of entities updated or deleted) - irrespective of flushing the Hibernate session, how could the int be returned without calling the database immediately and synchronously? The JPQL/HQL/SQL would have to be evaluated client-side, which is not possible because the entities to be bulk updated/deleted may not even have been read into the Hibernate session. Furthermore if the update/delete were not executed on the database immediately, subsequent queries to read in JPA entities could get stale data. Example:

1. executeUpdate to bulk delete all Customers with ID > 1000.
2. read Customer entity with ID = 1001.

If the executeUpdate at 1 were allowed to be deferred until after the read at 2, then you get the wrong answer (Customer still exists).

You either need to read the entities in using JPA, update them, and let Hibernate generate the update SQL (which it can batch), or call JDBC directly to perform batch updates.

View more solutions

Comments

[Andremoniy]

Opening transaction for each batch is not very effective.

[davidxxx]

It's not a batch processing. Don't be so agressive please.

[Nebras]

I already use an AOP on a high-level caller service for transaction management.