我有一个简单的实体(为了简洁起见,建议使用一些不相关的字段):
@Entity
@Table(name = "tenant")
public class Tenant {
@Id
private String id;
@Version @Column
private long version = 0;
@Column(name = "init_in_progress")
private Boolean initializationInProgress;
}
我想对所有租户进行批量更新,方法是使用JPA update查询将
true
设置为initializationInProgress
,如下所示: entityManager.createQuery(
"UPDATE Tenant t " +
"SET t.initializationInProgress = :ip")
.setParameter("ip", initializationInProgress)
.executeUpdate();
这对
EclipseLink 2.2.1
很有用。但是,当我对version2.3.3
进行相同的尝试时,会得到一个错误:Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.3.v20120629-r11760): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression
Error Code: 0
Call: UPDATE tenant SET version = (SELECT (version + ?) FROM tenant WHERE ID = tenant.ID), init_in_progress = ?
bind => [1, true]
Query: UpdateAllQuery(referenceClass=Tenant sql="UPDATE tenant SET version = (SELECT (version + ?) FROM tenant WHERE ID = tenant.ID), init_in_progress = ?")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1494)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:838)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:906)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:592)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:253)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:236)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.updateAll(DatasourceCallQueryMechanism.java:789)
at org.eclipse.persistence.queries.UpdateAllQuery.executeDatabaseQuery(UpdateAllQuery.java:153)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:743)
at org.eclipse.persistence.queries.ModifyAllQuery.executeInUnitOfWork(ModifyAllQuery.java:145)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1463)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeUpdate(EJBQueryImpl.java:540)
at com.example.dao.impl.tenant.TenantRepositoryImpl.updateAllTenantsInitializationStatus(TenantRepositoryImpl.java:100)
有人熟悉这个虫子吗?任何已知的解决方法/修复(除了不更改
EclipseLink
版本之外)?谢谢你的帮助。顺便说一下,我用的是
PostgreSQL 9.1
。 最佳答案
这最初看起来像是一个数据问题,在tenant
中有多个具有相同ID的行。。。但实际上这是eclipseline查询的一个问题。因为这是一个生成的查询,所以您的手上似乎有一个(严重的)eclipselinebug。
更新:请参见this EclipseLink bugzilla entry for bug 393223。
查询应该将内部引用真正别名为tenant
,例如:
UPDATE tenant SET version = (SELECT (t2.version + ?) FROM tenant t2 WHERE t2.ID = tenant.ID), init_in_progress = ?
未能添加此别名意味着
ID = tenant.ID
意味着tenant.ID = tenant.ID
。。。这总是正确的,因此子查询匹配tenant
的每一行。观看演示:
CREATE TABLE tenant ( ID integer primary key, version integer );
INSERT INTO tenant ( id, version ) values (1,0), (2,0), (3,0);
BEGIN;
PREPARE testq(integer) AS
UPDATE tenant SET version = (SELECT (version + $1) FROM tenant WHERE ID = tenant.ID);
regress=> EXECUTE testq(1);
ERROR: more than one row returned by a subquery used as an expression
ROLLBACK;
更正的查询:
BEGIN;
PREPARE testq2(integer) AS
UPDATE tenant SET version = (SELECT (version + $1) FROM tenant t2 WHERE t2.ID = tenant.ID);
regress=> EXECUTE testq2(1);
UPDATE 3
ROLLBACK;
这似乎是一个日食线虫。除了作为本地SQL进行大容量更新之外,我看不出在代码中可以做什么。
关于java - EclipseLink 2.3.3无法更新PostgreSQL上的版本化实体,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/13297567/