CODEWITHSUNDEEP
javamysqlhibernatejdbcspring-data-jpa
Sardor I.
Sardor I.

Reputation: 426

How to add limit to Hibernate query annotation?

I have this in Java Hibernate

@Query("SELECT dirPar FROM DirectiveParagraph dirPar, Directive dir  "
        + "WHERE dirPar.directive = dir "
        + "AND dir.txtDirCode = :txtDirCode ");

List<DirectiveParagraph> getByName(@Param("txtDirCode") String name, @Param("page") int page ,@Param("size") int size);

I want to retrieve with limit and size, same way like this 

SELECT * FROM tblDirectiveParagraph where intDirectiveID = 1 limit 10,10;

How do I add limit to above @Query annotation

Upvotes: 4

Views: 6052

Answers (2)

Adrian Viesca
Adrian Viesca

Reputation: 181

You can try adding a Pageable parameter to your getByName method

    @Query("SELECT dirPar FROM DirectiveParagraph dirPar, Directive dir  "
    + "WHERE dirPar.directive = dir "
    + "AND dir.txtDirCode = :txtDirCode ");
    Page<DirectiveParagraph> getByName(@Param("txtDirCode") String name, Pageable page);

And here's a sample method call:

    public void someMethod(){
        PageRequest pageR = new PageRequest(10,10);
        Page<DirectiveParagraph> result = directiveParagraphRepository.getByName("txtDirCode",pageR);
        List<DirectiveParagraph> resultList = result.getContent();
    }

Upvotes: 7

bradleyfitz
bradleyfitz

Reputation: 691

Limit is not supported in HQL. To set the size, you would use setMaxResults. To set start point, you use setFirstResult.

Query q = session.createQuery("...");
q.setFirstResult(10);
q.setMaxResults(10);

If you don't want to do it that way, you would have to use createSQLQuery to write a native sql query instead of hql.

Upvotes: 0

Related Questions