What is the LIMIT alternative in JPQL?

Question

I am working with PostgreSQL query execution in JPQL.

This is an example of a native psql request that works fine,

SELECT * FROM students ORDER BY id DESC LIMIT 1;

The same query in JPQL does not work,

@Query("SELECT s FROM Students s ORDER BY s.id DESC LIMIT 1")

Students getLastStudentDetails();

It looks like the LIMIT clause does not work in JPQL.

According to the JPA documentation, we can use setMaxResults/setFirstResult. Can someone tell me how can I use this in my previous request?

Answer 1

You are using JPQL, which does not support restricting results like this. When using native JPQL you should use setMaxResultsto limit the results.

Spring Data JPA, . , . find , .

findFirstByOrderById();

Pageable LIMIT.

@Query("SELECT s FROM Students s ORDER BY s.id DESC")
List<Students> getLastStudentDetails(Pageable pageable);

- ( ).

getLastStudentDetails(new PageRequest(0,1));

, SQL.

Answer 2

, JPQL LIMIT.

setMaxResults, , getSingleResult - , .

, :

TypedQuery<Student> query = entityManager.createQuery("SELECT s FROM Students s ORDER BY s.id DESC", Student.class);    
query.setMaxResults(1);

, query.setFirstResult(initPosition);

Answer 3

(new PageRequest(0, 1)) .

    @QueryHints({ @QueryHint(name = "org.hibernate.cacheable", value = "true") })
    @Query("select * from a_table order by a_table_column desc")
    List<String> getStringValue(Pageable pageable);

new PageRequest(0, 1) .

Answer 4

- :

 @Repository
 public interface ICustomerMasterRepository extends CrudRepository<CustomerMaster, String> 
 {
    @Query(value = "SELECT max(c.customer_id) FROM CustomerMaster c ")
    public String getMaxId();
 }

Answer 5

, LIMIT jpql, jpql, .

( - nativeQuery = true)

@Query("SELECT s FROM Students s ORDER BY s.id DESC LIMIT 1", nativeQuery=true)
Students getLastStudentDetails();