Sorting in Hibernate using HQL

In this post, we will see Sorting in hibernate using HQL and Spring Boot.

Sorting in Hibernate using HQL

Let’s see what we are going to do here. We will have an entity(Student) which contains four fields(id, name, rollNumber and university). First, we will save some entity and then we will perform sorting while fetching the entities from database using HQL.

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

}

 

 

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

 

Let’s see the complete code as below.

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

 

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 an example of Sorting in hibernate using HQL, Spring Boot and Oracle.

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

Sorting in Hibernate using HQL


 

 

Student.java

package com.netsurfingzone.entity;

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

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

	public int getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getRollNumber() {
		return rollNumber;
	}

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

	public String getUniversity() {
		return university;
	}

	public void setUniversity(String university) {
		this.university = university;
	}

}

StudentController.java

package com.netsurfingzone.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.netsurfingzone.entity.Student;
import com.netsurfingzone.service.StudentService;

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

	@Autowired
	private StudentService studentService;

	@RequestMapping(value = "/saveall", method = RequestMethod.POST)
	@ResponseBody
	public List<Student> saveAllStudents(@RequestBody List<Student> studentList) {
		List<Student> studentResponse = (List<Student>) studentService.saveAllStudent(studentList);
		return studentResponse;
	}
	
	
	@RequestMapping(value = "/getall", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> getAllStudents() {
		List<Student> studentResponse = (List<Student>) studentService.getAllStudents();
		return studentResponse;
	}

}

StudentRepository.java – interface

package com.netsurfingzone.repository;

import java.io.Serializable;

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

import com.netsurfingzone.entity.Student;

@Repository
public interface StudentRepository extends CrudRepository<Student, Serializable> {

}

StudentService.java – interface

package com.netsurfingzone.service;

import java.util.List;

import org.springframework.stereotype.Component;

import com.netsurfingzone.entity.Student;

@Component
public interface StudentService {
	public List<Student> saveAllStudent(List<Student> studentList);
	public List<Student> getAllStudents();
	
}

 

StudentServiceImpl.java

package com.netsurfingzone.impl;

import java.util.List;

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

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

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

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

	@Autowired
	private StudentRepository studentRepository;
	
	@PersistenceContext
	EntityManager entityManager;

	@Transactional
	public List<Student> saveAllStudent(List<Student> studentList) {
		List<Student> response = (List<Student>) studentRepository.saveAll(studentList);
		return response;
	}
	
	@Transactional
	public List<Student> getAllStudents() {
		String hql = "FROM Student s ORDER BY s.name";
		Session session = entityManager.unwrap(Session.class);
		Query query = session.createQuery(hql);
		List<Student> studentResponse = query.getResultList();
		return studentResponse;
	}
	

}

SpringMain.java


package com.netsurfingzone.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.netsurfingzone.entity")
public class SpringMain {
	public static void main(String[] args) {

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

}

 

JpaConfig.java

package com.netsurfingzone.config;

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

@Configuration
@EnableJpaRepositories(basePackages = "com.netsurfingzone.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/saveall

Request Data –

[
    {
        "name": "Hiteshdo",
        "rollNumber": "0126CS01",
        "university":"rgtu"
    },
    {
        "name": "Johnhjhjhjhj",
        "rollNumber": "0126CS02",
        "university":"rgtu"
    },
    {
        "name": "Mohankkkkkkkkkkkkkk",
        "rollNumber": "0126CS03",
        "university":"rgtu"
    },
    {
        "name": "Nagesh",
        "rollNumber": "0126CS04",
        "university":"rgtu"
    },
    {
        "name": "s",
        "rollNumber": "0126CS05",
        "university":"rgtu"
    },
    {
        "name": "Ranakum",
        "rollNumber": "0126CS06",
        "university":"rgtu"
    },
    {
        "name": "Roc",
        "rollNumber": "0126CS07",
        "university":"rgtu"
    },
    {
        "name": "Simpy",
        "rollNumber": "0126CS08",
        "university":"rgtu"
    },
    {
        "name": "Tiwari",
        "rollNumber": "0126CS09",
        "university":"rgtu"
    },    {
        "name": "Appu",
        "rollNumber": "0126CS10",
        "university":"rgtu"
    },
    {
        "name": "Babloo",
        "rollNumber": "0126CS11",
        "university":"rgtu"
    },
    {
        "name": "Ga",
        "rollNumber": "0126CS12",
        "university":"rgtu"
    }
]

Response Data –

[
    {
        "id": 1,
        "name": "Hiteshdo",
        "rollNumber": "0126CS01",
        "university": "rgtu"
    },
    {
        "id": 2,
        "name": "Johnhjhjhjhj",
        "rollNumber": "0126CS02",
        "university": "rgtu"
    },
    {
        "id": 3,
        "name": "Mohankkkkkkkkkkkkkk",
        "rollNumber": "0126CS03",
        "university": "rgtu"
    },
    {
        "id": 4,
        "name": "Nagesh",
        "rollNumber": "0126CS04",
        "university": "rgtu"
    },
    {
        "id": 5,
        "name": "s",
        "rollNumber": "0126CS05",
        "university": "rgtu"
    },
    {
        "id": 6,
        "name": "Ranakum",
        "rollNumber": "0126CS06",
        "university": "rgtu"
    },
    {
        "id": 7,
        "name": "Roc",
        "rollNumber": "0126CS07",
        "university": "rgtu"
    },
    {
        "id": 8,
        "name": "Simpy",
        "rollNumber": "0126CS08",
        "university": "rgtu"
    },
    {
        "id": 9,
        "name": "Tiwari",
        "rollNumber": "0126CS09",
        "university": "rgtu"
    },
    {
        "id": 10,
        "name": "Appu",
        "rollNumber": "0126CS10",
        "university": "rgtu"
    },
    {
        "id": 11,
        "name": "Babloo",
        "rollNumber": "0126CS11",
        "university": "rgtu"
    },
    {
        "id": 12,
        "name": "Ga",
        "rollNumber": "0126CS12",
        "university": "rgtu"
    }
]

 

 

Sorting in Hibernate using HQL

 

http://localhost:9091/student/getall – Observe the below response data. it’s in sorting order.

[
    {
        "id": 10,
        "name": "Appu",
        "rollNumber": "0126CS10",
        "university": "rgtu"
    },
    {
        "id": 11,
        "name": "Babloo",
        "rollNumber": "0126CS11",
        "university": "rgtu"
    },
    {
        "id": 12,
        "name": "Ga",
        "rollNumber": "0126CS12",
        "university": "rgtu"
    },
    {
        "id": 1,
        "name": "Hiteshdo",
        "rollNumber": "0126CS01",
        "university": "rgtu"
    },
    {
        "id": 2,
        "name": "Johnhjhjhjhj",
        "rollNumber": "0126CS02",
        "university": "rgtu"
    },
    {
        "id": 3,
        "name": "Mohankkkkkkkkkkkkkk",
        "rollNumber": "0126CS03",
        "university": "rgtu"
    },
    {
        "id": 4,
        "name": "Nagesh",
        "rollNumber": "0126CS04",
        "university": "rgtu"
    },
    {
        "id": 6,
        "name": "Ranakum",
        "rollNumber": "0126CS06",
        "university": "rgtu"
    },
    {
        "id": 7,
        "name": "Roc",
        "rollNumber": "0126CS07",
        "university": "rgtu"
    },
    {
        "id": 8,
        "name": "Simpy",
        "rollNumber": "0126CS08",
        "university": "rgtu"
    },
    {
        "id": 9,
        "name": "Tiwari",
        "rollNumber": "0126CS09",
        "university": "rgtu"
    },
    {
        "id": 5,
        "name": "s",
        "rollNumber": "0126CS05",
        "university": "rgtu"
    }
]

 

Generated Query –

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

 

That’s all about Sorting in hibernate using HQL and Spring Boot.

You may like.

 

HQL Docs.