java - 组合 key 的JPQL异常

我不知道2 JPQL Exception的解决方案


子查询只能返回一列
此表达式中的[PARENTENTITY.PARENTID2]中有一个无效的表
这种情况
对于以下实体-

    class ParentEntity{
      @Id
      String parentId1;
      @Id
      String parentId2;
      @ManyToOne
      ChildEntity childEntityRef;
    }

    class ChildEntity {
      @Id
      String childId1;
      @Id
      String childId2;
    }

当我尝试查询-
 SELECT me.childId1,me.childId2 FROM ChildEntity as me where me <> ALL(Select pe.childEntityRef From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 )
它给出以下异常-

Caused by: Exception [EclipseLink-6069] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.QueryException
Exception Description: The field [PARENTENTITY.PARENTID2] in this expression has an invalid table in this context.
    at org.eclipse.persistence.exceptions.QueryException.invalidTableForFieldInExpression(QueryException.java:712)
    at org.eclipse.persistence.internal.expressions.FieldExpression.validateNode(FieldExpression.java:277)
    at org.eclipse.persistence.expressions.Expression.normalize(Expression.java:2978)
    at org.eclipse.persistence.internal.expressions.DataExpression.normalize(DataExpression.java:342)
    at org.eclipse.persistence.internal.expressions.FieldExpression.normalize(FieldExpression.java:205)
    at org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:218)

当我尝试查询-
 SELECT me.childId1,me.childId2 FROM ChildEntity as me where me <> (Select pe.childEntityRef From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 )
它给出以下异常-

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: subquery must return only one column
Error Code: 0
Call: SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE ( <> (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2 LEFT OUTER JOIN CHILDENTITY t1 ON ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1)) WHERE ((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?))))
    bind => [2 parameters bound]
Query: ReportQuery(referenceClass=ChildEntity sql="SELECT t0.CHILDID1, t0.CHILDID2 FROM CHILDENTITY t0 WHERE ( <> (SELECT t1.CHILDID1, t1.CHILDID2 FROM PARENTENTITY t2 LEFT OUTER JOIN CHILDENTITY t1 ON ((t1.CHILDID2 = t2.CHILDID2) AND (t1.CHILDID1 = t2.CHILDID1)) WHERE ((t2.PARENTID1 = ?) AND (t2.PARENTID2 = ?))))")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
Caused by: org.postgresql.util.PSQLException: ERROR: subquery must return only one column
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)

最佳答案

尝试使用2.4或2.5版本时，JPQL进行了很多增强和修复。尽管您尝试执行的操作仍然无法实现。

尝试简化查询，

SELECT me.childId1,me.childId2 FROM ChildEntity as me where not exists (Select pe.parentId1 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 and pe.childEntityRef = me)

要么，

SELECT me.childId1,me.childId2 FROM ChildEntity as me where not exists (Select pe.parentId1 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 and pe.childEntityRef.childId1 = me.childId1 and pe.childEntityRef.childId2 = me.childId2)

在2.5中，您也应该能够做到，

SELECT me.childId1,me.childId2 FROM ChildEntity as me where (me.childId1, mew.childId2) NOT IN (Select pe.childEntityRef.childId1, pe.childEntityRef.childId2 From ParentEntity as pe where pe.parentId1=:parentId1 and pe.parentId2=:parentId2 )

关于java - 组合 key 的JPQL异常，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17132549/