Ebooks

Spring Boot Data JPA sort tutorial

Spring Boot Data JPA sort tutorial shows how to sort query results in Spring Data JPA. Query results are sorted either with ORDER BY clause or with a Sort object.

Spring is a popular Java application framework and Spring Boot is an evolution of Spring that helps create stand-alone, production-grade Spring based applications easily.

Spring Data JPA

Spring Data JPA is part of the umbrella Spring Data project that makes it easier to implement JPA based repositories. Spring Data JPA uses JPA to store data in a relational database. It can create repository implementations automatically, at runtime, from a repository interface.

Spring Data JPA sorting

In Spring Data JPA query results can be sorted in two ways:

Spring Boot Data JPA sort example

The following application retrieves cities sorted in ascending order.

pom.xml
src
├───main
│   ├───java
│   │   └───com
│   │       └───zetcode
│   │           │   Application.java
│   │           ├───controller
│   │           │       MyController.java
│   │           ├───model
│   │           │       City.java
│   │           ├───repository
│   │           │       CityRepository.java
│   │           └───service
│   │                   CityService.java
│   │                   ICityService.java
│   └───resources
│           application.properties
│           data-h2.sql
│           schema-h2.sql
└───test
    └───java

This is the project structure.

pom.xml
<?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
http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>
    <groupId>com.zetcode</groupId>
    <artifactId>springbootdatajpasort</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.1.RELEASE</version>
    </parent>

    <dependencies>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

        <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>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

This is the Maven pom.xml file.

resources/application.properties
spring.main.banner-mode=off
spring.datasource.platform=h2
spring.jpa.hibernate.ddl-auto=none

In the main properties file, we turn off the Spring Boot banner with the spring.main.banner-mode property. The spring.datasource.platform is set to h2, so the database name is present in the database initialization scripts. Since we initialize scripts from SQL code, we turn off automatic creation of tables from entities by settting spring.jpa.hibernate.ddl-auto to none.

com/zetcode/model/City.java
package com.zetcode.model;

import java.util.Objects;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "cities")
public class City {

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

    private String name;
    private int population;

    public City() {
    }

    public City(String name, int population) {
        this.name = name;
        this.population = population;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public int getPopulation() {
        return population;
    }

    public void setPopulation(int population) {
        this.population = population;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 79 * hash + Objects.hashCode(this.id);
        hash = 79 * hash + Objects.hashCode(this.name);
        hash = 79 * hash + this.population;
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final City other = (City) obj;
        if (this.population != other.population) {
            return false;
        }
        if (!Objects.equals(this.name, other.name)) {
            return false;
        }
        return Objects.equals(this.id, other.id);
    }

    @Override
    public String toString() {

        var builder = new StringBuilder();
        builder.append("City{id=").append(id).append(", name=")
                .append(name).append(", population=")
                .append(population).append("}");

        return builder.toString();
    }
}

This is the City entity.

resources/schema-h2.sql
CREATE TABLE cities(id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255), population INT);

When the application is started, the schema-h2.sql script is executed. It creates a new database table.

resources/data-h2.sql
INSERT INTO cities(name, population) VALUES('Bratislava', 432000);
INSERT INTO cities(name, population) VALUES('Budapest', 1759000);
INSERT INTO cities(name, population) VALUES('Prague', 1280000);
INSERT INTO cities(name, population) VALUES('Warsaw', 1748000);
INSERT INTO cities(name, population) VALUES('Los Angeles', 3971000);
INSERT INTO cities(name, population) VALUES('New York', 8550000);
INSERT INTO cities(name, population) VALUES('Edinburgh', 464000);
INSERT INTO cities(name, population) VALUES('Suzhou', 4327066);
INSERT INTO cities(name, population) VALUES('Zhengzhou', 4122087);
INSERT INTO cities(name, population) VALUES('Berlin', 3671000);

Later, the data-h2.sql file is executed. It fills the table with data.

com/zetcode/service/ICityService.java
package com.zetcode.service;

import com.zetcode.model.City;

import java.util.List;

public interface ICityService {

    List<City> findAllOrderByPopulationAsc();
    List<City> findAllOrderByNameAsc();
}

ICityService contains two contract methods.

com/zetcode/service/CityService.java
package com.zetcode.service;

import com.zetcode.model.City;
import com.zetcode.repository.CityRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CityService implements ICityService {

    @Autowired
    private CityRepository repository;

    @Override
    public List<City> findAllOrderByPopulationAsc() {
        return repository.findAllOrderByPopulationAsc();
    }

    @Override
    public List<City> findAllOrderByNameAsc() {

        var sort = new Sort(Sort.Direction.ASC, "name");
        return repository.findAllOrderByNameAsc(sort);
    }
}

Here we have the implementations of the two sorting methods.

@Override
public List<City> findAllOrderByNameAsc() {

    var sort = new Sort(Sort.Direction.ASC, "name");
    return repository.findAllOrderByNameAsc(sort);
}

The Sort object sorts the query results by name in ascending order. The Sort is passed to the method as a parameter.

com/zetcode/repository/CityRepository.java
package com.zetcode.repository;

import com.zetcode.model.City;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CityRepository extends CrudRepository<City, Long> {

    @Query("FROM City ORDER BY population ASC")
    List<City> findAllOrderByPopulationAsc();

    @Query("FROM City")
    List<City> findAllOrderByNameAsc(Sort sort);
}

CityRepository has two sorting methods. In the first case, we use the ORDER BY clause. In the second case, we use the Sort object.

com/zetcode/controller/MyController.java
package com.zetcode.controller;

import com.zetcode.model.City;
import com.zetcode.service.ICityService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class MyController {

    @Autowired
    private ICityService cityService;

    @GetMapping(value = "/cities")
    public List<City> getCitiesByPopulation() {

        return cityService.findAllOrderByPopulationAsc();
    }

    @GetMapping(value = "/cities2")
    public List<City> getCitiesByName() {

        return cityService.findAllOrderByNameAsc();
    }
}

The controller is RESTFul and returns data in a JSON format.

com/zetcode/Application.java
package com.zetcode;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application  {
    
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Application is the entry point which sets up the Spring Boot application.

We run the application with mvn -q spring-boot:run.

$ curl localhost:8080/cities
[{"id":1,"name":"Bratislava","population":432000},{"id":7,"name":"Edinburgh","population":464000},
{"id":3,"name":"Prague","population":1280000},{"id":4,"name":"Warsaw","population":1748000},
{"id":2,"name":"Budapest","population":1759000},{"id":10,"name":"Berlin","population":3671000},
{"id":5,"name":"LosAngeles","population":3971000},{"id":9,"name":"Zhengzhou","population":4122087},
{"id":8,"name":"Suzhou","population":4327066},{"id":6,"name":"NewYork","population":8550000}]

In this output, the cities are sorted by population in asceding order.

$ curl localhost:8080/cities2
[{"id":10,"name":"Berlin","population":3671000},{"id":1,"name":"Bratislava","population":432000},
{"id":2,"name":"Budapest","population":1759000},{"id":7,"name":"Edinburgh","population":464000},
{"id":5,"name":"LosAngeles","population":3971000},{"id":6,"name":"NewYork","population":8550000},
{"id":3,"name":"Prague","population":1280000},{"id":8,"name":"Suzhou","population":4327066},
{"id":4,"name":"Warsaw","population":1748000},{"id":9,"name":"Zhengzhou","population":4122087}]

In this output, the cities are sorted by name in asceding order.

This tutorial showed how to sort query results in Spring Boot Data JPA. You might also be interested in the related tutorials: Spring Boot Data JPA @Query tutorial, Spring Boot Data JPA @NamedQuery tutorial, Java tutorial, or list all Spring Boot tutorials.