How do you order a oneToMany join table in hibernate criteria

Question

Say I have a class Mother with a oneToMany mapping to Kittens

@Entity
@org.hibernate.annotations.Entity(dynamicUpdate = true)
@Table(name = "Mother")

.....

@OneToMany(fetch = FetchType.LAZY, targetEntity=Kittens.class, cascade=CascadeType.ALL)
@JoinColumn(name="motherId")
private List<Kittens> kittens;

I am using the criteria Api to provide a list

Criteria criteria = this.getSession().createCriteria(Mother.class);
Criterion MotherType = Restrictions.eq("type", "domesticated");
criteria.add(MotherType)
.addOrder(Order.asc("motherName"))
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.setFetchMode("kittens", FetchMode.JOIN)
// .addOrder(Order.asc("kittens.kittenName"));
List test = criteria.list();

Works fine until I try to add the order of the kittens field kittenName

I've also tried adding the @OrderBy(value="kittenName") annotation under the Kitten @OneToMany(...etc ) which works fine until you use the criteria API and this order will precede any other order statements in the sql.

Cheers in advance for any help...

score 118 · Accepted Answer · edited Nov 04 '20 at 22:24

118

You need to add the @javax.persistence.OrderBy annotation to your kittens list.

@OneToMany(fetch = FetchType.LAZY,
           targetEntity=Kittens.class,
           cascade=CascadeType.ALL)
@JoinColumn(name="motherId")
@OrderBy("kittenName")
private List<Kittens> kittens;

@See

Hibernate Annotations Reference Guide
Chapter 2.2.5. Mapping entity associations/relationships
Subchapter 2.2.5.3.4. Indexed collections (List, Map)

edited Nov 04 '20 at 22:24

armandino

17,625
17
69
81

answered Apr 06 '11 at 05:35

Ralph

118,862
56
287
383

Hi Ralph. I've tried that but the generated sql puts the kittenName order before the MotherName order. ie select ... order by kittens.kittenName asc, mother.motherName asc. And I'm trying to achive order by mother.motherName asc, kittens.kittenName asc. Thanks – jaseFace Apr 06 '11 at 11:46
@user456147: It seems that I do not really understand what you want to do? Load what, in which order? And what does not work like you want? – Ralph Apr 07 '11 at 06:31
apologies if I didn't make it clear. I want to order by MotherName then KittenName. The order is the other way round if I put the OrderBy on the Kitten entity. – jaseFace Apr 07 '11 at 14:29
@user456147: "I want to order by MotherName then KittenName."? - Do you mean, "I want to order the Kittens by Mother Name"? – Ralph Apr 07 '11 at 15:21
Hi Ralph ended up using criteria.createAlias("genres", "genreAlias", CriteriaSpecification.INNER_JOIN) instead of setFetchMode(... SELECT) yhis allowed me to set the order as you suggested in your answer with @OrderBy. Cheers. – jaseFace Apr 07 '11 at 19:21
And be sure that you are using a List and not a Set. – androniennn Oct 01 '19 at 07:03
@androniennn: if you use a Set (SortedSet) then the sorting must be done in the application, but it is not possible to sort them in the database – Ralph Oct 07 '19 at 08:11

How do you order a oneToMany join table in hibernate criteria

1 Answers1

Linked