Eclipse Community Forums: EclipseLink » JPQL Composite Key in Dynamic Persistence

Help

Home

Home » Eclipse Projects » EclipseLink » JPQL Composite Key in Dynamic Persistence

Show: Today's Messages :: Show Polls :: Message Navigator

JPQL Composite Key in Dynamic Persistence [message #1063885]

Fri, 14 June 2013 18:18

Gaurav Gupta

Messages: 9
Registered: June 2013
Location: India

Junior Member

JPQL query for ManyToOne Unidirectional and Bidirectional Relationship

//Code shows only just mapping [actually implemented using Dynamic Persistent]

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

        class ChildEntity {  //ManyToOne Unidirectional Relationship case
          @Id
          String childId1;  //composite key
          @Id
          String childId2;
        }

        class ChildEntity {  //ManyToOne Bidirectional Relationship case
          @Id
          String childId1;  //composite key
          @Id
          String childId2;
          @OneToMany
          List<ParentEntity> parentEntitiesref;
        }

   _______________________________________________________________________
    ||        ParentEntity  Table              ||    ChildEntity Table     ||
    ||_________________________________________||__________________________||
    parentId1|parentId2|childId1_FK|childId1_FK|| childId1| childId2       ||
    ||_______|_________|___________|___________||_________|________________||
    || p11   |p21      | c11       |c21        ||   c11   |    c21         ||
    || p12   |p22      | c11       |c21        ||   c12   |    c22         ||
    || p13   |p23      | c12       |c22        ||   c13   |    c23         ||
    || p14   |p24      | c12       |c22        ||         |                ||
    ||_______|_________|___________|___________||_________|________________||

What will JPQL query to access List of all childEntity that are not connected to particular ParentEntity (in both case Unidirectional and Bidirectional Relationship).

Example -
For ParentEntity[p11,p21] get All other disconnected List of ChildEntity([c12,c22],[c13,c23])

UPDATE: Query in single primary case (non composite key)-

select c from ChildEntity c where c.childId != (
    select p.childEntityRef.childId from ParentEntity p where p.parentId = :parentId)

But i unable call this query in Composite key case - where inner query will be like -

 select p.childEntityRef.childId1,p.childEntityRef.childId2 from ParentEntity p where p.parentId1 = :parentId1 and p.parentId2 = :parentId2

but don,t know how to compare(not equal with two variable) p.childEntityRef.childId1,p.childEntityRef.childId2 with c.childId1 and c.childId2 in outer query .

Best Regards,
Gaurav Gupta.

[Updated on: Fri, 14 June 2013 19:35]

Report message to a moderator

Re: JPQL Composite Key in Dynamic Persistence [message #1063928 is a reply to message #1063885]

Sun, 16 June 2013 06:11

Gaurav Gupta

Messages: 9
Registered: June 2013
Location: India

Junior Member

I have tried with these query but gives following exception ?

SELECT me.childId1,me.childId2,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 )

FINEST: Execute query ReportQuery(referenceClass=ChildEntity sql="SELECT t0.childId1, t0.childId2, t0.childId1, t0.childId2 FROM ChildEntity t0 WHERE ( <> (SELECT t1.childId1, t1.childId2 FROM ParentEntity t2 LEFT OUTER JOIN ChildEntity t1 ON ((t1.childId1 = t2.childId1_ChildEntity_FK) AND (t1.childId2 = t2.childId2_ChildEntity_FK)) WHERE ((t2.parentId1 = ?) AND (t2.parentId2 = ?))))")
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

SELECT me.childId1,me.childId2,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 )

Exception [EclipseLink-6069] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.QueryException
Exception Description: The field [ParentEntity.parentId1] 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)

Please provide me some guidance on this ...

[Updated on: Sun, 16 June 2013 06:28]

Report message to a moderator