How to use OrderBy with findAll in Spring Data

Question

I am using spring data and my DAO looks like

public interface StudentDAO extends JpaRepository<StudentEntity, Integer> {
    public findAllOrderByIdAsc();   // I want to use some thing like this
}

In above code, commented line shows my intent. Can spring Data provides inbuilt functionality to use such a method to find all records order by some column with ASC/DESC?

Answer 1

public interface StudentDAO extends JpaRepository<StudentEntity, Integer> {
    public List<StudentEntity> findAllByOrderByIdAsc();
}

The code above should work. I'm using something similar:

public List<Pilot> findTop10ByOrderByLevelDesc();

It returns 10 rows with the highest level.

IMPORTANT: Since I've been told that it's easy to miss the key point of this answer, here's a little clarification:

findAllByOrderByIdAsc(); // don't miss "by"
       ^

Answer 2

AFAIK, I don't think this is possible with a direct method naming query. You can however use the built in sorting mechanism, using the Sort class. The repository has a findAll(Sort) method that you can pass an instance of Sort to. For example:

import org.springframework.data.domain.Sort;

@Repository
public class StudentServiceImpl implements StudentService {
    @Autowired
    private StudentDAO studentDao;

    @Override
    public List<Student> findAll() {
        return studentDao.findAll(sortByIdAsc());
    }

    private Sort sortByIdAsc() {
        return new Sort(Sort.Direction.ASC, "id");
    }
} 

Answer 3

Simple way:

repository.findAll(Sort.by(Sort.Direction.DESC, "colName"));

Source: https://www.baeldung.com/spring-data-sorting

Answer 4

Please have a look at the Spring Data JPA - Reference Documentation, section 5.3. Query Methods, especially at section 5.3.2. Query Creation, in "Table 3. Supported keywords inside method names" (links as of 2019-05-03).

I think it has exactly what you need and same query as you stated should work...

Answer 5

Yes you can sort using query method in Spring Data.

Ex:ascending order or descending order by using the value of the id field.

Code:

  public interface StudentDAO extends JpaRepository<StudentEntity, Integer> {
    public findAllByOrderByIdAsc();   
}

alternative solution:

    @Repository
public class StudentServiceImpl implements StudentService {
    @Autowired
    private StudentDAO studentDao;

    @Override
    public List<Student> findAll() {
        return studentDao.findAll(orderByIdAsc());
    }
private Sort orderByIdAsc() {
    return new Sort(Sort.Direction.ASC, "id")
                .and(new Sort(Sort.Direction.ASC, "name"));
}
}

Spring Data Sorting: Sorting

Answer 6

I try in this example to show you a complete example to personalize your OrderBy sorts

 import java.util.List;
 import org.springframework.data.domain.Page;

 import org.springframework.stereotype.Repository;
 import org.springframework.data.domain.Sort;
 /**
 * Spring Data  repository for the User entity.
 */
 @SuppressWarnings("unused")
 @Repository
 public interface UserRepository extends JpaRepository<User, Long> {
 List <User> findAllWithCustomOrderBy(Sort sort);
 }

you will use this example : A method for build dynamically a object that instance of Sort :

import org.springframework.data.domain.Sort;
public class SampleOrderBySpring{
 Sort dynamicOrderBySort = createSort();
     public static void main( String[] args )
     {
       System.out.println("default sort \"firstName\",\"name\",\"age\",\"size\" ");
       Sort defaultSort = createStaticSort();
       System.out.println(userRepository.findAllWithCustomOrderBy(defaultSort ));
        

       String[] orderBySortedArray = {"name", "firstName"};
       System.out.println("default sort ,\"name\",\"firstName\" ");
       Sort dynamicSort = createDynamicSort(orderBySortedArray );
       System.out.println(userRepository.findAllWithCustomOrderBy(dynamicSort ));
      }
      public Sort createDynamicSort(String[] arrayOrdre) {
        return  Sort.by(arrayOrdre);
        }

   public Sort createStaticSort() {
        String[] arrayOrdre  ={"firstName","name","age","size");
        return  Sort.by(arrayOrdre);
        }
}

Answer 7

Combining all answers above, you can write reusable code with BaseEntity:

@Data
@NoArgsConstructor
@MappedSuperclass
public abstract class BaseEntity {

  @Transient
  public static final Sort SORT_BY_CREATED_AT_DESC = 
                        Sort.by(Sort.Direction.DESC, "createdAt");

  @Id
  private Long id;
  private LocalDateTime createdAt;
  private LocalDateTime updatedAt;

  @PrePersist
  void prePersist() {
    this.createdAt = LocalDateTime.now();
  }

  @PreUpdate
  void preUpdate() {
    this.updatedAt = LocalDateTime.now();
  }
}

DAO object overloads findAll method - basically, still uses findAll()

public interface StudentDAO extends CrudRepository<StudentEntity, Long> {

  Iterable<StudentEntity> findAll(Sort sort);

}

StudentEntity extends BaseEntity which contains repeatable fields (maybe you want to sort by ID, as well)

@Getter
@Setter
@FieldDefaults(level = AccessLevel.PRIVATE)
@Entity
class StudentEntity extends BaseEntity {

  String firstName;
  String surname;

}

Finally, the service and usage of SORT_BY_CREATED_AT_DESC which probably will be used not only in the StudentService.

@Service
class StudentService {

  @Autowired
  StudentDAO studentDao;

  Iterable<StudentEntity> findStudents() {
    return this.studentDao.findAll(SORT_BY_CREATED_AT_DESC);
  }
}

Answer 8

for spring boot 3, your Repository should implement JpaRepository and define the sort as follow :

Sort sortBy = Sort.by(Sort.Direction.DESC, "id");

then use it :

repository.findAll(sortBy);

Answer 9

Sort sortBy = Sort.by(new Sort.Order(Sort.Direction.ASC, "name").ignoreCase());

repository.findAll(sortBy);