use Limit on HQL query

Question

I have a HQL Query in JpaRepository:

public interface LineRepository extends JpaRepository<LineEntity , Integer>{
    @Query(
            value = "SELECT l from LineEntity l" +
                    "WHERE l.lineCode = :lineCode "
    )
    List<LineEntity> findAllByLineCode(
            @Param("lineCode")int lineCode
    );

}

I want to change this method to return a LineEntity. Limit just work on nativeQuery and i want put this into annotation

Possible duplicate of [What is the LIMIT clause alternative in JPQL?](https://stackoverflow.com/questions/44565820/what-is-the-limit-clause-alternative-in-jpql) — Tom, Aug 16 '18 at 12:44
You need to implement that as a native query. You can not use Limit in HQL because Limit is database vendor dependent so hibernate doesn't allow it through hql query. — David Jesus, Jul 01 '20 at 19:40

David Jesus · Answer 1 · 2020-07-02T14:14:21.860

1

If there are a lot of LineEntity with the same LineCode then you need an extra attribute, that attribute can be the id, for example:

public interface LineRepository extends JpaRepository<LineEntity , Integer>{
    @Query(
            value = "FROM LineEntity li WHERE li.id = " +
                    "(SELECT max(l.id) FROM LineEntity l " +
                    "WHERE l.lineCode = :lineCode)"
    )
    LineEntity findFirstByLineCode(
            @Param("lineCode") int lineCode
    );
}

edited Jul 02 '20 at 14:14

answered Jul 02 '20 at 02:15

David Jesus

1,041
2
17
24

is sub query optimized? what are u doing ? answered my question or create new problem ? – Morteza Jalambadani Jul 02 '20 at 08:21
Yesterday I had the same problem and solved it this way. I only share one solution. – David Jesus Jul 02 '20 at 14:13

score 0 · Answer 2 · answered Aug 16 '18 at 09:58

0

On the query object in hibernate you have a setMaxResults(int maxResults) method.

I am not use of how to put this into annotation but with the classical way it works like this:

Query q = session.createQuery("XXX").setMaxResults(1000);
q.list();

answered Aug 16 '18 at 09:58

Orden

441
2
11

1

yes this solution is right but a want use it into annotation – Morteza Jalambadani Aug 16 '18 at 10:08

score 0 · Answer 3 · answered Aug 16 '18 at 10:50

If you want to do this without query, then we can use Pageable interface. Just do two steps.

1) In Controller

@RequestMapping("/getLineByLineCode/{code}")    
public @ResponseBody Page<LineEntity> getFirstUserByName(@PathVariable String code,Pageable pageable)
{
  // this pageable for single result.(first result will return)
    Page<LineCode> line = repository.findByCode(code,new PageRequest(0,1));
                                                        //(start index,upto index)
   return line;
}

2) In Repository

public interface LineRepository extends JpaRepository<LineEntity , Integer>{
       Page<LineCode> findByCode(String code,Pageable pageable);
}

score 0 · Answer 4 · answered Aug 16 '18 at 11:37

0

As the documentation states

The results of query methods can be limited by using the first or top keywords, which can be used interchangeably. An optional numeric value can be appended to top or first to specify the maximum result size to be returned. If the number is left out, a result size of 1 is assumed

So to limit by 10 you can also use:

List<LineEntity> findTop10ByLineCode(int lineCode);

answered Aug 16 '18 at 11:37

mtshaikh

349
3
12

2

my problem is on hql query i want use is on @query annotation and work same as findFirst the query in my question is simple – Morteza Jalambadani Aug 16 '18 at 12:09

use Limit on HQL query

4 Answers4