Hibernate entity with association tagged as @OrderBy is not fully fetched

Assume you have two entities SuperEntity and SubEntity that has one to many bi-directional association.

The one to many association is eagerly fetched and also defined with the @OrderBy jpa annotation.

When trying to load the SuperEntity, we expect the entity contains a list of SubEntitys sorted according to the field defined in the order by annotation.

In fact the list retrieved unordered. Moreover the sql generated by Hibernate framework includes only select query for the SuperEntity without any reference to the SubEntity or to order by. The generated sql suppose to have select query with a join or two select queries for both tables with the order by clause.

Lets go to a code example:

SuperEntity Class:

@Entity
@Table(name = "SuperEntity")
public class SuperEntity {
	public SuperEntity() {
		super();
	}

	@Id
	private long id;

	@OneToMany(mappedBy = "superEntity", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	@OrderBy("priority")
	List<SubEntity> subEntities;

	public List<SubEntity> getSubEntities() {
		return subEntities;
	}

	public void setSubEntities(List<SubEntity> subEntities) {
		this.subEntities = subEntities;
	}

	public SuperEntity(long id, List<SubEntity> subEntities) {
		super();
		this.id = id;
		this.subEntities = subEntities;
	}

	@Override
	public String toString() {
		return "SuperEntity [id=" + id + ", inputProfileTransmitDatas=" + subEntities + "]";
	}

}

SubEntity Class:

@Entity
@Table(name = "SubEntity")
public class SubEntity {
	@Id
	private long id;

	@Column(name = "PRIORITY")
	Integer priority;

	@ManyToOne
	@JoinColumn(name = "SuperEntity_ID")
	SuperEntity superEntity;

	public SuperEntity getSuperEntity() {
		return superEntity;
	}

	@Override
	public String toString() {
		return "SubEntity [id=" + id + ", priority=" + priority + "]";
	}

	public void setSuperEntity(SuperEntity superEntity) {
		this.superEntity = superEntity;
	}

	public SubEntity() {
		super();
	}

	public SubEntity(long id, Integer priority) {
		super();
		this.id = id;
		this.priority = priority;
	}
}

Query Method:

	public void query() {
		Session session = sessionFactory.getCurrentSession();
		SubEntity subEntity2 = new SubEntity(2, 2);
		SubEntity subEntity1 = new SubEntity(1, 1);
		List<SubEntity> inputTransmitList = new ArrayList<SubEntity>();
		inputTransmitList.add(subEntity2);
		inputTransmitList.add(subEntity1);
		SuperEntity superEntity = new SuperEntity(1, inputTransmitList);
		subEntity1.setSuperEntity(superEntity);
		subEntity2.setSuperEntity(superEntity);
		
		session.beginTransaction();
		session.save(superEntity);
		session.save(subEntity2);
		session.save(subEntity1);
		session.getTransaction().commit();
		session.close();

		session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		Query query = session.createQuery("from SuperEntity");
		List<SuperEntity> result = query.list();
		System.out.println(result.get(0).getSubEntities());
		session.getTransaction().commit();
	}

Hibernate might sometimes generates the following sql for the hql query above:

select superentit0_.id as id19_ from super_entity superentit0_

The results for this sql are of course unordered:

[SubEntity [id=2, priority=2], SubEntity [id=1, priority=1]]

There are two things that when done can improve your mood:

  1. Taking care to define default constructors for the entities.
  2. Making sure to close the previous Hibernate session. Sometimes the Hibernate framework uses the cache of the session to retrieve part of the hql results like in the case above. To make sure Hibernate framework will turn to the database you should close previous sessions.

Now after adding these items (marked in the code) the generated sql is as expected:

select superentit0_.id as id19_ from super_entity superentit0_
select subentitie0_.super_entity_id as super3_19_1_, subentitie0_.id as id1_, subentitie0_.id as id18_0_, subentitie0_.priority as priority18_0_, subentitie0_.super_entity_id as super3_18_0_ from sub_entity subentitie0_ where subentitie0_.super_entity_id=? order by subentitie0_.priority asc

The results are ordered as well:

[SubEntity [id=1, priority=1], SubEntity [id=2, priority=2]]

 

Thank you for your interest!

We will contact you as soon as possible.

Send us a message

Oops, something went wrong
Please try again or contact us by email at info@tikalk.com