How to sort using Criteria in Hibernate

In this post, we will see How to sort using Criteria in Hibernate. We will see Criteria addOrder() example Using Spring Boot. We will fetch record from the database and we will sort on the basis of some field(let’s say studentName). Criteria interface has addOrder() method which used for sorting purpose. At the end of the post, we will also see how to customized order class for sorting purpose.

public Criteria addOrder(Order order); 

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

	public static Order asc(String propertyName) {
		return new Order( propertyName, true );
	}

	public static Order desc(String propertyName) {
		return new Order( propertyName, false );
	}

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

	@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("studentName"));
		List<Student> studentList = criteria.list();
		return studentList;
	}

 

Let’s see a complete example of sort using Criteria in Hibernate using Spring Boot and Oracle. Before going ahead let’s see what are we going to do. We will have one entity Student.java which have three fields(id, studentName and rollNumber). First, we will save some student records in the database using REST API and then we will fetch all record using Criteria which would be in sorted order.

 

First, we will save record using the below rest API.

http://localhost:9091/student/save

Request Data-

[
    {
        "studentName": "Hitesh",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "John",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Mohan",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Nagesh",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Ramesh",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Rana",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Rock",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Simpy",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Tiwari",
        "rollNumber": "0126CS01"
    },    {
        "studentName": "Appu",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Babloo",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Gagesh",
        "rollNumber": "0126CS01"
    }
]

 

Response data for save call.

[
    {
        "id": 1,
        "studentName": "Hitesh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 2,
        "studentName": "John",
        "rollNumber": "0126CS01"
    },
    {
        "id": 3,
        "studentName": "Mohan",
        "rollNumber": "0126CS01"
    },
    {
        "id": 4,
        "studentName": "Nagesh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 5,
        "studentName": "Ramesh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 6,
        "studentName": "Rana",
        "rollNumber": "0126CS01"
    },
    {
        "id": 7,
        "studentName": "Rock",
        "rollNumber": "0126CS01"
    },
    {
        "id": 8,
        "studentName": "Simpy",
        "rollNumber": "0126CS01"
    },
    {
        "id": 9,
        "studentName": "Tiwari",
        "rollNumber": "0126CS01"
    },
    {
        "id": 10,
        "studentName": "Appu",
        "rollNumber": "0126CS01"
    },
    {
        "id": 11,
        "studentName": "Babloo",
        "rollNumber": "0126CS01"
    },
    {
        "id": 12,
        "studentName": "Gagesh",
        "rollNumber": "0126CS01"
    }
]

 

REST API to fetch all students.

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

Response data which is sorted on the base of  studentName.

[
    {
        "id": 10,
        "studentName": "Appu",
        "rollNumber": "0126CS01"
    },
    {
        "id": 11,
        "studentName": "Babloo",
        "rollNumber": "0126CS01"
    },
    {
        "id": 12,
        "studentName": "Gagesh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 1,
        "studentName": "Hitesh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 2,
        "studentName": "John",
        "rollNumber": "0126CS01"
    },
    {
        "id": 3,
        "studentName": "Mohan",
        "rollNumber": "0126CS01"
    },
    {
        "id": 4,
        "studentName": "Nagesh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 5,
        "studentName": "Ramesh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 6,
        "studentName": "Rana",
        "rollNumber": "0126CS01"
    },
    {
        "id": 7,
        "studentName": "Rock",
        "rollNumber": "0126CS01"
    },
    {
        "id": 8,
        "studentName": "Simpy",
        "rollNumber": "0126CS01"
    },
    {
        "id": 9,
        "studentName": "Tiwari",
        "rollNumber": "0126CS01"
    }
]

 

Generated Query while the get operation.

Hibernate: 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 this_.student_name asc

Criteria addOrder() example in Hibernate Using Spring Boot.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ click on next.  Fill all details(GroupId – criteriasortingexample, ArtifactId – criteriasortingexamplename – criteriasortingexample) 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>criteriasortingexample</groupId>
	<artifactId>criteriasortingexample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>criteriasortingexample</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 –

 

 

 

Student.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;
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;

	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;
	}


}

 

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 List<Student> saveBook(@RequestBody List<Student> studentList) {
		List<Student> studentResponse = (List<Student>) studentService.saveStudent(studentList);
		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> {

}

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 List<Student> saveStudent(List<Student> studentList);
	
	public List<Student> findAll();
}

 

StudentServiceImpl.java

package com.javatute.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
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;
	
	@PersistenceContext
	EntityManager entityManager;

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

	
	@Transactional
	public List<Student> findAll() {
		Session session = entityManager.unwrap(Session.class);
		//below one is depricated, but we will use to keep example simple.
		//Instead we can use JPA Criteria
		Criteria criteria = session.createCriteria(Student.class);
		criteria.addOrder(Order.asc("studentName"));
		List<Student> studentList = criteria.list();
		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(Use request data to perform save operation which has been given in the beginning of the post).

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

Expected response data –

Perform GET operation using below REST API.

http://localhost:9091/student/allstudents

In response, we should have sorted data basis of student name as below.

 

Custom Order.

We can define our own Custom Order for sorting. Let’s see a simple use case where you may need a custom order. Consider the above example, we want to sort on the basis of length of studentName. That means if we sort in ascending order then the studentName which contains less character, should come first.

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.

	@Override
	public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {

		final StringBuilder query = new StringBuilder();
		query.append(" LENGTH(").append("student_name").append(") ").append(super.isAscending() ? "asc" : "desc");

		return query.toString();
	}

See here complete example which explains how to create custom order in hibernate.

That’s all about How to sort using Criteria in Hibernate.

You may like –

• @Version Annotation Example In Hibernate.
• Hibernate Validator Constraints Example Using Spring Boot.
• @Temporal Annotation Example In Hibernate/Jpa Using Spring Boot.
• Hibernate Table Per Concrete Class Spring Boot.
• Hibernate Table Per Subclass Inheritance Spring Boot.
• Hibernate Single Table Inheritance using Spring Boot.
• One To One Mapping Annotation Example in Hibernate/JPA using Spring Boot and Oracle.
• One To One Bidirectional Mapping Example In Hibernate/JPA Using Spring Boot and Oracle.
• One To Many Mapping Annotation Example In Hibernate/JPA Using Spring Boot And Oracle.
• Many To One Unidirectional Mapping In Hibernate/JPA Annotation Example Using Spring Boot and Oracle.
• One To Many Bidirectional Mapping In Hibernate/JPA Annotation Example Using Spring Boot and Oracle.
• Many To Many Mapping Annotation Example In Hibernate/JPA Using Spring Boot And Oracle.
• Spring Data CrudRepository save() Method.

Criteria interface docs.