[SOLVED] SQL query for selecting a single role in PostgeSQL query with Pageable object

SQL query for selecting a single role in PostgeSQL query with Pageable object

I have this Oracle query implemented as HQL and Spring Boot query which I want to migrate to PostgreSQL

  @Query("select profileId from HrDepartment where profileId = :id and profileId is not null and rownum = 1)")
  String getProfileId(@Param("id") String id);

The solution so far that I found is

@Query("select profileId from HrDepartment where profileId = :id and profileId is not null)")
String getProfileId(@Param("id") String id, Pageable pageable);

repository.getProfileId("somId), PageRequest.of(0, 1));

I'm not clear is this going to select all rows from table HrDepartment and then into Java code get only the first row. Or it's going to generate SQL query and add SQL clause LIMIT 1 and select only one row into database?

Solution

Spring Data will use JPA API to limit the result to just one element. By all JPA implementations I know this will be translated into a limit clause added to the SQL statement.