CODEWITHSUNDEEP
javamysqljpaspring-data-jpajpql
Madhu
Madhu

Reputation: 2823

What is the LIMIT clause alternative in JPQL?

I'm working with PostgreSQL query implementing in JPQL.

This is a sample native psql query which works fine,

SELECT * FROM students ORDER BY id DESC LIMIT 1;

The same query in JPQL doesnt work,

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

Students getLastStudentDetails();

seems like LIMIT clause doesn't work in JPQL.

According to JPA documentation we can use setMaxResults/setFirstResult, Can anyone tell me how can I use that in my above query?

Upvotes: 136

Views: 224517

Answers (10)

M. Deinum
M. Deinum

Reputation: 125212

You are using JPQL which doesn't support limiting results like this. When using native JPQL you should use setMaxResults to limit the results.

However, you are using Spring Data JPA which makes it pretty easy. See here in the reference guide on how to limit results based on a query. In your case, the following find method would do exactly what you want.

findFirstByOrderById();

You could also use a Pageable argument with your query instead of a LIMIT clause.

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

Then in your calling code do something like this (as explained here in the reference guide).

getLastStudentDetails(PageRequest.of(0,1));

Both should yield the same result, without resorting to plain SQL.

Upvotes: 156

р&#252;ффп
р&#252;ффп

Reputation: 5448

As your query is simple, you can use the solution of the accepted answer, naming your query findFirstByOrderById();

But if your query is more complicated, I also found this way without need to use a native query:

@Query("SELECT MAX(s) FROM Students s ORDER BY s.id DESC")
Students getLastStudentDetails();

Here a practical example where the named query method cannot be used.

Upvotes: -1

NRJ
NRJ

Reputation: 1204

The correct way is to write your JPA interface method like this

public interface MyRepository extends PagingAndSortingRepository<EntityClass, KeyClass> {

List<EntityClass> findTop100ByOrderByLastModifiedDesc();
}

In the method name, "100" denotes how many rows you want which you would have otherwise put in the limit clause. also "LastModified" is the column which you want to sort by.

PagingAndSortingRepository or CrudRepository, both will work for this.

For the sake of completeness, OP's interface method would be

List<Students> findTop1ByIdDesc();

Upvotes: 10

Shoniisra
Shoniisra

Reputation: 781

Here a Top Ten Service (it's a useful example)

REPOSITORY
(In the Query, I parse the score entity to ScoreTo ( DTO class) by a constructor)

@Repository
public interface ScoreRepository extends JpaRepository<Scores, UUID> {     
  @Query("SELECT new com.example.parameters.model.to.ScoreTo(u.scoreId , u.level, u.userEmail, u.scoreLearningPoints, u.scoreExperiencePoints, u.scoreCommunityPoints, u.scoreTeamworkPoints, u.scoreCommunicationPoints, u.scoreTotalPoints) FROM Scores u "+
            "order by u.scoreTotalPoints desc")
    List<ScoreTo> findTopScore(Pageable pageable);
}

SERVICE

@Service
public class ScoreService {
    @Autowired
    private ScoreRepository scoreRepository;    
  
    public List<ScoreTo> getTopScores(){
        return scoreRepository.findTopScore(PageRequest.of(0,10));
    }
}

Upvotes: 2

David Jesus
David Jesus

Reputation: 2121

You can not use Limit in HQL because Limit is database vendor dependent so Hibernate doesn't allow it through HQL query.

A way you can implement is using a subquery:

@Query("FROM Students st WHERE st.id = (SELECT max(s.id) FROM Students s)")
Students getLastStudentDetails();

Upvotes: 9

tk_
tk_

Reputation: 17418

Hardcode the pagination(new PageRequest(0, 1)) to achieve fetch only one record.

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

you have to pass new PageRequest(0, 1)to fetch records and from the list fetch the first record.

Upvotes: 3

greenhorn
greenhorn

Reputation: 654

JPQL does not allow to add the limit keyword to the query generated by the HQL. You would get the following exception.

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: LIMIT near line 1

But don't worry there is an alternative to use the limit keyword in the query generated by the HQL by using the following steps.

Sort.by(sortBy).descending() // fetch the records in descending order

pageSize = 1 // fetch the first record from the descending order result set.

Refer the following service class

Service:

@Autowired
StudentRepository repository; 

public List<Student> getLastStudentDetails(Integer pageNo, Integer pageSize, String sortBy)
{
    Integer pageNo = 0;
    Integer pageSize = 1;
    String sortBy = "id";
    Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by(sortBy).descending());

    Slice<Student> pagedResult = repository.findLastStudent(paging);

    return pagedResult.getContent();
}

Your repository interface should implement the PagingAndSortingRepository

Repository:

public interface StudentRepository extends JpaRepository<Student,Long>, PagingAndSortingRepository<Student,Long>{

    @Query("select student from Student student")
    Slice<Student> findLastStudent(Pageable paging);
}

This will add the limit keyword to you query which you can see in the console. Hope this helps.

Upvotes: 4

Manish Joshi
Manish Joshi

Reputation: 183

Hello for fetching single row and using LIMIT in jpql we can tell the jpql if it's a native query.

( using - nativeQuery=true )

Below is the use

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

Upvotes: 14

Rajib Das Gupta
Rajib Das Gupta

Reputation: 19

You can use something like this:

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

Upvotes: 1

dazito
dazito

Reputation: 7980

As stated in the comments, JPQL does not support the LIMIT keyword.

You can achieve that using the setMaxResults but if what you want is just a single item, then use the getSingleResult - it throws an exception if no item is found.

So, your query would be something like:

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

If you want to set a specific start offset, use query.setFirstResult(initPosition); too

Upvotes: 29

Related Questions