Spring Data JPA Sort/Order by multiple Columns

In previous tutorial, we’ve known how to build Spring Boot Rest CRUD Apis with Spring Data JPA. Today I will show you how to sort/order result by multiple Columns in Spring Boot with Spring Data JPA. You also know way to apply sorting and paging together.

More Practice:
– Spring Boot Token based Authentication with Spring Security & JWT
– Spring Boot One To One example with JPA, Hibernate
– Spring Boot One To Many example with JPA, Hibernate
– Spring Boot Many to Many example with JPA, Hibernate
– Spring Boot Unit Test for JPA Repository
– Spring Boot Unit Test for Rest Controller
– Documentation: Spring Boot Swagger 3 example
– Caching: Spring Boot Redis Cache example
– Validation: Spring Boot Validate Request Body

Contents

Spring Data Sort multiple Columns example Overview
Order by multiple Columns with Spring Data JPA
Spring Data Sort and Order
Paging and Sorting
Spring Boot Application
Data Model
Repository that supports Paging and Sorting
Controller with Sort/Order By Multiple Columns
Conclusion
Further Reading
Source Code

Spring Data Sort multiple Columns example Overview

Assume that we have tutorials table in database like this:

Here are some url samples for order by single/multiple Columns (with/without paging), sort by Ascending or Descending:

/api/tutorials
sort by [id, descending] (default)
/api/tutorials?sort=title,asc
sort by [title, ascending]
/api/tutorials?sort=published,desc&sort=title,asc
order by column [published, descending], then order by column [title, ascending]
/api/tutorials?page=0&size=3&sort=published,desc&sort=title,asc
order by column [published, descending], then order by column [title, ascending] together with pagination

In this tutorial, to help you have a clear idea in Sorting using Spring Boot, I will create separated endpoints with different response structure:

for Sorting by Multiple Columns: /api/sortedtutorials

[
    {
        "id": 8,
        "title": "Spring Data JPA Tut#8",
        "description": "Tut#8 Description",
        "published": true
    },
    ...
    ...
    ...
    {
        "id": 1,
        "title": "Spring Boot Tut#1",
        "description": "Tut#1 Description",
        "published": false
    }
]

for Paging & Sorting coming together: /api/tutorials

{
    "totalItems": 8,
    "tutorials": [
        ...
    ],
    "totalPages": 3,
    "currentPage": 1
}

Please visit: Spring Boot Pagination and Sorting example

Let’s look at the result after building this Spring Boot Application:

– Get all Tutorials with default order [id, descending]:

– Get all Tutorials, sort by single column [title, ascending]:

– Get all Tutorials, sort by multiple columns [published, descending] & [title, ascending]:

Order by multiple Columns with Spring Data JPA

To help us deal with this situation, Spring Data JPA provides way to implement pagination with PagingAndSortingRepository.

PagingAndSortingRepository extends CrudRepository to provide additional methods to retrieve entities using the sorting abstraction. So you can add a special Sort parameter to your query method.

public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
  Iterable<T> findAll(Sort sort);
}

findAll(Sort sort): returns a Iterable of entities meeting the sorting condition provided by Sort object.

You can also define more derived and custom query methods with additional Sort parameter. For example, the following method returns List of Tutorials which title contains a given string:

List<Tutorial> findByTitleContaining(String title, Sort sort);

You can find more supported keywords inside method names here.

Let’s continue to explore Sort class.

Spring Data Sort and Order

The Sort class provides sorting options for database queries with more flexibility in choosing single/multiple sort columns and directions (ascending/descending).

For example, we use by(), descending(), and() methods to create Sort object and pass it to Repository.findAll():

// order by 'published' column - ascending
List<Tutorial> tutorials =
     tutorialRepository.findAll(Sort.by("published"));

// order by 'published' column, descending
List<Tutorial> tutorials =
     tutorialRepository.findAll(Sort.by("published").descending());

// order by 'published' column - descending, then order by 'title' - ascending
List<Tutorial> tutorials =
     tutorialRepository.findAll(Sort.by("published").descending().and(Sort.by("title")));

We can also create a new Sort object with List of Order objects.

List<Order> orders = new ArrayList<Order>();

Order order1 = new Order(Sort.Direction.DESC, "published");
orders.add(order1);

Order order2 = new Order(Sort.Direction.ASC, "title");
orders.add(order2);

List<Tutorial> tutorials = tutorialRepository.findAll(Sort.by(orders));

Paging and Sorting

What if we want todo both sorting and paging the data?

CrudRepository also provides additional methods to retrieve entities using the pagination abstraction.

public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
  Page<T> findAll(Pageable pageable);
}

findAll(Pageable pageable): returns a Page of entities meeting the paging condition provided by Pageable object.

Spring Data also supports many useful Query Creation from method names that we’re gonna use to filter result in this example such as:

Page<Tutorial> findByPublished(boolean published, Pageable pageable);
Page<Tutorial> findByTitleContaining(String title, Pageable pageable);

For more details about Pagination and Sorting, please visit:
Spring Boot Pagination and Sorting example

Spring Boot Application

You can follow step by step, or get source code in this post:
Spring Boot, Spring Data JPA – Rest CRUD API example

The Spring Project contains structure that we only need to add some changes to make the pagination work well.

Or you can get the new Github source code (including paging and sorting) at the end of this tutorial.

Data Model

This is the Tutorial entity that we’re gonna work:

model/Tutorial.java

package com.bezkoder.spring.data.jpa.pagingsorting.model;

import javax.persistence.*;
// import jakarta.persistence.*; // for Spring Boot 3

@Entity
@Table(name = "tutorials")
public class Tutorial {

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

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

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

  @Column(name = "published")
  private boolean published;

  public Tutorial() {

  }

  public Tutorial(String title, String description, boolean published) {
    this.title = title;
    this.description = description;
    this.published = published;
  }

  public long getId() {
    return id;
  }

  public String getTitle() {
    return title;
  }

  public void setTitle(String title) {
    this.title = title;
  }

  public String getDescription() {
    return description;
  }

  public void setDescription(String description) {
    this.description = description;
  }

  public boolean isPublished() {
    return published;
  }

  public void setPublished(boolean isPublished) {
    this.published = isPublished;
  }

  @Override
  public String toString() {
    return "Tutorial [id=" + id + ", title=" + title + ", desc=" + description + ", published=" + published + "]";
  }
}

Repository that supports Paging and Sorting

Early in this tutorial, we know PagingAndSortingRepository, but in this example, for keeping the continuity and taking advantage Spring Data JPA, we continue to use JpaRepository which extends PagingAndSortingRepository interface.

repository/TutorialRepository.java

package com.bezkoder.spring.data.jpa.pagingsorting.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;

import com.bezkoder.spring.data.jpa.pagingsorting.model.Tutorial;

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
  Page<Tutorial> findByPublished(boolean published, Pageable pageable);

  Page<Tutorial> findByTitleContaining(String title, Pageable pageable);
  
  List<Tutorial> findByTitleContaining(String title, Sort sort);
}

In the code above, we use add pageable parameter with Spring Query Creation to find all Tutorials which title containing input string.

More Derived queries at:
JPA Repository query example in Spring Boot

Custom query with @Query annotation:
Spring JPA @Query example: Custom query in Spring Boot

Controller with Sort/Order By Multiple Columns

To get multiple sort request parameters, we use @RequestParam String[] sort with defaultValue = "id,desc".

Before writing the Controller method to handle the case, let’s see what we retrieve with the parameters:

?sort=column1,direction1: sorting single column
String[] sort is an array with 2 elements: [“column1”, “direction1”]
?sort=column1,direction1&sort=column2,direction2: sorting multiple columns
String[] sort is also an array with 2 elements: [“column1, direction1”, “column2, direction2”]

That’s why we need to check if the first item in the array contains "," or not.

We also need to convert "asc"/"desc" into Sort.Direction.ASC/Sort.Direction.DES for working with Sort.Order class.

controller/TutorialController.java

package com.bezkoder.spring.data.jpa.pagingsorting.controller;

import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
...
import com.bezkoder.spring.data.jpa.pagingsorting.model.Tutorial;
import com.bezkoder.spring.data.jpa.pagingsorting.repository.TutorialRepository;

@RestController
@RequestMapping("/api")
public class TutorialController {

  @Autowired
  TutorialRepository tutorialRepository;

  private Sort.Direction getSortDirection(String direction) {
    if (direction.equals("asc")) {
      return Sort.Direction.ASC;
    } else if (direction.equals("desc")) {
      return Sort.Direction.DESC;
    }

    return Sort.Direction.ASC;
  }

  @GetMapping("/sortedtutorials")
  public ResponseEntity<List<Tutorial>> getAllTutorials(@RequestParam(defaultValue = "id,desc") String[] sort) {

    try {
      List<Order> orders = new ArrayList<Order>();

      if (sort[0].contains(",")) {
        // will sort more than 2 columns
        for (String sortOrder : sort) {
          // sortOrder="column, direction"
          String[] _sort = sortOrder.split(",");
          orders.add(new Order(getSortDirection(_sort[1]), _sort[0]));
        }
      } else {
        // sort=[column, direction]
        orders.add(new Order(getSortDirection(sort[1]), sort[0]));
      }

      List<Tutorial> tutorials = tutorialRepository.findAll(Sort.by(orders));

      if (tutorials.isEmpty()) {
        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
      }

      return new ResponseEntity<>(tutorials, HttpStatus.OK);
    } catch (Exception e) {
      return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
    }
  }

}

How about controller that brings pagination and sorting together?
Please visit: Spring Boot Pagination and Sorting example

Conclusion

In this post, we have learned how to sort/order by multiple columns in Spring Boot application using Spring Data JPA, Sort class and Pageable interface.

We also see that JpaRepository supports a great way to make sorting, paging and filter methods without need of boilerplate code.

Custom query with @Query annotation:
Spring JPA @Query example: Custom query in Spring Boot

You can also know how to:
– find by multiple Columns using JPA Repository
– paging and filter in this article.
– handle exception in this post.
– deploy this Spring Boot App on AWS (for free) with this tutorial.

Happy learning! See you again.

Source Code

You can find the complete source code for this tutorial on Github.

More Derived queries at:
JPA Repository query example in Spring Boot

You can use the code as an additional feature for following Posts:
– Spring Boot, Spring Data JPA, H2 example
– Spring Boot, Spring Data JPA, MySQL example
– Spring Boot, Spring Data JPA, PostgreSQL example
– Spring Boot, Spring Data JPA, SQL Server example
– Spring Boot, Spring Data JPA, Oracle example

Associations:
– Spring Boot One To One example with JPA, Hibernate
– Spring Boot One To Many example with JPA, Hibernate
– Spring Boot Many to Many example with JPA, Hibernate

Unit Test:
– Spring Boot Unit Test for JPA Repository
– Spring Boot Unit Test for Rest Controller

Documentation: Spring Boot + Swagger 3 example (with OpenAPI 3)
Caching: Spring Boot Redis Cache example
Validation: Spring Boot Validate Request Body

5 thoughts to “Spring Data JPA Sort/Order by multiple Columns | Spring Boot”

Webba says:

June 4, 2021 at 11:14 am

Thx for the tutorial!
Faraz Durrani says:

February 24, 2021 at 5:19 pm

This is great bezkoder concise and to the point.
rariq says:

December 28, 2020 at 8:06 am

I just want to thank you for this tutorial.
Codeurx says:

August 24, 2020 at 8:23 pm

Very thanks to you , the best tutorials
James Hackett says:

August 12, 2020 at 12:23 am

THE JAVA series is excellent with spring