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/attribu...@name=$value0]'
passing attributes as "a", CAST (? AS VARCHAR(128)) as "value0") and
xmlexists('$a/attributes/attribute/attributes/attribu...@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/attribu...@name="complexAttribute1"]/attributes/attribu...@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/attribu...@name="complexAttribute1"]/attributes/attribu...@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/attribu...@name="complexAttribute1"]/attributes/attribu...@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 <[email protected]> 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 <[email protected]> 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!
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
>
