questiona about JPQL and Collections
|
View:
New views
5 Messages
—
Rating Filter:
Alert me
|
 |
questiona about JPQL and Collections
by Daniel.Stucky
::
Rate this Message:
Hi all,
given the following Entities, how can I express a named query that selects all Records where AttributeDao._name = "X" and AttributeDao._values.contains("Y") @Entity @Table(name = "RECORDS") public class RecordDao implements Serializable { @Id @Column(name = "ID") private String _idString; @Column(name = "SOURCE") private String _source; ... } @Entity @Table(name = "ATTRIBUTES") public class AttributeDao { @Id @GeneratedValue(strategy=GenerationType.AUTO) @Column(name = "ATT_ID") private String _id; @Column(name = "ATT_NAME") private String _name; @BasicCollection ( fetch=FetchType.EAGER, valueColumn=@Column(name="ATT_VALUE")) @CollectionTable ( name="ATTRIBUTE_VALUES", primaryKeyJoinColumns= {@PrimaryKeyJoinColumn(name="ATT_ID", referencedColumnName="ATT_ID")} ) private List<String> _values; ... } I tried something like this @NamedQueries({ @NamedQuery(name="RecordDao.findByAttribute", query="SELECT DISTINCT r FROM RecordDao r JOIN r._attributes a JOIN a._values v WHERE a._name = :name AND v._values = :value") }) but during EntityManager creation I get the following error: Exception [EclipseLink-8030] (Eclipse Persistence Services - 1.0.2 (Build 20081024)): org.eclipse.persistence.exceptions.JPQLException Exception Description: Error compiling the query [RecordDao.findByAttribute: SELECT DISTINCT r FROM RecordDao r JOIN r._attributes a JOIN a._values v WHERE a._name = :name AND v._values = :value], line 1, column 63: unknown state or association field [_values] of class [org.eclipse.smila.datamodel.persistence.AttributeDao]. I expect the syntax for "AND v._values = :value" to be not valid, but I wonder why it complains about JOIN a._values v. Anyone any ideas/suggestions ? Bye, Daniel _______________________________________________ eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: questiona about JPQL and Collections
by tware
::
Rate this Message:
Hi Daniel,
Because BasicCollection is not a part of the JPA specification yet, JPQL cannot currently query across BasicCollections. (That behavior is expected in the upcoming JPA 2.0 specification) To access a BasicCollection in a query, you will have to use EclipseLink's criteria API instead. Your code will look something like this: ReadAllQuery query = new ReadAllQuery(RecordDAO.class); ExpressionBuilder record = new ExpressionBuilder(); Expression attributes = record.anyOfAttributes("attributes"); Expresion criteria = attributes.get("_name").equal(record.getParameter("name")); criteria = criteria.and(attributes.anyOf("_values").equal(record.getParameter("value"))) query.setSelectionCriteria(criteria); This query can be added as a named query through a SessionCustomizer by calling the addQuery(name, query) method. That is the basics of how it should look. With a little massaging of the code, it should be possible to use this query just like any other named query in JPA. BTW: Here is the Customizer doc: http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Using_EclipseLink_JPA_Extensions_for_Customization_and_Optimization -Tom Daniel.Stucky@... wrote: > Hi all, > > given the following Entities, how can I express a named query that > selects all Records where AttributeDao._name = "X" and > AttributeDao._values.contains("Y") > > > @Entity > @Table(name = "RECORDS") > public class RecordDao implements Serializable { > > @Id > @Column(name = "ID") > private String _idString; > > @Column(name = "SOURCE") > private String _source; > ... > } > > @Entity > @Table(name = "ATTRIBUTES") > public class AttributeDao { > > @Id > @GeneratedValue(strategy=GenerationType.AUTO) > @Column(name = "ATT_ID") > private String _id; > > @Column(name = "ATT_NAME") > private String _name; > > @BasicCollection ( > fetch=FetchType.EAGER, > valueColumn=@Column(name="ATT_VALUE")) > @CollectionTable ( > name="ATTRIBUTE_VALUES", > primaryKeyJoinColumns= > {@PrimaryKeyJoinColumn(name="ATT_ID", > referencedColumnName="ATT_ID")} > ) > private List<String> _values; > ... > } > > > > I tried something like this > > @NamedQueries({ > @NamedQuery(name="RecordDao.findByAttribute", > query="SELECT DISTINCT r FROM RecordDao r JOIN r._attributes a > JOIN a._values v WHERE a._name = :name AND v._values = :value") > > }) > > > but during EntityManager creation I get the following error: > > Exception [EclipseLink-8030] (Eclipse Persistence Services - 1.0.2 > (Build 20081024)): org.eclipse.persistence.exceptions.JPQLException > Exception Description: Error compiling the query > [RecordDao.findByAttribute: SELECT DISTINCT r FROM RecordDao r JOIN > r._attributes a JOIN a._values v WHERE a._name = :name AND v._values = > :value], line 1, column 63: unknown state or association field [_values] > of class [org.eclipse.smila.datamodel.persistence.AttributeDao]. > > > I expect the syntax for "AND v._values = :value" to be not valid, but I > wonder why it complains about JOIN a._values v. > Anyone any ideas/suggestions ? > > > Bye, > Daniel > _______________________________________________ > eclipselink-users mailing list > eclipselink-users@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
AW: questiona about JPQL and Collections
by Daniel.Stucky
::
Rate this Message:
Hi Tom,
thanks for your help, in general it works as described by you. However, I forgot to include the following member of RecordDao in the sample code: @Column(name = "RECORD") private byte[] _serializedRecord; The generated SQL query contains DISTINCT, which is not usable with queries containing BLOBs. Any suggestions how to handle this ? Bye, Daniel > -----Ursprüngliche Nachricht----- > Von: eclipselink-users-bounces@... [mailto:eclipselink-users- > bounces@...] Im Auftrag von Tom Ware > Gesendet: Mittwoch, 28. Januar 2009 21:12 > An: EclipseLink User Discussions > Betreff: Re: [eclipselink-users] questiona about JPQL and Collections > > Hi Daniel, > > Because BasicCollection is not a part of the JPA specification yet, > JPQL > cannot currently query across BasicCollections. (That behavior is > expected in > the upcoming JPA 2.0 specification) > > To access a BasicCollection in a query, you will have to use > EclipseLink's > criteria API instead. > > Your code will look something like this: > > ReadAllQuery query = new ReadAllQuery(RecordDAO.class); > ExpressionBuilder record = new ExpressionBuilder(); > Expression attributes = record.anyOfAttributes("attributes"); > Expresion criteria = > attributes.get("_name").equal(record.getParameter("name")); > criteria = > criteria.and(attributes.anyOf("_values").equal(record.getParameter("val > ue"))) > query.setSelectionCriteria(criteria); > > This query can be added as a named query through a SessionCustomizer by > calling > the addQuery(name, query) method. > > That is the basics of how it should look. With a little massaging > of the > code, it should be possible to use this query just like any other named > query in > JPA. > > BTW: Here is the Customizer doc: > > http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Usi > ng_EclipseLink_JPA_Extensions_for_Customization_and_Optimization > > -Tom > > > > > Daniel.Stucky@... wrote: > > Hi all, > > > > given the following Entities, how can I express a named query that > > selects all Records where AttributeDao._name = "X" and > > AttributeDao._values.contains("Y") > > > > > > @Entity > > @Table(name = "RECORDS") > > public class RecordDao implements Serializable { > > > > @Id > > @Column(name = "ID") > > private String _idString; > > > > @Column(name = "SOURCE") > > private String _source; > > ... > > } > > > > @Entity > > @Table(name = "ATTRIBUTES") > > public class AttributeDao { > > > > @Id > > @GeneratedValue(strategy=GenerationType.AUTO) > > @Column(name = "ATT_ID") > > private String _id; > > > > @Column(name = "ATT_NAME") > > private String _name; > > > > @BasicCollection ( > > fetch=FetchType.EAGER, > > valueColumn=@Column(name="ATT_VALUE")) > > @CollectionTable ( > > name="ATTRIBUTE_VALUES", > > primaryKeyJoinColumns= > > {@PrimaryKeyJoinColumn(name="ATT_ID", > > referencedColumnName="ATT_ID")} > > ) > > private List<String> _values; > > ... > > } > > > > > > > > I tried something like this > > > > @NamedQueries({ > > @NamedQuery(name="RecordDao.findByAttribute", > > query="SELECT DISTINCT r FROM RecordDao r JOIN r._attributes a > > JOIN a._values v WHERE a._name = :name AND v._values = :value") > > > > }) > > > > > > but during EntityManager creation I get the following error: > > > > Exception [EclipseLink-8030] (Eclipse Persistence Services - 1.0.2 > > (Build 20081024)): org.eclipse.persistence.exceptions.JPQLException > > Exception Description: Error compiling the query > > [RecordDao.findByAttribute: SELECT DISTINCT r FROM RecordDao r JOIN > > r._attributes a JOIN a._values v WHERE a._name = :name AND v._values > = > > :value], line 1, column 63: unknown state or association field > [_values] > > of class [org.eclipse.smila.datamodel.persistence.AttributeDao]. > > > > > > I expect the syntax for "AND v._values = :value" to be not valid, but > I > > wonder why it complains about JOIN a._values v. > > Anyone any ideas/suggestions ? > > > > > > Bye, > > Daniel > > _______________________________________________ > > eclipselink-users mailing list > > eclipselink-users@... > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users > _______________________________________________ > eclipselink-users mailing list > eclipselink-users@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
Re: AW: questiona about JPQL and Collections
by tware
::
Rate this Message:
Try calling readAllQuery.dontUseDistinct().
-Tom Daniel.Stucky@... wrote: > Hi Tom, > > thanks for your help, in general it works as described by you. > > However, I forgot to include the following member of RecordDao in the sample code: > @Column(name = "RECORD") > private byte[] _serializedRecord; > > The generated SQL query contains DISTINCT, which is not usable with queries containing BLOBs. > Any suggestions how to handle this ? > > > Bye, > Daniel > >> -----Ursprüngliche Nachricht----- >> Von: eclipselink-users-bounces@... [mailto:eclipselink-users- >> bounces@...] Im Auftrag von Tom Ware >> Gesendet: Mittwoch, 28. Januar 2009 21:12 >> An: EclipseLink User Discussions >> Betreff: Re: [eclipselink-users] questiona about JPQL and Collections >> >> Hi Daniel, >> >> Because BasicCollection is not a part of the JPA specification yet, >> JPQL >> cannot currently query across BasicCollections. (That behavior is >> expected in >> the upcoming JPA 2.0 specification) >> >> To access a BasicCollection in a query, you will have to use >> EclipseLink's >> criteria API instead. >> >> Your code will look something like this: >> >> ReadAllQuery query = new ReadAllQuery(RecordDAO.class); >> ExpressionBuilder record = new ExpressionBuilder(); >> Expression attributes = record.anyOfAttributes("attributes"); >> Expresion criteria = >> attributes.get("_name").equal(record.getParameter("name")); >> criteria = >> criteria.and(attributes.anyOf("_values").equal(record.getParameter("val >> ue"))) >> query.setSelectionCriteria(criteria); >> >> This query can be added as a named query through a SessionCustomizer by >> calling >> the addQuery(name, query) method. >> >> That is the basics of how it should look. With a little massaging >> of the >> code, it should be possible to use this query just like any other named >> query in >> JPA. >> >> BTW: Here is the Customizer doc: >> >> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Usi >> ng_EclipseLink_JPA_Extensions_for_Customization_and_Optimization >> >> -Tom >> >> >> >> >> Daniel.Stucky@... wrote: >>> Hi all, >>> >>> given the following Entities, how can I express a named query that >>> selects all Records where AttributeDao._name = "X" and >>> AttributeDao._values.contains("Y") >>> >>> >>> @Entity >>> @Table(name = "RECORDS") >>> public class RecordDao implements Serializable { >>> >>> @Id >>> @Column(name = "ID") >>> private String _idString; >>> >>> @Column(name = "SOURCE") >>> private String _source; >>> ... >>> } >>> >>> @Entity >>> @Table(name = "ATTRIBUTES") >>> public class AttributeDao { >>> >>> @Id >>> @GeneratedValue(strategy=GenerationType.AUTO) >>> @Column(name = "ATT_ID") >>> private String _id; >>> >>> @Column(name = "ATT_NAME") >>> private String _name; >>> >>> @BasicCollection ( >>> fetch=FetchType.EAGER, >>> valueColumn=@Column(name="ATT_VALUE")) >>> @CollectionTable ( >>> name="ATTRIBUTE_VALUES", >>> primaryKeyJoinColumns= >>> {@PrimaryKeyJoinColumn(name="ATT_ID", >>> referencedColumnName="ATT_ID")} >>> ) >>> private List<String> _values; >>> ... >>> } >>> >>> >>> >>> I tried something like this >>> >>> @NamedQueries({ >>> @NamedQuery(name="RecordDao.findByAttribute", >>> query="SELECT DISTINCT r FROM RecordDao r JOIN r._attributes a >>> JOIN a._values v WHERE a._name = :name AND v._values = :value") >>> >>> }) >>> >>> >>> but during EntityManager creation I get the following error: >>> >>> Exception [EclipseLink-8030] (Eclipse Persistence Services - 1.0.2 >>> (Build 20081024)): org.eclipse.persistence.exceptions.JPQLException >>> Exception Description: Error compiling the query >>> [RecordDao.findByAttribute: SELECT DISTINCT r FROM RecordDao r JOIN >>> r._attributes a JOIN a._values v WHERE a._name = :name AND v._values >> = >>> :value], line 1, column 63: unknown state or association field >> [_values] >>> of class [org.eclipse.smila.datamodel.persistence.AttributeDao]. >>> >>> >>> I expect the syntax for "AND v._values = :value" to be not valid, but >> I >>> wonder why it complains about JOIN a._values v. >>> Anyone any ideas/suggestions ? >>> >>> >>> Bye, >>> Daniel >>> _______________________________________________ >>> eclipselink-users mailing list >>> eclipselink-users@... >>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users >> _______________________________________________ >> eclipselink-users mailing list >> eclipselink-users@... >> https://dev.eclipse.org/mailman/listinfo/eclipselink-users > _______________________________________________ > eclipselink-users mailing list > eclipselink-users@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
|
|
AW: AW: questiona about JPQL and Collections
by Daniel.Stucky
::
Rate this Message:
Thanks Tom !
Sometimes it's just too easy :-) Bye, Daniel > -----Ursprüngliche Nachricht----- > Von: eclipselink-users-bounces@... [mailto:eclipselink-users- > bounces@...] Im Auftrag von Tom Ware > Gesendet: Donnerstag, 29. Januar 2009 15:17 > An: EclipseLink User Discussions > Betreff: Re: AW: [eclipselink-users] questiona about JPQL and > Collections > > Try calling readAllQuery.dontUseDistinct(). > > -Tom > > Daniel.Stucky@... wrote: > > Hi Tom, > > > > thanks for your help, in general it works as described by you. > > > > However, I forgot to include the following member of RecordDao in the > sample code: > > @Column(name = "RECORD") > > private byte[] _serializedRecord; > > > > The generated SQL query contains DISTINCT, which is not usable with > queries containing BLOBs. > > Any suggestions how to handle this ? > > > > > > Bye, > > Daniel > > > >> -----Ursprüngliche Nachricht----- > >> Von: eclipselink-users-bounces@... [mailto:eclipselink- > users- > >> bounces@...] Im Auftrag von Tom Ware > >> Gesendet: Mittwoch, 28. Januar 2009 21:12 > >> An: EclipseLink User Discussions > >> Betreff: Re: [eclipselink-users] questiona about JPQL and > Collections > >> > >> Hi Daniel, > >> > >> Because BasicCollection is not a part of the JPA specification > yet, > >> JPQL > >> cannot currently query across BasicCollections. (That behavior is > >> expected in > >> the upcoming JPA 2.0 specification) > >> > >> To access a BasicCollection in a query, you will have to use > >> EclipseLink's > >> criteria API instead. > >> > >> Your code will look something like this: > >> > >> ReadAllQuery query = new ReadAllQuery(RecordDAO.class); > >> ExpressionBuilder record = new ExpressionBuilder(); > >> Expression attributes = record.anyOfAttributes("attributes"); > >> Expresion criteria = > >> attributes.get("_name").equal(record.getParameter("name")); > >> criteria = > >> > criteria.and(attributes.anyOf("_values").equal(record.getParameter("val > >> ue"))) > >> query.setSelectionCriteria(criteria); > >> > >> This query can be added as a named query through a SessionCustomizer > by > >> calling > >> the addQuery(name, query) method. > >> > >> That is the basics of how it should look. With a little > massaging > >> of the > >> code, it should be possible to use this query just like any other > named > >> query in > >> JPA. > >> > >> BTW: Here is the Customizer doc: > >> > >> > http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Usi > >> ng_EclipseLink_JPA_Extensions_for_Customization_and_Optimization > >> > >> -Tom > >> > >> > >> > >> > >> Daniel.Stucky@... wrote: > >>> Hi all, > >>> > >>> given the following Entities, how can I express a named query that > >>> selects all Records where AttributeDao._name = "X" and > >>> AttributeDao._values.contains("Y") > >>> > >>> > >>> @Entity > >>> @Table(name = "RECORDS") > >>> public class RecordDao implements Serializable { > >>> > >>> @Id > >>> @Column(name = "ID") > >>> private String _idString; > >>> > >>> @Column(name = "SOURCE") > >>> private String _source; > >>> ... > >>> } > >>> > >>> @Entity > >>> @Table(name = "ATTRIBUTES") > >>> public class AttributeDao { > >>> > >>> @Id > >>> @GeneratedValue(strategy=GenerationType.AUTO) > >>> @Column(name = "ATT_ID") > >>> private String _id; > >>> > >>> @Column(name = "ATT_NAME") > >>> private String _name; > >>> > >>> @BasicCollection ( > >>> fetch=FetchType.EAGER, > >>> valueColumn=@Column(name="ATT_VALUE")) > >>> @CollectionTable ( > >>> name="ATTRIBUTE_VALUES", > >>> primaryKeyJoinColumns= > >>> {@PrimaryKeyJoinColumn(name="ATT_ID", > >>> referencedColumnName="ATT_ID")} > >>> ) > >>> private List<String> _values; > >>> ... > >>> } > >>> > >>> > >>> > >>> I tried something like this > >>> > >>> @NamedQueries({ > >>> @NamedQuery(name="RecordDao.findByAttribute", > >>> query="SELECT DISTINCT r FROM RecordDao r JOIN r._attributes > a > >>> JOIN a._values v WHERE a._name = :name AND v._values = :value") > >>> > >>> }) > >>> > >>> > >>> but during EntityManager creation I get the following error: > >>> > >>> Exception [EclipseLink-8030] (Eclipse Persistence Services - 1.0.2 > >>> (Build 20081024)): org.eclipse.persistence.exceptions.JPQLException > >>> Exception Description: Error compiling the query > >>> [RecordDao.findByAttribute: SELECT DISTINCT r FROM RecordDao r JOIN > >>> r._attributes a JOIN a._values v WHERE a._name = :name AND > v._values > >> = > >>> :value], line 1, column 63: unknown state or association field > >> [_values] > >>> of class [org.eclipse.smila.datamodel.persistence.AttributeDao]. > >>> > >>> > >>> I expect the syntax for "AND v._values = :value" to be not valid, > but > >> I > >>> wonder why it complains about JOIN a._values v. > >>> Anyone any ideas/suggestions ? > >>> > >>> > >>> Bye, > >>> Daniel > >>> _______________________________________________ > >>> eclipselink-users mailing list > >>> eclipselink-users@... > >>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users > >> _______________________________________________ > >> eclipselink-users mailing list > >> eclipselink-users@... > >> https://dev.eclipse.org/mailman/listinfo/eclipselink-users > > _______________________________________________ > > eclipselink-users mailing list > > eclipselink-users@... > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users > _______________________________________________ > eclipselink-users mailing list > eclipselink-users@... > https://dev.eclipse.org/mailman/listinfo/eclipselink-users eclipselink-users mailing list eclipselink-users@... https://dev.eclipse.org/mailman/listinfo/eclipselink-users |
| Free embeddable forum powered by Nabble | Forum Help |
