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!
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > >
> >
>
|