openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Miłosz Tylenda <mtyle...@o2.pl>
Subject Re: "collection-valued-path" ArgumentException when querying based on multi-valued elements in an XML column
Date Sat, 30 Jan 2010 10:28:29 GMT
Hi Stella,

Thanks for posting the queries. Now I better understand your case - I was assuming something
simpler :)
Looks like doable still though.

I can currently see two approaches of handling your XPath expressions:

1. You write a JPQL query like this:

select d from Dummy d where
d.attributes.attribute.name = "complexAttribute1" and
d.attributes.attribute.attributes.attribute.name = "simpleAttribute1" and
d.attributes.attribute.attributes.attribute.value = "456"

which would give DB2 SQL similar to:

select * from Dummy where
xmlexists('$a/attributes/attribute[@name=$value0]'
passing attributes as "a", CAST (? AS VARCHAR(128)) as "value0") and
xmlexists('$a/attributes/attribute/attributes/attribute[@name=$value0]'
passing attributes as "a", CAST (? AS VARCHAR(128)) as "value0") and
xmlexists('$a/attributes/attribute/attributes/attribute[val=$value0]'
passing attributes as "a", CAST (? AS VARCHAR(128)) as "value0") 

However, such a query is similar to yours but not equivalent.

2. You place your XPath expression as XML column value in JPQL:

select d from Dummy d where
d = "/attributes/attribute[@name="complexAttribute1"]/attributes/attribute[@name="simpleAttribute1"]
[val="456"]"

which would give SQL very similar to yours, probably without ?-parameter.

The first approach looks simpler to implement but I guess you wouldn't benefit much from it.
Worries here are the "DECLARE @value0..." syntax for SQL Server and the fact OpenJPA might
not currently handle elements with same name at diffrent depths ("attribute" here).
The second approach seems much more flexible but is also more of unknown land. One can also
question its advantages over a native query.

What do you think? Would any of the above meet your requirements or you rather stick with
native queries?

Cheers,
Milosz


> Hi Milosz,
> 
> Sure, thanks for your help! Here are some sample native queries assuming a
> table called Dummy with an XML column called "attributes" that has the
> following schema:
> 
> <?xml version="1.0" encoding="utf-8"?>
> <xsd:schema version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
>     <xsd:element name="attributes" type="Attributes" />
>     <xsd:complexType name="Attributes">
>         <xsd:sequence>
>             <xsd:element minOccurs="0" maxOccurs="unbounded"
> name="attribute"
>                 type="Attribute" />
>         </xsd:sequence>
>     </xsd:complexType>
>     <xsd:complexType name="Attribute">
>         <xsd:sequence>
>             <xsd:choice minOccurs="1" maxOccurs="1">
>                 <xsd:element name="val" type="xsd:string" />
>                 <xsd:element name="attributes" type="Attributes" />
>             </xsd:choice>
>         </xsd:sequence>
>         <xsd:attribute name="name" type="xsd:string" use="required" />
>     </xsd:complexType>
> </xsd:schema>
> 
> An example of a value for the xml column would be:
> <attributes>
>   <attribute name="complexAttribute">
>     <attributes>
>       <attribute name="simpleAttribute1">
>          <value>456</value>
>       </attribute>
>       <attribute name="simpleAttribute2">
>          <value>789</value>
>       </attribute>
>     </attributes>
>   </attribute>
> </attributes>
> 
> A native query with the criterion that attribute with the name
> "simpleAttribute1" has a val of "456" would be expressed like this:
> 
> In DB2:
> SELECT * FROM Dummy o WHERE
> xmlexists('$a/attributes/attribute[@name="complexAttribute1"]/attributes/attribute[@name="simpleAttribute1"][val
> = $value0]' passing attributes as "a", CAST (? AS VARCHAR(128)) as "value0")
> 
> In SQL Server:
> DECLARE @value0 varchar(128) SET @value0=? SELECT * FROM Dummy o WHERE
> attributes.exist('/attributes/attribute[@name="complexAttribute1"]/attributes/attribute[@name="simpleAttribute1"][val
> = sql:variable("@value0")]') = 1
> 
> Let me know if you need any clarifications, thanks
> 
> Stella
> 
> On Sat, Jan 23, 2010 at 10:47 PM, Miłosz Tylenda <mtylenda@o2.pl> wrote:
> 
> > Hi Stella,
> >
> > It would help if I look at sample native queries you use for collection
> > valued paths. Can you post a few examples for DB2 and SQL Server?
> >
> > Regards,
> > Milosz
> >
> > > Hi Milosz,
> > >
> > > I am using DB2 9.5 and Microsoft SQL Server 2005.
> > > Thank you for creating the JIRA issue, I would be very interested in
> > future
> > > support for collection valued paths as I am currently falling back on
> > native
> > > queries for such paths.
> > >
> > > Stella
> > >
> > > On Fri, Jan 15, 2010 at 8:35 PM, Milosz Tylenda <mtylenda@o2.pl> wrote:
> > >
> > > > Hi Stella,
> > > >
> > > > I did some looking and created a JIRA issue [1] for possible further
> > work. I haven't found any workaround but there are chances for the
> > relaxation in future versions of OpenJPA.
> > > >
> > > > If you are still interested, what database are you using?
> > > >
> > > > Cheers,
> > > > Milosz
> > > >
> > > > [1] http://issues.apache.org/jira/browse/OPENJPA-1465
> > > >
> > > >
> > > > > Hello!
> > > > >
> > > > > I will look into this in the following weeks. If I am able to relax
> > the limitation or provide a workaround, I will report back here.
> > > >
> > > > >
> > > > > Greetings,
> > > > > Milosz
> > > > >
> > > > >
> > > > > > Hi Milosz,
> > > > > >
> > > > > > May I know if there is any workaround for this and if there
are
> > plans to
> > > > > > address it? Thank you for the reference!
> > > >
> > > > > >
> > > > > > On Tue, Dec 15, 2009 at 5:44 AM, Miłosz wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I guess this is an OpenJPA limitation - only queries over
> > single-valued
> > > > > > > elements are supported. There is a section on XML mapping
in the
> > user manual
> > > >
> > > > > > > [1].
> > > > > > >
> > > > > > > Regards,
> > > > > > > Milosz
> > > > > > >
> > > > > > > [1]
> > > > > > >
> > http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_xmlmapping
> > > >
> > > > > > >
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I am having trouble executing a JPA named query that
uses
> > multi-valued
> > > > > > > > elements, within an XML column, as criteria.
> > > >
> > > > > > > >
> > > > > > > > I have a table with the following DB2 schema:
> > > > > > > > CREATE TABLE SWD.Dummy (
> > > > > > > >         dummyId VARCHAR(32) NOT NULL,
> > > > > > > >         properties XML  NOT NULL
> > > >
> > > > > > > >     )
> > > > > > > >     DATA CAPTURE NONE;
> > > > > > > >
> > > > > > > > ALTER TABLE SWD.Dummy ADD CONSTRAINT Dummy_PK PRIMARY
KEY
> > (dummyId);
> > > > > > > >
> > > > > > > > and I used JAXB to generate the beans for marshalling
&
> > unmarshalling
> > > >
> > > > > > > from
> > > > > > > > the XSD schema defined for the "properties" XML column.
My XSD
> > schema is
> > > > > > > as
> > > > > > > > follows:
> > > > > > > >
> > > > > > > > <?xml version="1.0" encoding="utf-8"?>
> > > >
> > > > > > > > <xsd:schema version="1.0" xmlns:xsd="
> > http://www.w3.org/2001/XMLSchema">
> > > > > > > >     <xsd:element name="properties" type="propertiesType"
/>
> > > >
> > > > > > > >     <xsd:complexType name="propertiesType">
> > > > > > > >         <xsd:sequence>
> > > > > > > >             <xsd:element maxOccurs="unbounded"
name="property"
> > > >
> > > > > > > > type="propertyType" />
> > > > > > > >         </xsd:sequence>
> > > > > > > >     </xsd:complexType>
> > > > > > > >     <xsd:complexType name="propertyType">
> > > >
> > > > > > > >         <xsd:sequence>
> > > > > > > >             <xsd:element name="keyy" type="xsd:string"
/>
> > > > > > > >             <xsd:element name="valuee" type="xsd:string"
/>
> > > >
> > > > > > > >         </xsd:sequence>
> > > > > > > >     </xsd:complexType>
> > > > > > > > </xsd:schema>
> > > > > > > >
> > > > > > > >  In summary, the element "properties" is the root
element and
> > it contains
> > > >
> > > > > > > > any number of "property" elements which in turn may
contain
> > "keyy" and
> > > > > > > > "valuee" string elements. An example of an XML compliant
with
> > the schema:
> > > >
> > > > > > > >
> > > > > > > > <?xml version="1.0" encoding="UTF-16"
> > > > > > > >
> > > > > > >
> > ?><properties><property><keyy>abc</keyy><valuee>123</valuee></property><property><keyy>def</keyy><valuee>xyz</valuee></property></properties>
> > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > I am able to use JPA to make simple queries on the
Dummy
> > entity. However,
> > > > > > > I
> > > > > > > > get an error if I try to query on the "property" element.
> > > >
> > > > > > > > For example, I get an exception if I execute the following
JPQL
> > query:
> > > > > > > > SELECT d FROM Dummy d, IN (d.properties.property)
p WHERE
> > p.keyy=:keyy
> > > > > > > AND
> > > > > > > > p.valuee=:valuee
> > > >
> > > > > > > >
> > > > > > > > The stack trace that I get is:
> > > > > > > > com.example.DAOException: <openjpa-2.0.0-M3-r422266:822833
> > nonfatal user
> > > > > > > > error> org.apache.openjpa.persistence.ArgumentException:
> > > >
> > > > > > > > collection-valued-path
> > > > > > > >     at
> > com.example.DummyDAO.findDummyByProperty(DummyDAO.java:142)
> > > > > > > >     at
> > com.example.GetDummyService.invoke(GetDummyService.java:63)
> > > >
> > > > > > > >     ... 32 more
> > > > > > > > Caused by:
> > > > > > > > <openjpa-2.0.0-M3-r422266:822833 nonfatal user
error>
> > > > > > > > org.apache.openjpa.persistence.ArgumentException:
> > collection-valued-path
> > > >
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > org.apache.openjpa.kernel.exps.AbstractExpressionBuilder.traverseXPath(AbstractExpressionBuilder.java:269)
> > > > > > > >     at
> > > > > > > >
> > > >
> > > > > > >
> > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getPath(JPQLExpressionBuilder.java:1921)
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.addJoin(JPQLExpressionBuilder.java:741)
> > > >
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.evalFromClause(JPQLExpressionBuilder.java:680)
> > > > > > > >     at
> > > > > > > >
> > > >
> > > > > > >
> > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.evalFromClause(JPQLExpressionBuilder.java:666)
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getQueryExpressions(JPQLExpressionBuilder.java:292)
> > > >
> > > > > > > >     at
> > org.apache.openjpa.kernel.jpql.JPQLParser.eval(JPQLParser.java:67)
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.<init>(ExpressionStoreQuery.java:728)
> > > >
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > org.apache.openjpa.kernel.ExpressionStoreQuery.newDataStoreExecutor(ExpressionStoreQuery.java:170)
> > > > > > > >     at
> > > > > > > >
> > org.apache.openjpa.kernel.QueryImpl.createExecutor(QueryImpl.java:742)
> > > >
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > org.apache.openjpa.kernel.QueryImpl.compileForDataStore(QueryImpl.java:700)
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:682)
> > > >
> > > > > > > >     at
> > org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:582)
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > com.ibm.ws.persistence.EntityManagerImpl.createNamedQuery(EntityManagerImpl.java:104)
> > > >
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > com.ibm.ws.persistence.EntityManagerImpl.createNamedQuery(EntityManagerImpl.java:36)
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > com.ibm.ws.jpa.management.JPATxEmInvocation.createNamedQuery(JPATxEmInvocation.java:116)
> > > >
> > > > > > > >     at
> > > > > > > >
> > > > > > >
> > com.ibm.ws.jpa.management.JPAEntityManager.createNamedQuery(JPAEntityManager.java:332)
> > > > > > > >     at
> > com.example.DummyDAO.findDummyByProperty(DummyDAO.java:137)
> > > >
> > > > > > > >     ... 33 more
> > > > > > > >
> > > > > > > > I've tweaked the query many times, but am unable to
get it
> > working. I
> > > > > > > > couldn't find resources online regarding this either,
so I
> > would greatly
> > > >
> > > > > > > > appreciate any help on this, thank you!
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
> 

Mime
View raw message