Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPQL Composite Key in Dynamic Persistence
JPQL Composite Key in Dynamic Persistence [message #1063885] Fri, 14 June 2013 14:18 Go to next message
Gaurav Gupta is currently offline 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;
        }
or
        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 15: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 02:11 Go to previous messageGo to next message
Gaurav Gupta is currently offline 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 02:28]

Report message to a moderator

Re: JPQL Composite Key in Dynamic Persistence [message #1064287 is a reply to message #1063928] Tue, 18 June 2013 10:42 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Try the 2.4 or 2.5 release, and use a NOT EXISTS instead of ALL.

James : Wiki : Book : Blog : Twitter
Re: JPQL Composite Key in Dynamic Persistence [message #1064519 is a reply to message #1064287] Wed, 19 June 2013 11:46 Go to previous message
Gaurav Gupta is currently offline Gaurav Gupta
Messages: 9
Registered: June 2013
Location: India
Junior Member
Thanks for your response , problem resolved Smile

[Updated on: Wed, 19 June 2013 11:47]

Report message to a moderator

Previous Topic:jaxb marshalling unmarshalling exception in jboss servlet
Next Topic:InheritancePolicy SingleTableStrategy in Dynamic Persistence
Goto Forum:
  


Current Time: Thu Jul 31 09:47:07 EDT 2014

Powered by FUDForum. Page generated in 0.02224 seconds