CODEWITHSUNDEEP
javaspringspring-bootjpajpql
Iván Sánchez Castellanos
Iván Sánchez Castellanos

Reputation: 381

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

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!

Upvotes: 17

Views: 59995

Answers (5)

Steve McCollom
Steve McCollom

Reputation: 171

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.

Upvotes: 7

jumping_monkey
jumping_monkey

Reputation: 7799

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

Upvotes: 4

yyiu1933
yyiu1933

Reputation: 61

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

Upvotes: 6

Avi
Avi

Reputation: 1528

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

Upvotes: 12

Maciej Kowalski
Maciej Kowalski

Reputation: 26502

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

Upvotes: 4

Related Questions