@OrderBy Annotation in Hibernate for Sorting

In this post, we will see How to use @OrderBy Annotation in Hibernate for Sorting purpose using Spring Boot and Oracle. We will have two REST APIs, the first one will be used to save the entity and another one to retrieve the entity. Consider we have two entity Student.java and Book.java. Both entities are in One To Many relationships.

Student.java

@Entity
public class Student {

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

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

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

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

}

Book.java

package com.javatute.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Book {

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

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

	@Column(name = "number_of_page")
	private String numberOfPage;
	
}

Using @OrderBy Annotation we can sort the record on basis of bookName or bookId(any field defined in Book entity).

Before moving forward let’s see some basic points about @OrderBy Annotation.

@OrderBy Annotation used with Collection type of field – @OrderBy is used with only collection type of filed(Generally in case of association mapping the field annotated with @OneToMany  or @ManyToMany or @ElementCollection 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.

Note – we will see sample example of @OrderBy annotation to use with @ElementCollection type fields at the end of the post.

Correct way to use @OrderBy.

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

Below code snippet will not work.

@Column(name = "student_name")
@OrderBy("studentName")
private String studentName;

 

By Default @OrderBy sort in ascending order – If we don’t provide in which order we want to sort it will sort in ascending order.

@OrderBy("bookName")  – This will sort on basis of bookName as ascending order(i.e @OrderBy(“bookName”) and @OrderBy("bookName ASC") both will behave same)

We can sort in descending order as below –

@OrderBy("bookName DESC")

Using @OrderBy only – If we don’t provide field name then it will sort on the basis of the primary key. In below code, Book entity will get sort on basis of primary key i.e id.

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

Using @OrderBy annotation with @ElementCollection type of collection – Below code snippet will give result sorted phoneNumbers as ASC.

@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "student_phone_number", joinColumns = @JoinColumn(name = "student_id"))
@OrderBy
private Set<String> phoneNumbers = new HashSet<String>();

@OrderBy annotation will work the same way even if we have an embeddable type of @ElementCollection.

@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "student_book", joinColumns = @JoinColumn(name = "student_id"))
@OrderBy("bookName")
private List<Book> bookList = new ArrayList<Book>();

See complete example about @ElementCollection annotation here.

 

Let’s see what we going to do here. We will have two entity Student and Book which is in One To Many relationship. Student can have many books.

@Entity
public class Student {

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

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

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

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

}

We would have save and get APIs as below.

http://localhost:9091/student/save  – POST Operation

http://localhost:9091/student/allstudents – GET Operation

Generated Query –

Hibernate: select booklist0_.student_id as student_id4_0_0_, booklist0_.id as id1_0_0_, booklist0_.id as id1_0_1_, booklist0_.book_name as book_name2_0_1_, booklist0_.number_of_page as number_of_page3_0_1_ from book booklist0_ where booklist0_.student_id=? order by booklist0_.book_name

Let’s see @OrderBy Annotation in Hibernate for Sorting Using Spring Boot from scratch.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ click on next.  Fill all details(GroupId – orderbyexample, ArtifactId – orderbyexampleand name – orderbyexample) and click on finish. Keep packaging as the jar.

Modify pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>oerderbyexample</groupId>
	<artifactId>oerderbyexample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>oerderbyexample</name>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.3</version>
		</dependency>

	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
		<plugins>

			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<fork>true</fork>
					<executable>C:\Program Files\Java\jdk1.8.0_131\bin\javac.exe</executable>
				</configuration>
			</plugin>


		</plugins>
	</build>
</project>

Note – In pom.xml we have defined javac.exe path in configuration tag. You need to change accordingly i.e where you have installed JDK.

If you see any error for oracle dependency then follow these steps.

Directory structure –

 

Let’s see entity for @OrderBy Annotation in Hibernate for Sorting purpose using Spring Boot and Oracle.

Student.java

package com.javatute.entity;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;

@Entity
public class Student {

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

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

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

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

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getStudentName() {
		return studentName;
	}

	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}

	public String getRollNumber() {
		return rollNumber;
	}

	public void setRollNumber(String rollNumber) {
		this.rollNumber = rollNumber;
	}

	public List<Book> getBookList() {
		return bookList;
	}

	public void setBookList(List<Book> bookList) {
		this.bookList = bookList;
	}

}

Book.java

package com.javatute.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Book {

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

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

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

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getBookName() {
		return bookName;
	}

	public void setBookName(String bookName) {
		this.bookName = bookName;
	}

	public String getNumberOfPage() {
		return numberOfPage;
	}

	public void setNumberOfPage(String numberOfPage) {
		this.numberOfPage = numberOfPage;
	}
	
	
}

 

 

StudentController.java

package com.javatute.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.javatute.entity.Student;
import com.javatute.service.StudentService;

@RestController
@RequestMapping(value = "/student")
public class StudentController {

	@Autowired
	private StudentService studentService;

	@RequestMapping(value = "/save", method = RequestMethod.POST)
	@ResponseBody
	public Student saveBook(@RequestBody Student student) {
		Student studentResponse = (Student) studentService.saveStudent(student);
		return studentResponse;
	}

	@RequestMapping(value = "/allstudents", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> getAllStudents() {
		List<Student> studentList = (List<Student>) studentService.findAll();
		return studentList;
	}

}

StudentRepository.java – interface

package com.javatute.repository;

import java.io.Serializable;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.javatute.entity.Student;
@Repository
public interface StudentRepository extends CrudRepository<Student,Serializable> {
	public Student findById(int id);
}

StudentService.java – interface

package com.javatute.service;

import java.util.List;

import org.springframework.stereotype.Component;

import com.javatute.entity.Student;

@Component
public interface StudentService {
	public Student saveStudent(Student student);
	
	public List<Student> findAll();
}

Business logic code and implementation for @OrderBy Annotation in Hibernate for Sorting purpose using Spring Boot and Oracle.

StudentServiceImpl.java

package com.javatute.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.javatute.entity.Student;
import com.javatute.repository.StudentRepository;
import com.javatute.service.StudentService;

@Service("studentServiceImpl")
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentRepository studentRepository;

	@Transactional
	public Student saveStudent(Student student) {
		Student studentresponse = studentRepository.save(student);
		return studentresponse;
	}

	
	@Transactional
	public List<Student> findAll() {
		List<Student> studentList = (List<Student>) studentRepository.findAll();
		return studentList;
	}
	
}

SpringMain.java

package com.javatute.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.*")
@EntityScan("com.javatute.entity")
public class SpringMain {
	public static void main(String[] args) {

		SpringApplication.run(SpringMain.class, args);
	}

}

 

JpaConfig.java

package com.javatute.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class JpaConfig {

}

application.properties

# Connection url for the database
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=SYSTEM
spring.datasource.password=oracle2
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
# Show or not log for each sql query
spring.jpa.show-sql = true
 
 
spring.jpa.hibernate.ddl-auto =create
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
 
server.port = 9091

Let’s run the SpringMain class(run as java application).

Perform save operation first using below REST API.

http://localhost:9091/student/save  – POST Operation

Request Data for save operation.

{
	"studentName": "Nagesh",
	"rollNumber": "0126CS01",
	"bookList": [{
			"bookName": "Godan",
			"numberOfPage": "300"
		},
		{
			"bookName": "Premchand's best stories",
			"numberOfPage": "400"
		},
		{
			"bookName": "Alchemist",
			"numberOfPage": "90"
		},
				{
			"bookName": "Devdas",
			"numberOfPage": "340"
		},
				{
			"bookName": "Rich dad poor dad",
			"numberOfPage": "250"
		}
	]

}

http://localhost:9091/student/allstudents – GET Operation

Sorted Response data -Perform retrieve operation, Book entity will get sorted in ascending order basis of bookName(As we are using @OrderBy(“bookName”)  in our example).

[
    {
        "id": 1,
        "studentName": "Nagesh",
        "rollNumber": "0126CS01",
        "bookList": [
            {
                "id": 4,
                "bookName": "Alchemist",
                "numberOfPage": "90"
            },
            {
                "id": 5,
                "bookName": "Devdas",
                "numberOfPage": "340"
            },
            {
                "id": 2,
                "bookName": "Godan",
                "numberOfPage": "300"
            },
            {
                "id": 3,
                "bookName": "Premchand's best stories",
                "numberOfPage": "400"
            },
            {
                "id": 6,
                "bookName": "Rich dad poor dad",
                "numberOfPage": "250"
            }
        ]
    }
]

 

Let’s see the sample code which tells how to define @ElementCollection types field and perform sorting using @OrderBy annotation.

You can modify Student.java and Book.java as below. The rest of logic would be the same.

Student.java

package com.javatute.entity;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.persistence.CollectionTable;
import javax.persistence.Column;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OrderBy;

@Entity
public class Student {

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

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

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

	@ElementCollection(fetch = FetchType.EAGER)
	@CollectionTable(name = "student_book", joinColumns = @JoinColumn(name = "student_id"))
	@OrderBy("bookName")
	private List<Book> bookList = new ArrayList<Book>();

	@ElementCollection(fetch = FetchType.EAGER)
	@CollectionTable(name = "student_phone_number", joinColumns = @JoinColumn(name = "student_id"))
	@OrderBy
	private Set<String> phoneNumbers = new HashSet<String>();
	
	
	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getStudentName() {
		return studentName;
	}

	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}

	public String getRollNumber() {
		return rollNumber;
	}

	public void setRollNumber(String rollNumber) {
		this.rollNumber = rollNumber;
	}

	public List<Book> getBookList() {
		return bookList;
	}

	public void setBookList(List<Book> bookList) {
		this.bookList = bookList;
	}

	public Set<String> getPhoneNumbers() {
		return phoneNumbers;
	}

	public void setPhoneNumbesr(Set<String> phoneNumbers) {
		this.phoneNumbers = phoneNumbers;
	}
	
	

}

Book.java

package com.javatute.entity;

import javax.persistence.Column;
import javax.persistence.Embeddable;

@Embeddable
public class Book {

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

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

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

	public String getBookName() {
		return bookName;
	}

	public void setBookName(String bookName) {
		this.bookName = bookName;
	}

	public String getNumberOfPage() {
		return numberOfPage;
	}

	public void setNumberOfPage(String numberOfPage) {
		this.numberOfPage = numberOfPage;
	}

	public String getAuthor() {
		return author;
	}

	public void setAuthor(String author) {
		this.author = author;
	}

}

 

If you trying to fetch student record using GET API below query will generate.

Hibernate: select phonenumbe0_.student_id as student_id1_2_0_, phonenumbe0_.phone_numbers as phone_numbers2_2_0_ from student_phone_number phonenumbe0_ where phonenumbe0_.student_id=? order by phonenumbe0_.phone_numbers asc

Hibernate: select booklist0_.student_id as student_id1_1_0_, booklist0_.author as author2_1_0_, booklist0_.book_name as book_name3_1_0_, booklist0_.number_of_page as number_of_page4_1_0_ from student_book booklist0_ where booklist0_.student_id=? order by booklist0_.book_name

 

That’s all about @OrderBy Annotation in Hibernate for Sorting purpose using Spring Boot and oracle

You may like –

• How to sort using Criteria in Hibernate.
• Hibernate Validator Constraints Example Using Spring Boot
• Hibernate Table Per Concrete Class Spring Boot
• Hibernate Table Per Subclass Inheritance Spring Boot
• Hibernate Single Table Inheritance using Spring Boot
• Many To Many Mapping Annotation Example In Hibernate/JPA Using Spring Boot And Oracle
• One To Many Bidirectional Mapping In Hibernate/JPA Annotation Example Using Spring Boot and Oracle
• Many To One Unidirectional Mapping In Hibernate/JPA Annotation Example Using Spring Boot and Oracle
• @Temporal Annotation Example In Hibernate/Jpa Using Spring Boot

@OrderBy docs.

Summary – We have seen @OrderBy Annotation in Hibernate for Sorting example hibernate and Spring Boot. We have also seen @OrderBy is used with only collection type of filed(Generally in case of association mapping the field annotated with @OneToMany or @ManyToMany or @ElementCollection annotation)