jpa order records descending and select first record-CodePudding

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.

CodePudding user response：

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:

CodePudding user response：

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.

CodePudding user response：

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)