Sorting in Hibernate

In this post, we will see Sorting in Hibernate/JPA.

Sorting in Hibernate

We will cover below topics.

Sorting in Hibernate using Criteria.
Sorting in Spring Data JPA
Sorting in Hibernate Using @OrderBy annotation.
Sorting in Hibernate for the complex and customized query.

Consider we have entity called Student.java as below which contains four fields as below.

@Entity
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	String university;
}

 

Sorting in Hibernate using Criteria.

Criteria interface has addOrder() method which is used for sorting purpose. The addOrder() method has been defined with the following signature.

public Criteria addOrder(Order order);

Order is a class which contains static methods asc(String propertyName) and desc(String propertyName) which is used for sorting direction.

Let’s see how to use addOrder() method for sorting in Hibernate.

Sorting on the basis of field name ascending order –


	@Transactional
	public List<Student> findAll() {
		Session session = entityManager.unwrap(Session.class);
		//Although session.createCriteria is depricated, but we will 
		//use here to keep example simple. Instead we can use JPA Criteria.
		Criteria criteria = session.createCriteria(Student.class);
		criteria.addOrder(Order.asc("name"));
		List<Student> studentList = criteria.list();
		return studentList;
	}

Sorting on the basis of field name Descending order –

	@Transactional
	public List<Student> findAll() {
		Session session = entityManager.unwrap(Session.class);
		//Although session.createCriteria is depricated, but we will 
		//use here to keep example simple. Instead we can use JPA Criteria.
		Criteria criteria = session.createCriteria(Student.class);
		criteria.addOrder(Order.desc("name"));
		List<Student> studentList = criteria.list();
		return studentList;
	}

Sorting on the basis of multiple fields(for example name and rollNumber) –

	@Transactional
	public List<Student> findAll() {
		Session session = entityManager.unwrap(Session.class);
		//Although session.createCriteria is depricated, but we will 
		//use here to keep example simple. Instead we can use JPA Criteria.
		Criteria criteria = session.createCriteria(Student.class);
		criteria.addOrder(Order.desc("name"));
                criteria.addOrder(Order.asc("rollNumber"));
		List<Student> studentList = criteria.list();
		return studentList;
	}

See an example of how to sort in Hibernate using criteria, Spring Boot and Oracle here.

Sorting in Hibernate example.

 

Sorting in Spring Data JPA.

Spring Data JPA provides PagingAndSortingRepository interface for sorting and paginations. We can define our repository extending this interface.

See sample repository interface which contains four methods which are performing sorting in different ways.

 

@Repository
public interface StudentRepository extends PagingAndSortingRepository<Student, Serializable> {
 
	List<Student> findByUniversityOrderByNameAsc(String university);
 
	List<Student> findAllByOrderByNameAsc();
	
	@Query("select s from Student s")
	List<Student> findAllAndSortByName(Sort sort);
	
	@Query("select s from Student s where s.university = ?1")
	List<Student> findByUniversityAndSortByNameLength(String university, Sort sort);
}

 

Retrieve the data on the basis of one field(i.e university) and sort on the basis of another field(name).

public List<Student> findByUniversity(String university) {
List<Student> response = studentRepository.findByUniversityOrderByNameAsc(university);
return response;
}

Retrieve all record(rows)  and sort on the basis of some field(name).

public List<Student> findAll() {
List<Student> response = (List<Student>) studentRepository.findAllByOrderByNameAsc();
return response;
}

Retrieve all record(rows)  and sort on the basis of some field(name). This is same to second one only different is way to write the query.

public List<Student> findAllUsinSortAsParameter() {
List<Student> response = (List<Student>) studentRepository.findAll(Sort.by(Sort.Direction.ASC, “name”));
return response;
}


 

Retrieve all record(rows)  and sort on the basis of some field(name). This is similar to the previous one only different is the way to write the query.

public List<Student> findAllAndSortByName() {
List<Student> response = studentRepository.findAllAndSortByName(new Sort(“name”));
return response;
}

 

Retrieve the data on the basis of one field(i.e university) and sort on basis of some other field lenght. for example we want to sort on basis of lenght of name(the student which name is short in length should come first).

public List<Student> findByUniversityAndSortByNameLength() {
List<Student> response = studentRepository.findByUniversityAndSortByNameLength(“rgtu”, JpaSort.unsafe(“LENGTH(name)”));
return response;
}


See example of Sorting in Spring Data JPA using Spring Boot here.

Sorting in Hibernate Using @OrderBy annotation.

@OrderBy is used with only collection type of filed(Generally in case of association mapping the field annotated with @OneToMany  or @ManyToMany annotation). If we use @OrderBy annotation with String type(or primitive/wrapper type) there is no compilation error or no exception but sorting will not work. Suppose We have two entity Student.java and Book.java. Student and Book entities are in One To Many relationships. One Student can have multiple books. We want to sort on the basis of Book entity filed i.e bookName.

 

Using @OrderBy Annotation.

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = “student_id”, referencedColumnName = “id”)
@OrderBy(“bookName DESC”)
private List<Book> bookList = new ArrayList<Book>();

 

See more details and example of @OrderBy Annotation in Hibernate Using Spring Boot here.

Sorting in Hibernate Using HQL.

We will prepare HQL and then will use session.createQuery().

First, we will create entityManager object and then we will get Session Object from entityManger object using unwrap() method.

@PersistenceContext
EntityManager entityManager;

 

Session session = entityManager.unwrap(Session.class);

Prepare HQL.

String hql = "FROM Student s ORDER BY s.name asc";

Create Query reference using createQuery() method of Session.

Query query = session.createQuery(hql);

Get the list of the entity using getResultList() method of Query.

List<Student> studentResponse = query.getResultList();

 

HQL Query to sort on basis of multiple fields.

String hql = "FROM Student s ORDER BY s.name asc, s.rollNumber desc";

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ order by student0_.name asc, student0_.roll_number desc

 

Let’s see below code snippet.

	public List<Student> getAllStudents() {
		Session session = entityManager.unwrap(Session.class);
		String hql = "FROM Student s ORDER BY s.name asc, s.rollNumber desc";
		Query query = session.createQuery(hql);
		List<Student> studentResponse = query.getResultList();
		return studentResponse;
	}

 

See the complete example of Sorting in Hibernate using HQL and Spring Boot here.

Sorting in Hibernate for the complex and customized query.

Customizing Criteria Query.

We want query something like below.

select * from student order by LENGTH(student_name) asc;

select this_.id as id1_0_0_, this_.roll_number as roll_number2_0_0_, this_.student_name as student_name3_0_0_ from student this_ order by LENGTH(student_name) asc

We can define custom class extending Order class and override toSqlString() method.

See an example here.

That’s all about Sorting in Hibernate.

You may like.

 

See docs here.