PagingAndSortingRepository findAll Sort By

findAll(Sort.by("properties")) method defined in PagingAndSortingRepository interface and implemented in the SimpleJpaRepository class is used to retrieve all the records of the entity from the underlying database sorted on the property provided. The default direction is Ascending

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple ( apple_id BIGINT NOT NULL, apple_name VARCHAR(255) DEFAULT NULL, taste VARCHAR(255) DEFAULT NULL, PRIMARY KEY (apple_id)) ENGINE=INNODB;
CREATE TABLE hibernate_sequence (next_val BIGINT) ENGINE=INNODB;
INSERT INTO hibernate_sequence VALUES ( 1 );
INSERT INTO apple (apple_id, apple_name, taste) VALUES (1, "Macintosh", "tangy");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (2, "Macintosh", "sweet");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (3, "Macintosh", "juicy");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (4, "Macintosh", "tart");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (5, "Fuji", "tangy");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (6, "Fuji", "sweet");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (7, "Fuji", "juicy");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (8, "Fuji", "tart");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (9, "Gala", "tangy");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (10, "Gala", "sweet");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (11, "Gala", "juicy");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (12, "Gala", "tart");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (13, "Jonagold", "tangy");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (14, "Jonagold", "sweet");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (15, "Jonagold", "juicy");
INSERT INTO apple (apple_id, apple_name, taste) VALUES (16, "Jonagold", "tart");
package com.sujan.example.jpa.controller;

import com.sujan.example.jpa.entity.Apple;
import com.sujan.example.jpa.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class AppleController {
    @Autowired
    private AppleRepository appleRepository;

    @GetMapping
    void findAllSortBy() {
        List<Apple> all = appleRepository.findAll(Sort.by("appleName"));
        all.forEach(System.out::println);
    }
}
package com.sujan.example.jpa.entity;

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

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

@Data
@ToString
@Entity
@NoArgsConstructor
public class Apple {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long appleId;
    private String appleName;

    public Apple(String appleName) {
        this.appleName = appleName;
    }
}
package com.sujan.example.jpa.repository;

import com.sujan.example.jpa.entity.Apple;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
}
package com.sujan.example.jpa;

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

@SpringBootApplication
public class JpaApplication {
    public static void main(String[] args) {
        SpringApplication.run(JpaApplication.class, args);
    }
}
spring.datasource.url=jdbc:mysql://localhost:3306/AppleDb
spring.datasource.username=root
spring.datasource.password=root
plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}
group = 'com.sujan'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}
repositories {
    mavenCentral()
}
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    annotationProcessor 'org.projectlombok:lombok'
    compileOnly 'org.projectlombok:lombok'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}
test {
    useJUnitPlatform()
}
curl --location --request GET 'http://localhost:8080/'
Hibernate: select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ order by apple0_.apple_name asc, apple0_.taste asc
Apple(appleId=7, appleName=Fuji, taste=juicy)
Apple(appleId=6, appleName=Fuji, taste=sweet)
Apple(appleId=5, appleName=Fuji, taste=tangy)
Apple(appleId=8, appleName=Fuji, taste=tart)
Apple(appleId=11, appleName=Gala, taste=juicy)
Apple(appleId=10, appleName=Gala, taste=sweet)
Apple(appleId=9, appleName=Gala, taste=tangy)
Apple(appleId=12, appleName=Gala, taste=tart)
Apple(appleId=15, appleName=Jonagold, taste=juicy)
Apple(appleId=14, appleName=Jonagold, taste=sweet)
Apple(appleId=13, appleName=Jonagold, taste=tangy)
Apple(appleId=16, appleName=Jonagold, taste=tart)
Apple(appleId=3, appleName=Macintosh, taste=juicy)
Apple(appleId=2, appleName=Macintosh, taste=sweet)
Apple(appleId=1, appleName=Macintosh, taste=tangy)
Apple(appleId=4, appleName=Macintosh, taste=tart)

The findAll(Sort.by(String... properties)) method takes a list of the parameter as input, so if required we can sort the retrieved results on multiple properties, priority is given to the first properties and if the result is the same then will be sorted on next properties and so on.

drop database if exists AppleDb;
create database AppleDb; 
use AppleDb;
create table apple ( apple_id bigint not null, apple_name varchar(255) default null, taste varchar(255) default null, primary key (apple_id)) engine=innodb;
create table hibernate_sequence (next_val bigint) engine=innodb;
insert into hibernate_sequence values ( 1 );
insert into apple (apple_id, apple_name, taste) values (1, "Macintosh", "tangy");
insert into apple (apple_id, apple_name, taste) values (2, "Macintosh", "sweet");
insert into apple (apple_id, apple_name, taste) values (3, "Macintosh", "juicy");
insert into apple (apple_id, apple_name, taste) values (4, "Macintosh", "tart");
insert into apple (apple_id, apple_name, taste) values (5, "Fuji", "tangy");
insert into apple (apple_id, apple_name, taste) values (6, "Fuji", "sweet");
insert into apple (apple_id, apple_name, taste) values (7, "Fuji", "juicy");
insert into apple (apple_id, apple_name, taste) values (8, "Fuji", "tart");
insert into apple (apple_id, apple_name, taste) values (9, "Gala", "tangy");
insert into apple (apple_id, apple_name, taste) values (10, "Gala", "sweet");
insert into apple (apple_id, apple_name, taste) values (11, "Gala", "juicy");
insert into apple (apple_id, apple_name, taste) values (12, "Gala", "tart");
insert into apple (apple_id, apple_name, taste) values (13, "Jonagold", "tangy");
insert into apple (apple_id, apple_name, taste) values (14, "Jonagold", "sweet");
insert into apple (apple_id, apple_name, taste) values (15, "Jonagold", "juicy");
insert into apple (apple_id, apple_name, taste) values (16, "Jonagold", "tart");
package com.sujan.example.jpa.controller;

import com.sujan.example.jpa.entity.Apple;
import com.sujan.example.jpa.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class AppleController {
    @Autowired
    private AppleRepository appleRepository;

    @GetMapping
    void findAllSortBy() {
        List<Apple> all = appleRepository.findAll(Sort.by("appleName", "taste"));
        all.forEach(System.out::println);
    }
}
package com.sujan.example.jpa.entity;

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

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

@Data
@Entity
@NoArgsConstructor
public class Apple {
   @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long appleId;
    private String appleName;
    private String taste;
}
package com.sujan.example.jpa.repository;

import com.sujan.example.jpa.entity.Apple;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
}
package com.sujan.example.jpa;

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

@SpringBootApplication
public class JpaApplication {
    public static void main(String[] args) {
        SpringApplication.run(JpaApplication.class, args);
    }
}
spring.datasource.url=jdbc:mysql://localhost:3306/AppleDb
spring.datasource.username=root
spring.datasource.password=root
plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}
group = 'com.sujan'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}
repositories {
    mavenCentral()
}
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    annotationProcessor 'org.projectlombok:lombok'
    compileOnly 'org.projectlombok:lombok'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}
test {
    useJUnitPlatform()
}
curl --location --request GET 'http://localhost:8080/'
Hibernate: select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ order by apple0_.apple_name asc, apple0_.taste asc
Apple(appleId=7, appleName=Fuji, taste=juicy)
Apple(appleId=6, appleName=Fuji, taste=sweet)
Apple(appleId=5, appleName=Fuji, taste=tangy)
Apple(appleId=8, appleName=Fuji, taste=tart)
Apple(appleId=11, appleName=Gala, taste=juicy)
Apple(appleId=10, appleName=Gala, taste=sweet)
Apple(appleId=9, appleName=Gala, taste=tangy)
Apple(appleId=12, appleName=Gala, taste=tart)
Apple(appleId=15, appleName=Jonagold, taste=juicy)
Apple(appleId=14, appleName=Jonagold, taste=sweet)
Apple(appleId=13, appleName=Jonagold, taste=tangy)
Apple(appleId=16, appleName=Jonagold, taste=tart)
Apple(appleId=3, appleName=Macintosh, taste=juicy)
Apple(appleId=2, appleName=Macintosh, taste=sweet)
Apple(appleId=1, appleName=Macintosh, taste=tangy)
Apple(appleId=4, appleName=Macintosh, taste=tart)

 

properties passed in The findAll(Sort.by(String... properties)) method must not be null, else method will throw java.lang.IllegalArgumentException

package com.sujan.example.jpa.controller;

import com.sujan.example.jpa.entity.Apple;
import com.sujan.example.jpa.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class AppleController {
    @Autowired
    private AppleRepository appleRepository;

    @GetMapping
    void findAllSortBy() {
        List<Apple> all = appleRepository.findAll(Sort.by("appleName", null));
        all.forEach(System.out::println);
    }
}
package com.sujan.example.jpa.entity;

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

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

@Data
@Entity
@NoArgsConstructor
public class Apple {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long appleId;
    private String appleName;
    private String taste;
}
package com.sujan.example.jpa.repository;

import com.sujan.example.jpa.entity.Apple;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
}
package com.sujan.example.jpa;

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

@SpringBootApplication
public class JpaApplication {
    public static void main(String[] args) {
        SpringApplication.run(JpaApplication.class, args);
    }
}
spring.datasource.url=jdbc:mysql://localhost:3306/AppleDb
spring.datasource.username=root
spring.datasource.password=root
plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}
group = 'com.sujan'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}
repositories {
    mavenCentral()
}
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    annotationProcessor 'org.projectlombok:lombok'
    compileOnly 'org.projectlombok:lombok'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}
test {
    useJUnitPlatform()
}
curl --location --request GET 'http://localhost:8080/'
java.lang.IllegalArgumentException: Property must not null or empty!

 

properties passed in The findAll(Sort.by(String... properties)) method must be valid and exist in entity class else method will throw org.springframework.data.mapping.PropertyReferenceException

drop database if exists AppleDb;
create database AppleDb; 
use AppleDb;
create table apple ( apple_id bigint not null, apple_name varchar(255) default null, taste varchar(255) default null, primary key (apple_id)) engine=innodb;
create table hibernate_sequence (next_val bigint) engine=innodb;
insert into hibernate_sequence values ( 1 );
insert into apple (apple_id, apple_name, taste) values (1, "Macintosh", "tangy");
insert into apple (apple_id, apple_name, taste) values (2, "Macintosh", "sweet");
insert into apple (apple_id, apple_name, taste) values (3, "Macintosh", "juicy");
insert into apple (apple_id, apple_name, taste) values (4, "Macintosh", "tart");
insert into apple (apple_id, apple_name, taste) values (5, "Fuji", "tangy");
insert into apple (apple_id, apple_name, taste) values (6, "Fuji", "sweet");
insert into apple (apple_id, apple_name, taste) values (7, "Fuji", "juicy");
insert into apple (apple_id, apple_name, taste) values (8, "Fuji", "tart");
insert into apple (apple_id, apple_name, taste) values (9, "Gala", "tangy");
insert into apple (apple_id, apple_name, taste) values (10, "Gala", "sweet");
insert into apple (apple_id, apple_name, taste) values (11, "Gala", "juicy");
insert into apple (apple_id, apple_name, taste) values (12, "Gala", "tart");
insert into apple (apple_id, apple_name, taste) values (13, "Jonagold", "tangy");
insert into apple (apple_id, apple_name, taste) values (14, "Jonagold", "sweet");
insert into apple (apple_id, apple_name, taste) values (15, "Jonagold", "juicy");
insert into apple (apple_id, apple_name, taste) values (16, "Jonagold", "tart");
package com.sujan.example.jpa.controller;

import com.sujan.example.jpa.entity.Apple;
import com.sujan.example.jpa.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class AppleController {
    @Autowired
    private AppleRepository appleRepository;

    @GetMapping
    void findAllSortBy() {
        List<Apple> all = appleRepository.findAll(Sort.by("appleName").and(Sort.by("taste1")));
        all.forEach(System.out::println);
    }
}
package com.sujan.example.jpa.entity;

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

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

@Data
@Entity
@NoArgsConstructor
public class Apple {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long appleId;
    private String appleName;
    private String taste;
}
package com.sujan.example.jpa.repository;

import com.sujan.example.jpa.entity.Apple;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
}
package com.sujan.example.jpa;

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

@SpringBootApplication
public class JpaApplication {
    public static void main(String[] args) {
        SpringApplication.run(JpaApplication.class, args);
    }
}
spring.datasource.url=jdbc:mysql://localhost:3306/AppleDb
spring.datasource.username=root
spring.datasource.password=root
plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}
group = 'com.sujan'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}
repositories {
    mavenCentral()
}
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    annotationProcessor 'org.projectlombok:lombok'
    compileOnly 'org.projectlombok:lombok'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}
test {
    useJUnitPlatform()
}
curl --location --request GET 'http://localhost:8080/'
org.springframework.data.mapping.PropertyReferenceException: No property taste1 found for type Apple! Did you mean 'taste'?

 

follow us on