Spring Data Jpa Query Using Sort

Spring Data Jpa Provides @Query to provide vendor specific native query to the repository methods. But in real world application Sorting is also requrired based on domin object variable.

Sorting in Spring Data Jpa can be done by 2 ways, by provide a PageRequest or by using Sort. The thing to take care here is to make sure that the property used with Sort must match with the property or alies in domain model.

Properties or alies are checked and matched to domain object at compile time and using a non existing property or alias will throw an Exception. But @Query with Sort lets you use unchecked containing functions within the ORDER BY clause.
It will result in Exception, so in order to avoid this situation By default, Spring Data JPA rejects any Order instance containing function calls, but if required, Spring Data Jpa provides JpaSort.unsafe to use potentially unsafe ordering.

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_name, apple_id, taste) values ("Macintosh", 1, "tangy");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 2, "sweet");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 3, "juicy");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 4, "firm sweet");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 5, "sharp");

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.data.jpa.domain.JpaSort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;
import java.util.List;

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

    @GetMapping
    void Query() {
        List<Apple> findByAppleNameAndSortByTaste = appleRepository.findByAppleNameAndSortByTaste("Macintosh", Sort.by(new String[]{"taste"}));
        System.out.println("findByAppleNameAndSortByTaste = " + findByAppleNameAndSortByTaste);
        findByAppleNameAndSortByTaste = appleRepository.findByAppleNameAndSortByTaste("Macintosh", JpaSort.unsafe("LENGTH(taste)"));
        System.out.println("findByAppleNameAndSortByTaste = " + findByAppleNameAndSortByTaste);
        List<Object[]> findByAppleNameAsArrayAndSortByTaste = appleRepository.findByAppleNameAsArrayAndSortByTaste("Macintosh", Sort.by(new String[]{"fn_len"}));
        findByAppleNameAsArrayAndSortByTaste.stream().flatMap(Arrays::stream).map(app -> "app = " + app).forEach(System.out::println);
    }
}

package com.sujan.example.jpa.entity;

import lombok.AllArgsConstructor;
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
@ToString
@NoArgsConstructor
@AllArgsConstructor()
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.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
    @Query("select a from Apple a where a.appleName = ?1")
    List<Apple> findByAppleNameAndSortByTaste(String appleName, Sort sort);

    @Query("select a.appleId, a.appleName, a.taste, LENGTH(a.taste) as fn_len from Apple a where a.appleName = ?1")
    List<Object[]> findByAppleNameAsArrayAndSortByTaste(String taste, Sort sort);
}

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

select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.apple_name=? order by apple0_.taste asc
findByAppleNameAndSortByTaste = [Apple(appleId=4, appleName=Macintosh, taste=firm sweet), Apple(appleId=3, appleName=Macintosh, taste=juicy), Apple(appleId=5, appleName=Macintosh, taste=sharp), Apple(appleId=2, appleName=Macintosh, taste=sweet), Apple(appleId=1, appleName=Macintosh, taste=tangy)]
select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.apple_name=? order by length(apple0_.taste) asc
findByAppleNameAndSortByTaste = [Apple(appleId=1, appleName=Macintosh, taste=tangy), Apple(appleId=2, appleName=Macintosh, taste=sweet), Apple(appleId=3, appleName=Macintosh, taste=juicy), Apple(appleId=5, appleName=Macintosh, taste=sharp), Apple(appleId=4, appleName=Macintosh, taste=firm sweet)]
select apple0_.apple_id as col_0_0_, apple0_.apple_name as col_1_0_, apple0_.taste as col_2_0_, length(apple0_.taste) as col_3_0_ from apple apple0_ where apple0_.apple_name=? order by col_3_0_ asc
app = 1
app = Macintosh
app = tangy
app = 5
app = 2
app = Macintosh
app = sweet
app = 5
app = 3
app = Macintosh
app = juicy
app = 5
app = 5
app = Macintosh
app = sharp
app = 5
app = 4
app = Macintosh
app = firm sweet
app = 10

Spring Data Jpa Query Using Sort

follow us on