Java Hibernate hql，在同一个查询中执行多个更新语句

Hibernate hql, execute multiple update statements in same query

提问by Nebras

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

我想在 hibernate Hql 中的同一个查询中执行多个更新语句。像下面这样：

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

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

在同一个executeUpdate调用中，我想更新 Table1 中的记录并删除 Table2 中的记录。

Is that possible?

那可能吗？

采纳答案by Adrian Shum

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.

简而言之，您正在寻找类似于 JDBC 中的批处理。Hibernate 没有为批量更新查询提供 Thich，我怀疑它是否会被考虑用于 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.

根据我过去的经验，HQL 的批处理功能在现实生活中很少有用。有些东西在 SQL+JDBC 中有用但在 HQL 中没有用，这听起来可能很奇怪。我会尽力解释。

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.

通常，当我们使用 Hibernate（或其他类似的 ORM）时，我们会针对实体工作。Hibernate 将负责将我们实体的状态与 DB 同步，这是 JDBC 批处理可以帮助提高性能的大多数情况。但是，在 Hibernate 中，我们不会通过批量更新查询更改单个实体的状态。

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):

在 JDBC 中，您可以执行以下操作（我正在尝试模仿您在示例中显示的内容）：

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:

从 JDBC 逻辑到 Hibernate 的简单转换可能会给你这样的结果：

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 NOThow thing should be done in Hibernate/JPA

我怀疑您认为您需要批处理功能，因为您正在做类似的事情（根据您的示例，您对单个记录使用批量更新）。然而，这不是在 Hibernate/JPA 中应该如何做的事情

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

（其实最好把持久层的访问通过repository包裹起来，这里我只是简化了下图）

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.

通过这样做，Hibernate 足够智能以检测更改/删除/插入的实体，并利用 JDBC 批处理在flush(). 更重要的是，这就是 ORM 的全部目的：我们希望提供行为丰富的实体来使用，实体的内部状态变化可以“透明地”反映在持久存储中。

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

HQL 批量更新旨在用于其他用途，类似于对 DB 进行批量更新以影响大量记录，例如：

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.

在这种使用场景下很少需要执行大量查询，因此，DB往返的开销微不足道，因此批量更新查询的好处和批处理支持很少显着。

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.

我不能忽略在某些情况下您确实需要发出大量更新查询，而这些查询不适合通过有意义的实体行为来完成。在这种情况下，您可能需要重新考虑 Hibernate 是否是要使用的正确工具。您可以考虑在此类用例中使用纯 JDBC，以便您可以控制查询的发出方式。

回答by Dragan Bozanovic

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.

不，这是不可能的，因为 HibernatePreparedStatement为此使用s（这很好，因为绑定变量），并且PreparedStatements 不支持由多个不同语句组成的批处理。

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

PreparedStatement只能为一个语句批处理不同的绑定变量组合，Hibernate在刷新持久性上下文（会话）中的更改时使用它来批量插入/更新。

回答by davidxxx

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

Is that possible?

在同一个 executeUpdate 调用中，我想更新 Table1 中的记录并删除 Table2 中的记录。

那可能吗？

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 :

executeUpdate()执行单个更新查询。所以，不，你不能这样做。您必须执行与要更新/删除的表一样多的更新查询。此外，如果您将查询分开，它会使代码更清晰：

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

• 查询更容易阅读，参数设置可读且不易出错。
• 调试查询执行，如果查询自己一个一个处理会更容易理解 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_sizeproperty must be set with a suitable value.

这并不意味着查询必须一一传输。
批处理是 Hibernate 提供的一项功能，用于在您想要执行多个查询时提高性能。您必须启用该功能才能使用它。 hibernate.jdbc.batch_size属性必须设置为合适的值。

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.

如果您正在进行批处理，则需要启用 JDBC 批处理。如果您想获得最佳性能，这绝对是必不可少的。将 JDBC 批处理大小设置为一个合理的数字（例如 10-50）：

hibernate.jdbc.batch_size 20 如果您使用身份标识符生成器，​​Hibernate 会在 JDBC 级别透明地禁用插入批处理。

Besides from official documentation:

除了官方文档：

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

如果您使用身份标识符生成器，​​Hibernate 会在 JDBC 级别透明地禁用插入批处理。

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 :

尽管如此，在您的情况下，它是无用的，因为正如 Dragan Bozanovic 解释的那样，您在查询中更新/删除了不同的表。因此，它将创建与查询表一样多的批处理执行。
因此，您应该单独执行每个查询。当您认为它应该是时提交（）事务：

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

回答by Olakunle Awotunbo

Why not execute the two queries separately in a Transactional method

为什么不在 Transactional 方法中分别执行两个查询

By annotating the method with @Transactional, if any of the query fails, the other won't execute.

通过使用@Transactional 注释方法，如果任何一个查询失败，另一个将不会执行。

 @Transactional(propagation = Propagation.REQUIRED, readOnly = false)

 public void executeQuery(Obj1 obj1) {

 String query="update table1 set actualRepaymentAmount=expectedRepaymentAmount,active='Y'  where loanCaseId = '"+caseId+"'";
        sessionFactory.getCurrentSession().createQuery(query).executeUpdate();

 query="update table2 set loanStatus='C' where loanCaseId = '"+caseId+"'";  
    sessionFactory.getCurrentSession().createQuery(query).executeUpdate();

        ...

 }

回答by coder

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 databaseimmediately, subsequent queries to read in JPA entities could get stale data. Example:

JPA 批量更新/删除生成的 SQL，即对 javax.persistence.Query.executeUpdate() 的调用在传递到 JDBC 时无法由 Hibernate 进行批处理。@DraganBozanovic 和 @AdrianShum 已经解释了这一点，但要添加到他们的评论中：executeUpdate() 返回一个 int（更新或删除的实体数） - 不管刷新 Hibernate 会话，如何在不调用数据库的情况下返回 int立即同步？JPQL/HQL/SQL 必须在客户端进行评估，这是不可能的，因为要批量更新/删除的实体甚至可能还没有被读入 Hibernate 会话。此外，如果没有立即在数据库上执行更新/删除，则在 JPA 实体中读取的后续查询可能会获得陈旧数据。例子：

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

1. 执行更新以批量删除 ID > 1000 的所有客户。
2. 读取 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).

如果允许将 1 处的 executeUpdate 推迟到 2 处的读取之后，那么您会得到错误的答案（客户仍然存在）。

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.

您要么需要使用 JPA 读取实体，更新它们，然后让 Hibernate 生成更新 SQL（它可以批处理），要么直接调用 JDBC 来执行批处理更新。