jpa order records descending and select first record

Question

I have a spring boot app connected to oracle DB. I am trying to order a list of records and select the top most record.

I wrote a JPA query as below but it fails.

@Query("SELECT id FROM UploadedFile uploadedFile "
    + "WHERE uploadedFile.p_file_type = 'branch' "
    + "and p_file_status='Processed' "
    + "and p_is_file_process_with_error = 0 "
    + "order by c_created_date desc "
    + "FETCH FIRST 1 rows only ")
public String findLatestBranchCodeFile();

The error received was

creating bean with name 'uploadedFileRepo': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.lang.String com.rhb.pintas.repo.UploadedFileRepo.findLatestBranchCodeFile()! org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: FETCH near line 1, column 204 [SELECT id FROM com.rhb.pintas.entities.UploadedFile uploadedFile WHERE uploadedFile.p_file_type = 'branch' and p_file_status='Processed' and p_is_file_process_with_error = 0 order by c_created_date desc FETCH FIRST 1 rows only ] -> [Help 1]

The issue seems to be with fetch,not sure whats wrong.

Did you try using `LIMIT`? – Arun Sudhakaran Jul 06 '22 at 05:02 — Arun Sudhakaran, Jul 06 '22 at 05:02

Andrey B. Panfilov · Answer 1 · 2022-07-06T05:09:56.303

1

You are trying to execute SQL query, in this case you need to add nativeQuery=true attribute to @Query annotation

UPD.

got confused because FETCH FIRST - is a SQL syntax, for JPA way please check another solution - it is possible to return list with at most one element.

edited Jul 06 '22 at 05:09

answered Jul 06 '22 at 04:59

Andrey B. Panfilov

4,324
2
12
18

thanks.......if i was to do it 100% jpa query way, wat will my query be? – Rahul SK Jul 06 '22 at 05:01
That is a JPA query. – Arun Sudhakaran Jul 06 '22 at 05:02

Beri · Accepted Answer · 2022-07-06T05:10:54.077

It seems you are mixing HQL and native query dialects:

If this will be a naviveQuery (like most of the columns would mention), then replace the entity name with table name and add nativeQuery option. And because You are using only a single table, You can skip the alias name:

 @Query("SELECT id FROM uploaded_file "
        + "WHERE p_file_type = 'branch' and p_file_status='Processed' and "
        + "p_is_file_process_with_error = 0 "
        + "order by c_created_date desc "
        + "FETCH FIRST 1 rows only ", nativeQuery = true)
    public String findLatestBranchCodeFile();

If You want to keep it as a HQL, then replace all column names with entity property names, like p_file_type > fileType (I guess column names). Secondly You will need to pass a Pageable parameter, to replace Your 'Fetch first' statement.

You can find more materials here:

score 0 · Answer 3 · answered Jul 06 '22 at 05:11

0

I guess, you can try passing pagable to limit result set size and unlimit your query:

public String findLatestBranchCodeFile(Pageable pageable); // pass new PageRequest(0,1)

answered Jul 06 '22 at 05:11

Roman

181
7

jpa order records descending and select first record

3 Answers3