Thanks for the help Gordon.
I had been incorrectly trying JPQL queries using some bad syntax. I tried
your example and it does provide the SQL result I wanted. I still have a
problem that might prevent me from using the JPQL query though - I would
like to modify the the query to use an array of attribute name values as the
attrName query parameter instead of just a single attrName parameter. From
what I have found in reading about JPA queries that is not supported. Is
that right? So trying to use a WHERE clause like - WHERE pAttr.attrName IN
( :attrNameArray )
isn't supported?
The ExpressionBuilder allows me to iterate through an array parameter and
append to an Expression object using an "or" join.
I haven't tested out your ExpressionBuilder example yet but plan to do that
next and will post my results with that test. In the meantime I did break
apart my search routine into more than one query using ExpressionBuilder.
That produces the end result I want but might not be the most efficient.
Thanks again,
John
Gordon Yorke-2 wrote:
>
> 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.
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Looking-for-Help-on-Relationship-Query-Using-Expressions-tp19302691p19341601.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.