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

• java: 8
• spring-boot: 2.0.1
• spring-data-jpa: 2.0.7

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/