Hibernate-查询更新后如何检索受影响的ID列表 [英] Hibernate - How to retrieve affected IDs list after query update

问题描述

我检查了此解决方案，但不适用于我的情况- https://stackoverflow.com/a/47987528/351903

I checked this solution but it did not work for my case - https://stackoverflow.com/a/47987528/351903

我正在使用Mysql.

I am working with Mysql.

如果我执行以下操作-

Session session = em.unwrap(Session.class);
    
    Query query = em.createNativeQuery("UPDATE Table tr SET tr.status=:newStatus WHERE tr.status=:oldStatus and tr.processAfter<now()");
    query.setParameter("newStatus", 0);
    query.setParameter("oldStatus",1);
    query.executeUpdate();
List<Table> empList = query.list();  //I get undefined method error - Error:(513, 57) java: cannot find symbol symbol:   method list()  location: variable query of type javax.persistence.Query


    List<Table> empList = query.getResultList(); //can't do this on dml case

我通过getResultList()得到此异常-

I get this exception with getResultList() -

2020-07-09 21:53:46:850 [Thread-10][] DEBUG org.hibernate.engine.jdbc.spi.SqlExceptionHelper[line:124] [] - could not extract ResultSet [n/a]
java.sql.SQLException: Can not issue data manipulation statements with executeQuery().
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.45.jar:5.1.45]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java-5.1.45.jar:5.1.45]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java-5.1.45.jar:5.1.45]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java-5.1.45.jar:5.1.45]
        at com.mysql.jdbc.StatementImpl.checkForDml(StatementImpl.java:469) ~[mysql-connector-java-5.1.45.jar:5.1.45]
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1923) ~[mysql-connector-java-5.1.45.jar:5.1.45]
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-2.7.8.jar:?]
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.8.jar:?]
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.getResultSet(Loader.java:2168) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.doQuery(Loader.java:938) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2692) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2675) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.Loader.list(Loader.java:2502) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2161) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at org.hibernate.query.Query.getResultList(Query.java:146) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
        at com.project.service.Service.Method(Service.java:514) [classes/:?]
        

推荐答案

如果您正在使用支持此功能的数据库，则可以使用Blaze-Persistence，它是在JPA/Hibernate之上运行的库，该库增加了对JPA/Hibernate的支持.这个.

If you were working with a database that supports this, you could use Blaze-Persistence, a library that works on top of JPA/Hibernate, which adds support for this.

有关此的更多信息:由于您使用的是MySQL，我认为做到这一点的最佳方法如下:

Since you are using MySQL, I think the best way to do this is the following:

1. 创建一个SELECT查询，以获取受影响对象的ID，并使用悲观锁定
2. 根据ID进行更新
3. (可选)根据ID提取所需的任何数据

不幸的是，那是MySQL可以做到的最好的事情.在某个时候，Blaze-Persistence将添加一个实现该方案的仿真，因此您不必考虑如何实现，但是性能模型仍会与您期望的不同.

That's the best that can be done with MySQL unfortunately. At some point, Blaze-Persistence will add an emulation that implements this scheme, so you don't have to think about how to do it, but still, the performance model will be different than what you'd expect.

这篇关于Hibernate-查询更新后如何检索受影响的ID列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！