[JAVA] Sort by Spring Data JPA (with compound key sort)

It is a memo of what I stumbled upon and investigated while using spring-boot.

environment

Premise

Consider with the following Entity

@Entity
public class Entity {
  @Id
  private int Id;

  private String name;

  private int position;

  // Getter,Setter omitted
}

List<T> findAll(Sort sort) List <T> findAll (Sort sort) is defined when the interface that inherits JpaRepository is created, and it can be sorted as follows.

@Service
public class EntityService {

  @Autowired
  EntityRepoistory entityRepository;

  public List<Entity> xxxMethod() {
    return entityRepoistory.findAll(new Sort(ASC, "position"));
  }

}

Composite key

Specify by combining Sort

//Sort by descending position and ascending id
Sort sort = new Sort(Direction.DESC, "position").and(new Sort(Direction.ASC, "id"));

Query Method definition

Sorting can be achieved simply by defining a method on the interface that inherits JpaRepository.

@Repository
public interface EntityRepository implements JpaRepository<Entity, Integer> {

  // select * from entity where name = 'xxx'Equivalent to order by position asc
  List<Entity> findByNameOrderByPosition(String name);

  // select *Equivalent to from entity order by position
  //findAllOrderByPosition is no good
  List<Entity> findAllByOrderByPosition();
}

If the return value of findBy is List, it can be obtained as multiple results even with this naming. A trap that findAllOrderByPosition doesn't work.

Composite key

// select * from entity order by position desc,Equivalent to id asc
List<Entity> findAllByOrderByPositionDescIdAsc();

Definition by annotation

Realized by writing in JPQL format in @Query

@Repository
public interface EntityRepository implements JpaRepository<Entity, Integer> {
  
  @Query(value = "select e from Entity e order by position desc, id asc")
  List<Sample> queryAll();

  //Or

  @Query(value = "select e from Entity")
  List<Sample> queryAll(Sort sort);
}

About sorting related entities

Use @ Orderby

@Entity
public class Entity {

  @OrderBy(value = "position desc, id asc")
  @OneToMany()
  @JoinColumn(name = "entity_id")
  private List<EntityItem> entityItems;
}

Reference URL

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/

Recommended Posts

Sort by Spring Data JPA (with compound key sort)
OR search with Spring Data Jpa Specification
Creating a common repository with Spring Data JPA
A memorandum when trying Spring Data JPA with STS
I tried to get started with Spring Data JPA
[spring] Let's use Spring Data JPA
Spring Boot Introductory Guide I tried [Accessing Data with JPA]
[How to install Spring Data Jpa]
Spring Data JPA SQL log output
Dynamically generate queries with Spring Data JPA (multi-word search) (paging support)
Creating REST APIs with Spring JPA Data with REST and Lombok incredibly easy.
Spring with Kotorin --2 RestController and Data Class
Exists using Specification in Spring Data JPA
Implementation method for multi-data source with Spring boot (Mybatis and Spring Data JPA)
How to delete data with foreign key
Jackson is unable to JSON serialize hibernateLazyInitializer in Spring Data JPA with error
Sample code for DB control by declarative transaction in Spring Boot + Spring Data JPA
Sample code for search using QBE (Query by Example) of Spring Data JPA
Until data acquisition with Spring Boot + MyBatis + PostgreSQL
Spring Data JPA save select-insert is only insert
Spring Data JPA Entity cross-reference and its notes
Build Spring Boot project by environment with Gradle
Get data with api created by curl command
Spring Boot + Spring Data JPA About multiple table joins
Compatibility of Spring JDBC and MyBatis with Spring Data JDBC (provisional)
MySQL JSON type Value search with SpringBoot + Spring JPA
Until the use of Spring Data and JPA Part 2
Until the use of Spring Data and JPA Part 1
Make the where clause variable in Spring Data JPA
How to boot by environment with Spring Boot of Maven