collection « Query « JPA Q&A

1. HQL order by within a collection

I have 2 entities: car and wheels (oneToMany) and I want to retrieve my car, with all the wheels and (this is the tricky part) ordered by wheels.location. Select c from Car ...

2. Hibernate - Selecting across multiple joins with collections

I'm having trouble getting a hibernate select to return a correctly populated object graph, when the select contains joins across many collections. Eg:

 String sql = "select distinct changeset " +

3. hibernate query problem, so close and stumped

The schema: (psuedocode) I have a bean, called BaseEntity... @Entity class BaseEntity { @OneToMany @CascadeType.ALL List [Property] properties; //the use angled braces ommited for the stackoverflow editor to show ...

4. Hibernate query for multiple items in a collection

I have a data model that looks something like this:

public class Item {
    private List<ItemAttribute> attributes;
    // other stuff

public class ItemAttribute {

5. Query scalar collections in HQL

I have the following class:

class User {
String username;
private Set<String> roles = new HashSet<String>();
[many more things here]
And I want to write a HQL query that retrieves the username and the roles for ...

6. Hibernate query for objects with collections

For a structure as follows:

class Person
   Integer personID;
   String personName
   List<Pet> pets;

class Pet
  String petID;
  Integer personID;
  String petName;
  Integer petNum;
The ...

7. Joining to a collection in a JPA-QL query

I'am using JPA for mapping ,I have this entity Class

@Table(name = "h_pe")

public class HPe implements Serializable {
 private static final long serialVersionUID = 1L;
 protected HPePK hPePK;
 @Column(name = "PE_TIMEOUT")

8. How do I query for an object and populate it with a subset of a collection in hibernate?

I'm trying to left outer join an object with a subset of a collection. Here's the query:

String queryStr = "select distinct hotel from Hotelmstr as hotel " +

9. Hibernate query on custom collection

I have a hibernate mapping with custom collection types in it. java:

class Policy {
private HistoryMap<Date, PolicyStatus> statusHistory;
    <map name="statusHistory" inverse="true" table="tbl_policy_in_time" order-by="PolIt_ValidFrom desc"

10. Hibernate criteria query for Collection Table?

I have following Entity

@Table(name = "rule")
public class Rule implements Serializable {

    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "rule_id")
    private ...

11. Hibernate: using criteria with collections (HashSet)

I have the following classes:

  public class Folder{
      private Set<Documents> documents;
      private Set<Clip> clips;

  public class Clip{

12. Creating HQL Query which works on Collections

I am having following Model Host { Long Id String fqdn2 Set files } HostDiscoveredReg{ Long ID Host hst String filename } SoftwareRegFiles{ String filename Set apps } SoftwareRegApps{ String appName; Set files } Now I want to find the set of HostDiscoveredReg objects for a Set of Apps and Set of Hosts. for given Set of ...

13. Query on collection

15. Querying a collection

I have an entity that has a set of arbitrary categories attached to it, it's a one-to-many unidirectional relationship using a join table. Basically, a textbook example as illustrated @ ... l-join-12m When I query the entity based on the collection of categories I have to use the following; Code: public List getByEntityFilter(EntityFilter filter) { ...

16. Select a collection in HQL

Hi everyone, I have a problem while developping my HQL query: This is a part of my query : " SELECT new RetourRequeteOTBean.class.getName() ( ODT.numero , ODT.version, elements(ODT.listeOMs) FROM OrdreDeTravailVO as ODT " numero is an integer , versions is an integer and listeOMs is a Collection. The constructor of RetourRequeteOTBean has the correct type with RetourRequeteOTBean(int, int, List ). But ...

17. Select a collection in HQL

Hi, After struggling with this for about an hour, I figure I'd ask if this was even possible in HQL. If I have a Person object with some simple properties (firstName, lastName) as well as a list (children) of Child objects. Is it possible to do a SELECT that would pull back the Child list as one of the array members. ...

18. Using order-by on collection

Hello, I can't seem to get the order-by property to work on my one-to-many collection. I am using XDoclet to generate my mapping files. Here is the relevant portion of the mapping: Code:

19. query on collections

20. problems with order-by and Collections

Hi all, I am having the following problem with hibernate using the order-by attribute in the Collection specification/Mapping. I have a field createdOn in a generic class A. Now I have two classes B and C that extend A (They are in different packages and I have them as joined subclasses of A). My class B has a one to many ...

21. collection query - how to

Michael, This works fine as long as I limit myself to getting only A with lazy loading true and outer join = false at global level. However, after getting A (session is now closed), I would like to then get to related B and C (based on my criteria). How do I do that ?

22. Query member of unreferenced collection

Hibernate Version: 2.1 With Spring 1.0.3 with JBoss 3.2.3, Mysql 4.0.15 I have a table that stores the project information. Projects can belong to project groups, which are basically projects too(they are differentiated by a type column). My mapping for the projectgroups column looks like this This ...

23. can't work out query with collection

Author Message markmansour Post subject: can't work out query with collection Posted: Mon Jul 26, 2004 11:19 am Newbie Joined: Sun Apr 04, 2004 1:58 am Posts: 13 Location: Melbourne, Australia I really need some help working out this query... I just can't make it fly... my tables are (simplified) Author - maps to Author object -------- id: key ...

24. How to make a query over a collection?

I can't find anywhere in the forum a satisfying answer on how to make a query over a collection of the following type with the criteria api: select distinct g.* from group as g, member as m1, member as m2 where g.groupId = m1.groupId and g.groupId = m2.groupId; in other words, how do I find the 'group' containing 'member1' and 'member2'? ...

25. Using Criteria queries on collections

26. Query a Many-to-Many Collection

I am messing around with the "Hibernate A Developer's Notebook" source code and the Hibernate Tools/IDE that is a plugin into eclipse. I can successfully run code that returns a Track along with its relationship to Artists...(a many to many). If I try to run the HQL query that is produced from the hibernate.show_sql within the Hibernate fails. How would ...

27. Find cause of "Found shared references to a collection&

Hello I am trying to do some mapping with hibernate through xdoclet and with one of my clases i am getting the following exception Found shared references to a collection org.hibernate.HibernateException: Found shared references to a collection Anyone has any clue of what is the most common cause for this? thanks a lot!

28. "Flatten" joined collections to query on multiple

Hi, We have a model that is basically A which has collection of B, each of which has a collection of C. We need to query for the objects where the value of B.value is multiple values, and c.value is also multiple values. Thus we need to "flatten" out the hierarchy so we can select on multiple columns. However, this is ...

29. ordering a collection - order-by="..." not adequat


30. Problem with join and order by on collection in HQL

2005-09-22 01:48:45,629 WARN JDBCExceptionReporter:57 - SQL Error: 0, SQLState: 42803 2005-09-22 01:48:45,630 ERROR JDBCExceptionReporter:58 - ERROR: column "picture0_.format" must appear in the GROUP BY clause or be used in an aggregate function 2005-09-22 01:48:45,633 WARN RequestProcessor:509 - Unhandled Exception thrown: class no.uka.intranett.db.DataResourceException 2005-09-22 01:48:45,653 ERROR InsertTag:920 - ServletException in '/secure/gallery/': net.sf.hibernate.exception.SQLGrammarException: Could not execute query

31. Query based on a collection's contents?

public class MyClass{ private Long id; private Set myOtherClasses; public Long getId(){return id;} protected Long setId(Long id){;} public Set getMyOtherClasses(){ return myOtherClasses; } public void ...

32. Efficient collection queries

Newbie Joined: Mon May 09, 2005 5:43 pm Posts: 2 I have two questions about querying collections as efficiently as possible. 1) In some cases Hibernate requires an unnecessary join of an intermediate table. Consider the this example: A User has a many-to-one association with a Group, and a Group has a set of Permissions. So the USER table has a ...

33. problem dereferencing a collection in a retrieving query

Newbie Joined: Fri Nov 11, 2005 9:58 am Posts: 1 I'm having problems of kind "you may not dereference a collection with cascade=all-delete-orphan". I've read something about, but my issue is a little different. It occurs when I try retrieving some objects from db, and it occurs randomly with a few queries. When I do a simple search, the exception occurs, ...

34. how does one avoid select sql on add to a collection

Newbie Joined: Tue Jan 10, 2006 9:42 pm Posts: 5 Ok, I swear I sent this last night, but as it is not in the forum, I'll try again. I'm trying to understand the ramifications of various mappings. I'm trying to figure out how a many-to-many mapping behaves. In particular, I'm curious about creating an assoication between two entities where I ...

35. How do I query an object having a collection

String sqlSelect = "select {s.*},{li.*} " + "from StarViInvoice s inner join StarViLineItem li on s.headerOrderReference = li.headerOrderReference " + "where s.identDealerNumber =:dealerNbr " + ...

36. HQL for finding a subset in a collection

I need help writing HQL query for the following scenerio: I have an object Booking which contains People in it. I want to find if a booking contains atleast one person from another booking: Booking A - {John, Mary, Jane} Booking B - {Mary, Henry, Joe} - The return should be a count of 1, because Booking A has Mary and ...

37. report queries & collections

Hi all! For example, I have classes class Test { int field1; String field2; List codes; } class Code { int codeField1; String codeField2; } I have to write a report query wich will retrieve field1, field2 and codeField1 from some of the codes associated with current Test object (with some specified condition on codeField2). Which is the best and the ...

39. Problem with ordering of collections

Hi, i have a problem with the ordering of a collection that i cant solve myself. I have two objects with a many-to-many relationship. In one of this objects i mapped this relationship unidirectional with hibernate annotations like this: Code: @ManyToMany(cascade={CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH}, targetEntity=BasiszeitraumDOM.class, fetch=FetchType.EAGER) @JoinTable(name="S_Basiszeitverknuepfungen", ...

40. Ordering collections in HQL, possible?

Sets can't be ordered, because the sense of sets is that they don't contain duplicates and that they are unordered. But if you declare your message attribute as Collection instead of Set, your order-by attribute (I'm only working with annotations so I won't put my hand into the fire for it) should work. Attributes of type collection can be ordered. Hoeft ...

41. order from joined table with collection

Hello, I have a problem that is similar to what people have posted before. But I have a different setup so I couldnt find a way to apply the solution. I have used myeclipse to generate the java code and the mappings. so here it is Code: class Animal { private Set animalAttributes = new HashSet(0); ...

42. HQL Query to exclude items with spcfc collection elmntproprt

@Entity class Family { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) int family_id; @OneToMany(targetEntity=Person.class, cascade = CascadeType.ALL, mappedBy = "family") public Set familyMembers = new HashSet(); } @Entity class Person { @Id @GeneratedValue private ...

44. Query into collection

46. "select new" and collection

Hi! i have 3 tables : item, sub_item and bid and 3 classes: Item, SubItem and Bid. And ResultItem class as result bean. I have this select: Code: return getHibernateTemplate().find("select new" + "item.createDate, item.publishDate, item.finishDate, " + ...

47. Querying a collection with AND criteria

I have a question that seems simple enough but I'm having trouble figuring it out. I'm trying to run a query that returns results from a collection where the criteria is "AND'd". Example - if I had an 'Item' that has a 'categories' collection, I want to return the items that have all categories in my criteria. So the query would ...

48. Select collections with HQL

class Person { String name; Set visitedHotels; String someOtherData; public Person() { } public Person ( String name, Set visitedHotels ) {; ...

49. Creating HQL Query which works on Collections

50. Selecting across multiple joins with collections

'm having trouble getting a hibernate select to return a correctly populated object graph, when the select contains joins across many collections. Eg: Code: String sql = "select distinct changeset " + ...