Java – hibernate HQL, execute multiple update statements in the same query

I want to execute multiple update statements in the same query in Hibernate HQL

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 the records in Table 1 and delete the records from table 2

Is that possible?

Solution

In short, what you are looking for is batch processing like JDBC This is not a batch update query provided by hibernate. I doubt whether it will be considered for hibernate

From my past experience, the batch processing function of HQL is rarely useful in real life This may sound strange. Some things are useful in SQL JDBC, but not in HQL I'll try my best to explain

Usually when we work with hibernate (or other similar ORM), we work with entities Hibernate will be responsible for synchronizing the entity state with DB, which is most cases where JDBC batch processing can help improve performance However, in Hibernate, we will not change the state of a single entity by batch updating queries

Give an example. In the pseudo code:

In JDBC, you can do the following (I'm trying to simulate what you display in exmaple):

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();

The real 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 batch processing because you are doing something similar (according to your example, you use batch update of individual records) But what should not be done in Hibernate / JPA

(in fact, it's best to access it through the repository wrapper persistence layer. I'm just simplifying the picture here)

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 smart enough to detect changed / deleted / inserted entities and perform DB cud operations in flush () using JDBC batch processing More importantly, this is the whole purpose of ORM: we want to provide behavior rich entities to deal with, and the internal state changes of entities can be "transparently" reflected in persistent storage

HQL batch update is used for other purposes, just like a batch update of DB, which affects a large number of records, such as:

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();

In this case, it is rarely necessary to execute a large number of queries. Therefore, the overhead of DB round-trip is insignificant, so there is little benefit of batch support for batch update queries

I can't ignore some cases where you really need to publish a large number of update queries, which is not suitable for meaningful entity behavior In this case, you may need to reconsider whether hibernate is the right tool to use You can consider using pure JDBC in this use case to control how queries are issued