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;
} 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 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
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.03290 seconds