I have seen some relationship query examples using Toplink and named queries.
I am having trouble with using Expressions and ExpressionBuilder to create a
desired query that has left outer joins.
My table structure is like this (simplified) -
ParentItem table
- pId : String (PK)
- name : String
- pAttributes : Collection (one-to-many) - to ParentItemAttribute
table
- pObjects : Collection (one-to-many) - to ParentObject table
ParentItemAttribute table
- pattrId : int (PK)
- pId : String (FK to ParentItem table)
- attrName : String
- attrValue : String
ParentObject table
- pobjId : int (PK)
- pId : String (FK to ParentItem table)
- type : String
- poAttributes : Collection (many-to-many) - to ObjectItemAttribute
table
ObjectItemAttribute table
- oattrId : int (PK)
- pobjId : int (FK to ParentObject table)
- attrName : String
- attrValue : String
So the structure of the JPA entity classes it that the top-level entity is
ParentItem.
ParentItem has a one-to-many relationship to ParentItemAttribute entity.
ParentItem also has a one-to-many relationship to ParentObject entity.
ParentObject has a one-to-many relationship to ObjectItemAttribute entity.
The insert, update and delete operations all work with these entities.
I would like to have a query that will retrieve ParentItem objects based on
this query:
SELECT ParentItem.pId, ParentItem.name
FROM ParentItem LEFT OUTER JOIN ParentItemAttribute on ParentItem.pId =
ParentItemAttribute.pId LEFT OUTER JOIN ParentObject on ParentItem.pId =
ParentObject.pId LEFT OUTER JOIN ObjectItemAttribute on ParentObject.pobjId
= ObjectItemAttribute.pobjId
WHERE (ParentItemAttribute.attrName = ? OR ObjectItemAttribute.attrName = ?)
AND ParentItem.pId = ?
I wasn't able to get a LEFT OUTER JOIN to work correctly using JPA QL. I
couldn't solve the JOIN FETCH syntax. From what I can tell from the Oracle
Toplink Developers Guide PDF document, the way to build queries with joins
the way I am trying is to use the Toplink Expressions.
In that attempt I have got kind of close. I have tried things like this -
String parentItemId = "test001";
String testAttrName = "name123";
Expression masterExp = null;
ExpressionBuilder piExb = new ExpressionBuilder(ParentItem.class);
Expression piExp = piExb.get("pId").equal(parentItemId);
Expression attrExp =
piExb.anyOfAllowingNone("pAttributes").get("attrName").equal(testAttrName);
ExpressionBuilder objSEB = new ExpressionBuilder();
ReportQuery aSubQuery = new ReportQuery(ParentObject.class, objSEB);
aSubQuery.addAttribute("poAttributes");
aSubQuery.setSelectionCriteria(objSEB.anyOfAllowingNone("poAttributes").get("attrName").equal(testAttrName);
Expression objExp =
piExb.anyOfAllowingNone("pObjects").equal(objSEB.subQuery(aSubQuery));
attrExp = attrExp.or(objExp);
masterExp = piExp.and(attrExp);
ReadAllQuery raQ = new ReadAllQuery(ParentItem.class);
raQ.setSelectionCriteria(masterExp);
raQ.prepareForExecution();
Session session = ( (oracle.toplink.essentials.ejb.cmp3.EntityManager)
em.getDelegate()).getActiveSession();
if ( session != null ) {
ArrayList list = new ArrayList();
List<ParentItem> results = (List)session.executeQuery(raQ);
if ( results != null ) {
for ( Iterator it = results.iterator();it.hasNext(); ) {
ParentItem pi = (ParentItem)it.next();
if ( pi != null ) {
// do something with the ParentItem
}
}
} else {
// display something about not finding any results
}
} else {
// throw an exception
}
The resulting query fails with the following error -
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b09d-fcs
(12/06/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
for JDBC][SQLServer]Line 1: Incorrect syntax near '='.
Error Code: 170
So my join is not correct as can be seen in the Toplink generated SQL
statement:
SELECT DISTINCT t0.pId, t0.name
FROM ParentItem t0
LEFT OUTER JOIN ParentItemAttribute t1 ON (t1.pId = t0.pId)
LEFT OUTER JOIN ParentObject t2 ON (t2.pId = t0.pId)
WHERE ((t0.pId = ?) AND ((t1.attrName = ?) OR ( = (SELECT DISTINCT
t3.oattrId, t3.pobjId, t3.attrName, t3.attrValue FROM ParentObject t4,
ObjectItemAttribute t3 WHERE ((t3.attrName = ?) AND (t3.pobjId = t4.pobjId)
))))
bind => [test001, name123]
I only included this attempt as it is one of many combinations I have tried.
Has anyone built this type of join query with Toplink and is this the right
approach? Maybe I should just create a view for the data I am trying to get
and then create a JPA entity class to query the view?
Thanks for any help offered.
--
View this message in context: http://www.nabble.com/Looking-for-Help-on-Relationship-Query-Using-Expressions-tp19302691p19302691.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.