How to create custom Order in Hibernate

Hibernate provides predefined APIs to perform sorting. Sometimes we want to define our custom sorting query with Hibernate generated query. In this post, we will see How to create custom Order for sorting purposes in Hibernate.

Consider we have an entity called Student.java as below.

@Entity
public class Student {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

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

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

}

Let’s see the use case and what we are going to implement in this tutorial.

Consider the scenario, we want to sort on the basis of the length of the name. That means if we sort in ascending order then the name that contains the less number of characters, should come first.

Suppose we have some hibernate generated query as below.

select this_.id as id1_0_0_, this_.roll_number as roll_number2_0_0_, this_.name as name3_0_0_ from student this_ order by this_.name asc

Now we want to add some query(LENGTH(name)) as string after order by and before asc, something like below.

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

Note – The above query will sort on basis of the length of the name.

Unfortunately, Hibernate Criteria doesn’t provide any APIs/direct ways to do this or similar kind of stuff.  We can modify our query by creating a custom order class.

Note – Make sure your query is running fine in SQL developer or any other database ide.

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

Creating Custom Order class.

CutomOrder.java

import org.hibernate.Criteria;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Order;

public class CustomOrder extends Order {

	private String propName;

	protected CustomOrder(String propertyName, boolean ascending) {
		super(propertyName, ascending);
		this.propName = propertyName;
	}

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

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

		return query.toString();
	}

	public static Order addCustomOrder(String propertyName, boolean ascending) {
		return new CustomOrder(propertyName, ascending);
	}

}

Expected response data after running the below example.

http://localhost:9091/student/allstudents

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

[
    {
        "id": 1,
        "studentName": "H",
        "rollNumber": "0126CS01"
    },
    {
        "id": 6,
        "studentName": "mu",
        "rollNumber": "0126CS01"
    },
    {
        "id": 12,
        "studentName": "ram",
        "rollNumber": "0126CS01"
    },
    {
        "id": 7,
        "studentName": "Rock",
        "rollNumber": "0126CS01"
    },
    {
        "id": 2,
        "studentName": "John",
        "rollNumber": "0126CS01"
    },
    {
        "id": 8,
        "studentName": "Simpy",
        "rollNumber": "0126CS01"
    },
    {
        "id": 4,
        "studentName": "sanja",
        "rollNumber": "0126CS01"
    },
    {
        "id": 9,
        "studentName": "Tiwari",
        "rollNumber": "0126CS01"
    },
    {
        "id": 3,
        "studentName": "mohandas",
        "rollNumber": "0126CS01"
    },
    {
        "id": 11,
        "studentName": "bibhukti",
        "rollNumber": "0126CS01"
    },
    {
        "id": 5,
        "studentName": "rajankumarsingh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 10,
        "studentName": "rohitkumarsinghrajput",
        "rollNumber": "0126CS01"
    }
]

Custom Order example for sorting in Hibernate using Spring Boot

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

<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>criteriasortingexamplename</groupId>
	<artifactId>criteriasortingexamplename</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>criteriasortingexamplename</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>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
	</dependencies>
</project>

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


}

Define 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();
}

Define Repository interface by extending CrudRepository interface i.e StudentRepository.java.

See more details about Spring Data JPA here.

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> {

}

Note – See here more about @Component, @Controller, @Service, and @Repository annotations here.

Define ServiceImpl class i.e StudentServiceImpl.java

package com.javatute.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.Criteria;
import org.hibernate.Session;
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);
		Criteria criteria = session.createCriteria(Student.class);
		criteria.addOrder(CustomOrder.addCustomOrder("studentName", true));

		// for normal ordering
		// criteria.addOrder(Order.asc("studentName"));

		List<Student> studentList = criteria.list();
		return studentList;
	}

}

Although session.createCriteria is deprecated, we are using it here to keep the example simple. Instead, we can use the JPA Criteria.

@Transaction – Check a separate tutorial here.

Define CustomOrder.java class.

This is the class where we are going to override toSqlString() method and will implement custom sorting logic.

CustomOrder.java

package com.javatute.impl;

import org.hibernate.Criteria;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Order;

public class CustomOrder extends Order {

	private String propName;

	protected CustomOrder(String propertyName, boolean ascending) {
		super(propertyName, ascending);
		this.propName = propertyName;
	}

	@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();
	}

	public static Order addCustomOrder(String propertyName, boolean ascending) {
		return new CustomOrder(propertyName, ascending);
	}

}

Hibernate provides an Order class for sorting purposes.

Define 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 {

}

@Configuration – This annotation is used for configuration purposes. See more details about @Configuration annotation here.

Define controller class 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;
	}

}

@RestController – This annotation is a combined form of @Controller and @ResponseBody. See more detail here.

Check more about @RequestMapping and @PatVarible.

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

}

Define properties file i.e application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/springbootcrudexample
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
server.port = 9091

You can also use application.yml as below.

spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: create
  datasource:
    url: jdbc:mysql://localhost:3306/springbootcrudexample
    username: root
    password: root
server:
   port: 9091 

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

Perform save operation first using below REST API(Use request data to perform save operation which has been given at the beginning of the post).

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

Request Data.

[
    {
        "studentName": "H",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "John",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "mohandas",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "sanja",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "rajankumarsingh",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "mu",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Rock",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Simpy",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "Tiwari",
        "rollNumber": "0126CS01"
    },    {
        "studentName": "rohitkumarsinghrajput",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "bibhukti",
        "rollNumber": "0126CS01"
    },
    {
        "studentName": "ram",
        "rollNumber": "0126CS01"
    }
]

Response data for save operation.

[
    {
        "id": 1,
        "studentName": "H",
        "rollNumber": "0126CS01"
    },
    {
        "id": 2,
        "studentName": "John",
        "rollNumber": "0126CS01"
    },
    {
        "id": 3,
        "studentName": "mohandas",
        "rollNumber": "0126CS01"
    },
    {
        "id": 4,
        "studentName": "sanja",
        "rollNumber": "0126CS01"
    },
    {
        "id": 5,
        "studentName": "rajankumarsingh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 6,
        "studentName": "mu",
        "rollNumber": "0126CS01"
    },
    {
        "id": 7,
        "studentName": "Rock",
        "rollNumber": "0126CS01"
    },
    {
        "id": 8,
        "studentName": "Simpy",
        "rollNumber": "0126CS01"
    },
    {
        "id": 9,
        "studentName": "Tiwari",
        "rollNumber": "0126CS01"
    },
    {
        "id": 10,
        "studentName": "rohitkumarsinghrajput",
        "rollNumber": "0126CS01"
    },
    {
        "id": 11,
        "studentName": "bibhukti",
        "rollNumber": "0126CS01"
    },
    {
        "id": 12,
        "studentName": "ram",
        "rollNumber": "0126CS01"
    }
]

http://localhost:9091/student/allstudents

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

[
    {
        "id": 1,
        "studentName": "H",
        "rollNumber": "0126CS01"
    },
    {
        "id": 6,
        "studentName": "mu",
        "rollNumber": "0126CS01"
    },
    {
        "id": 12,
        "studentName": "ram",
        "rollNumber": "0126CS01"
    },
    {
        "id": 7,
        "studentName": "Rock",
        "rollNumber": "0126CS01"
    },
    {
        "id": 2,
        "studentName": "John",
        "rollNumber": "0126CS01"
    },
    {
        "id": 8,
        "studentName": "Simpy",
        "rollNumber": "0126CS01"
    },
    {
        "id": 4,
        "studentName": "sanja",
        "rollNumber": "0126CS01"
    },
    {
        "id": 9,
        "studentName": "Tiwari",
        "rollNumber": "0126CS01"
    },
    {
        "id": 3,
        "studentName": "mohandas",
        "rollNumber": "0126CS01"
    },
    {
        "id": 11,
        "studentName": "bibhukti",
        "rollNumber": "0126CS01"
    },
    {
        "id": 5,
        "studentName": "rajankumarsingh",
        "rollNumber": "0126CS01"
    },
    {
        "id": 10,
        "studentName": "rohitkumarsinghrajput",
        "rollNumber": "0126CS01"
    }
]

Sorted data while get call.

That’s all about how to create custom Order in Hibernate.

Seed Docs here.

You may like other Hibernate/JPA sorting tutorials.

• Sorting in Hibernate.
• Sorting in Spring Data JPA using Spring Boot.
• @OrderBy Annotation in Hibernate for Sorting.
• How to sort using Criteria in Hibernate.

Other Hibernate/JPA tutorials.

• @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.

Spring Data JPA tutorials.

• Spring Data JPA greater than Example
• Spring Data JPA less than Example
• Spring Data JPA IsNull Example Using Spring Boot
• Spring Data findById() Vs getOne()
• Spring Data JPA CrudRepository findById()
• Spring Data JPA JpaRepository getOne()
• Spring Data CrudRepository saveAll() and findAll().
• Spring Data CrudRepository existsById()
• Spring Data JPA delete() vs deleteInBatch()
• Spring Data JPA deleteAll() Vs deleteAllInBatch()
• Spring Data JPA JpaRepository deleteAllInBatch()
• Spring Data JPA deleteInBatch() Example
• Spring Data JPA JpaRepository saveAndFlush() Example
• Spring Data JPA CrudRepository count() Example
• Spring Data JPA CrudRepository delete() and deleteAll()
• Spring Data JPA CrudRepository deleteById() Example
• CrudRepository findAllById() Example Using Spring Boot
• Spring Data CrudRepository save() Method.
• Sorting in Spring Data JPA using Spring Boot.
• Spring Data JPA example using spring boot.
• Spring Data JPA and its benefit.

Download the source code from github.

Summary – To create a custom Order in Hibernate we need to extend the Order class and need to override toSqlString() method. This is useful when we want to sort on basis of the length of some fields or maybe some other use case.