How can I obtain the first row from a SELECT operation using JPQL?

Question

I have a database with data about some tests, I want to order them decently using their attribute DATE and take only the first one of all of them. I mean, the equivalent TOP 1 of SQL in JPQL.

Thanks for your help!

Solution 1

In spring jpa you can do something like this

Foo findTopByOrderByDateDesc(); //This will return 1st item

List<Foo> findTop10ByOrderByDateDesc(); //This will return top 10 item

For reference Spring Jpa Doc

Solution 2

You normally set that on the Query object before triggering the fetch:

entityManager.createQuery("...")
       .setMaxResults(1)
       .getResultList();

With the use of Spring Data Jpa syntax you would use something like:

Optional<Test> findFirstByOrderByDateDesc();

Or using Pageable:

Page<Test> test = repository.findAll(
    PageRequest.of(0, 1, Sort.by(Sort.Direction.DESC, "date")));

Solution 3

Mostly common

Foo findFirstByOrderByDateDESC();

Using @Query with nativeQuery = true

@Query(value="SELECT 1 * FROM "TableName" ORDER BY "DATE in db" DESC LIMIT 1", nativeQuery = true)
Foo findFirstByOrderByDateDESC(Long id); // name can be random

Solution 4

In Spring Data JPA, use the keywords first or top, both are semantically the same, i prefer top, because there are 2 less letters to type ;-).

Foo findTopByOrderByDateDesc(); //Top 1 assumed
Foo findFirstByOrderByDateDesc(); //First 1 assumed

If you want to find the first X rows, where X is a number, use:

List<Foo> findTopXByOrderByDateDesc();
List<Foo> findFirstXByOrderByDateDesc();

If you want to remember to deal with null returns, wrap the result in an Optional:

Optional<Foo> findTopByOrderByDateDesc();
Optional<Foo> findFirstByOrderByDateDesc();

View more solutions

Comments

[Iván Sánchez Castellanos]

I am using the @Query annotation in the method defined in my repository as I am not too much familiarized with the JPA syntax and Pageable. So, I have something like "SELECT * FROM TEST t ORDER BY t.date DESC", and it returns all tests, but I want to add to the query like the TOP 1 of SQL, is there any option to take the first one in my way?

[Maciej Kowalski]

It seems you need option 2 here

[Vladislav Ashikhin]

Doesn't work for me