Hibernate HQL，在同一查询中执行多个更新语句 [英] Hibernate hql, execute multiple update statements in same query

问题描述

我想在休眠Hql的同一查询中执行多个更新语句. 如下所示:

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

在同一executeUpdate调用中，我想更新表1中的记录并从表2中删除记录.

有可能吗?

解决方案

简而言之，您正在寻找的东西类似于JDBC中的批处理. Hibernate并未提供Thich进行批量更新查询，因此我怀疑是否会考虑将其用于Hibernate.

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

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

仅举一个使用伪代码的示例:

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

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

我怀疑您认为您需要批处理功能，因为您正在执行类似的操作(根据您的示例，您使用批量更新来存储单个记录).但是在Hibernate/JPA中应该怎么做

(实际上最好通过存储库包装持久层访问，这里我只是简化图片)

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

这样做，Hibernate具有足够的智能来检测已更改/已删除/已插入的实体，并利用JDBC批处理在flush()处执行DB CUD操作.更重要的是，这是ORM的全部目的:我们希望提供行为丰富的实体以供使用，对于它们，实体的内部状态变化可以透明地"反映在持久性存储中.

HQL批量更新旨在用于其他用途，这类似于对数据库的一次批量更新会影响很多记录，例如:

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

在这种使用情况下几乎不需要执行大量查询，因此，数据库往返的开销微不足道，因此对批量更新查询的好处和批处理处理的支持也很少.

我不能忽略某些情况，您确实需要发出很多更新查询，而这些查询不适合有意义的实体行为来完成.在这种情况下，您可能需要重新考虑Hibernate是否是正确的工具.您可以考虑在这种用例中使用纯JDBC，这样您就可以控制如何发出查询.

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?

解决方案

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.

这篇关于Hibernate HQL，在同一查询中执行多个更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！