Spring Data JPA Sort Multiple Columns with Examples

Hey guys in this post we will discuss and implement Sorting multiple columns in Data JPA with examples.

Table of Contents

Overview

Data JPA provides Sorting option out of the box. To add Sorting option to our Repositories, we need to extend the PagingAndSortingRepository<T, ID> interface rather than the basic CrudRepository<T, ID> interface.

By extending the repository PagingAndSortingRepository<T, ID>, we get the following method –

List<Laptop> findAll(Sort sort);

Returns a sorted list of laptops.

Even we can use JPARepository<T, ID> instead, which extends the PagingAndSortingRepository<T, ID>

Once we extend the repository, we just need to create the Sort object which provides the following static method –

Sort sort = Sort.by(Direction direction, String... properties);

Creates a new Sort for the given Sort.Direction and the name of the property on which we need to sort. To sort multiple columns, we need to pass the multiple column names with comma-separated.

Sort sort = Sort.by(Direction.DESC, field1, field2)

Here first, it will sort based on field1, and then it will sort based on field2. On both properties, it will sort on Descending order.

what if we want to sort each property in a different direction?

We can do that with the help of the Order class, which is the inner class of the Sort class.

Order order = new Order(Direction.DESC, field1);

Creates a new Sort.Order instance. if the order is null then the order defaults to Sort.DEFAULT_DIRECTION

Usage of Interfaces, Classes, and Methods

PagingAndSortingRepository<T, ID>: Extension of CrudRepository<T, ID> to provide additional methods to retrieve entities using the pagination and sorting abstraction.
Sort: Sort option for queries. You have to provide at least a list of properties to sort for that must not include null or empty strings. The direction defaults to DEFAULT_DIRECTION.
Sort.DIRECTION: Enumeration for sort directions.
by(): Creates a new Sortfor the given Sort.Direction.
Order: PropertyPath implements the pairing of an Sort.Direction and a property. It is used to provide input for Sort

Complete example

Let’s create a step-by-step spring boot project and create different finder methods for various fields.

Create database and insert sample data

Open MySQL workbench and execute the following commands

CREATE DATABASE mydb;

USE mydb;

CREATE TABLE tbl_laptops(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255) NOT NULL,
    brand VARCHAR(255) NOT NULL,
    price DOUBLE(10, 2) NOT NULL,
    created_at DATE NOT NULL
);

INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Dell Inspiron", "dell company laptop", "Dell", 60000.00, '2021-02-10');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Dell XPS", "dell company laptop", "Dell", 70000.00, '2020-12-31');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Macbook Air", "apple company laptop", "Apple", 85000.00, '2021-01-31');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Macbook Pro", "apple company laptop", "Apple", 160000.00, '2021-12-25');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("HP", "hp company laptop", "HP", 50000.00, '2021-02-10');
INSERT INTO tbl_laptops(name, description, brand, price, created_at)
VALUES("Lenovo", "lenovo company laptop", "Lenovo", 50000.00, '2020-11-21');

SELECT * FROM tbl_laptops;

We have created a table tbl_laptops that contains 5 fields. We have inserted the sample data as well.

Create spring boot project

There are many different ways to create a spring boot application, you can follow the below articles to create one –

>> Create spring boot application using Spring initializer
>> Create spring boot application in Spring tool suite [STS]
>> Create spring boot application in IntelliJ IDEA

Add maven dependencies

Open pom.xml and add the following dependencies –

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.4</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>in.bushansirgur</groupId>
	<artifactId>findbyfieldname</artifactId>
	<version>v1</version>
	<name>findbyfieldname</name>
	<description>Spring boot data jpa find by field name</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

spring-boot-starter-web dependency for building web applications using Spring MVC. It uses the tomcat as the default embedded container.

spring-boot-devtools dependency for automatic reloads or live reload of applications. spring-boot-starter-data-jpa dependency is a starter for using Spring Data JPA with Hibernate. lombok dependency is a java library that will reduce the boilerplate code that we usually write inside every entity class like setters, getters, and toString()

Create an entity class

Create Laptop.java inside the in.bushansirgur.springboot.entity package and add the following content

package in.bushansirgur.springboot.entity;

import java.math.BigDecimal;
import java.sql.Date;

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

import lombok.Data;

@Entity
@Table(name="tbl_laptops")
@Data
public class Laptop {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;
	
	private String name;
	
	private String brand;
	
	private String description;
	
	private BigDecimal price;
	
	@Column(name="created_at")
	private Date createdAt;
}

We have added @Data annotation which is a Lombok annotation, that will automatically create setters, getters, toString(), and equals() for us.

@Entity is a mandatory annotation that indicates that this class is a JPA entity and is mapped with a database table.

@Table annotation is an optional annotation that contains the table info like table name.

@Id annotation is a mandatory annotation that marks a field as the primary key

Create a Repository

Create an interface LaptopRepository.java inside the in.bushansirgur.springboot.repos package and add the following content

package in.bushansirgur.springboot.repos;

import java.math.BigDecimal;
import java.util.List;
import java.sql.Date;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import in.bushansirgur.springboot.entity.Laptop;

@Repository
public interface LaptopRepository extends JpaRepository<Laptop, Long> {
	
}

Create a Rest controller

Create LaptopController.java inside the in.bushansirgur.springboot.controller package and add the following content

package in.bushansirgur.springboot.controller;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.domain.Sort.Order;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import in.bushansirgur.springboot.entity.Laptop;
import in.bushansirgur.springboot.repos.LaptopRepository;

@RestController
public class LaptopController {
	
	@Autowired
	LaptopRepository lRepo;
	
	@GetMapping("/laptopsByCols")
	public List<Laptop> getAllByCols (@RequestParam String field1, @RequestParam String field2) {
		return lRepo.findAll(Sort.by(Direction.DESC, field1, field2));
	}
	
	@GetMapping("/laptopsByDirections")
	public List<Laptop> getAllByDirections (@RequestParam String field1, @RequestParam String field2) {
		List<Order> orders = new ArrayList<>();
		orders.add(new Order(Direction.DESC, field1));
		orders.add(new Order(Direction.ASC, field2));
		return lRepo.findAll(Sort.by(orders));
	}
	
}

Here we have created 2 handler methods, getAllByCols() and getAllByDirections(). The first handler method will sort the multiple columns in the same direction. The second handler method will sort the multiple columns in different directions.

Run the app

Run the application using the below maven command –

mvn spring-boot:run

Open the browser and enter the following URL –

localhost:8080/laptopsByCols?field1=brand&field=price

[
    {
        "id": 6,
        "name": "Lenovo",
        "description": "lenovo company laptop",
        "brand": "Lenovo",
        "price": 50000.0,
        "date": "2020-11-21"
    },
    {
        "id": 5,
        "name": "HP",
        "description": "hp company laptop",
        "brand": "HP",
        "price": 50000.0,
        "date": "2021-02-10"
    },
    {
        "id": 2,
        "name": "Dell XPS",
        "description": "dell company laptop",
        "brand": "Dell",
        "price": 70000.0,
        "date": "2020-12-31"
    },
    {
        "id": 1,
        "name": "Dell Inspiron",
        "description": "dell company laptop",
        "brand": "Dell",
        "price": 60000.0,
        "date": "2021-02-10"
    },
    {
        "id": 4,
        "name": "Macbook Pro",
        "description": "apple company laptop",
        "brand": "Apple",
        "price": 160000.0,
        "date": "2021-12-25"
    },
    {
        "id": 3,
        "name": "Macbook Air",
        "description": "apple company laptop",
        "brand": "Apple",
        "price": 85000.0,
        "date": "2021-01-31"
    }
]

localhost:8080/laptopsByDirections?field1=brand&field=price

[
    {
        "id": 6,
        "name": "Lenovo",
        "description": "lenovo company laptop",
        "brand": "Lenovo",
        "price": 50000.0,
        "date": "2020-11-21"
    },
    {
        "id": 5,
        "name": "HP",
        "description": "hp company laptop",
        "brand": "HP",
        "price": 50000.0,
        "date": "2021-02-10"
    },
    {
        "id": 1,
        "name": "Dell Inspiron",
        "description": "dell company laptop",
        "brand": "Dell",
        "price": 60000.0,
        "date": "2021-02-10"
    },
    {
        "id": 2,
        "name": "Dell XPS",
        "description": "dell company laptop",
        "brand": "Dell",
        "price": 70000.0,
        "date": "2020-12-31"
    },
    {
        "id": 3,
        "name": "Macbook Air",
        "description": "apple company laptop",
        "brand": "Apple",
        "price": 85000.0,
        "date": "2021-01-31"
    },
    {
        "id": 4,
        "name": "Macbook Pro",
        "description": "apple company laptop",
        "brand": "Apple",
        "price": 160000.0,
        "date": "2021-12-25"
    }
]

Partners: 0x.bet casino med zimpler faktura pikakasino bonukset liste over interessante slots uden gratis spins danmark casino ohne lizenz bedste udenlandske casinoer funky time casino crazy time cronologia casinoohnelizenz crazy time diretta

Post Views: 19,466