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!

Answer 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

Answer 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")));

Answer 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

Answer 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();

Answer 5

The question specifically asks how to do it in JPQL. The answer is that you can't. You either need to use a JPA query derived from a method name (as in jumping_monkey's answer) or use a native query.