In TopLink you would write the query as :
ExpressionBuilder builder = new ExpressionBuilder();
ReportQuery query = new ReportQuery(ParentItem.class, builder);
query.addItem("pId", builder.get("pId"));
query.addItem("name", builder.get("name"));
Expression pAttributes =
builder.anyOfAllowingNone("pAttributes").get("attrName").equal(builder.getParameter("attrName"));
Expression oAttributes =
builder.anyOfAllowingNone("pObjects").anyOfAllowingNone("poAttributes").get("attrName").equal(builder.getParameter("attrName"));
//Not sure about this part as this will limit the results to one
item or null
Expression parentId =
builder.get("pId").equal(builder.getParameter("pId"));
query.setSelectionCriteria(pAttributes.or(oAttributes).and(parentId));
query.addArgument("attrName");
query.addArgument("pId");
Vector parameters = new Vector(2);
parameters.add("name123");
parameters.add("test001");
session.executeQuery(query, parameters);
but why not use JPQL?
Select p.pId, p.name from ParentItem p LEFT JOIN p.pattributes as
pAttr LEFT JOIN p.oObjects as o LEFT JOIN o.poAttributes as poAttr where
(pAttr.attrName = :attrName OR poAttr.attrName = :attrName) AND p.pId = :pId
--Gordon
sonavor wrote:
> 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.
>
>