51

How can I limit in a select query of JPQL named query? I need the limit to be done in the query level itself and not in the java layer!!! I am trying to use

@NamedQueries(value = {
        @NamedQuery(name = UserNotification.QueryName.NOTIFICATION_DISPLAYED,
                    query = "SELECT un FROM UserNotification un " +
                            "WHERE un.orgId IN (:orgList) " +
                            "AND un.user.id = :userId LIMIT 5")

but in vain!!!

Please suggest

Kevin Bowersox
  • 93,289
  • 19
  • 159
  • 189
user3115056
  • 1,266
  • 1
  • 10
  • 25

4 Answers4

63

JPQL does not provide a mechanism to limit queries. This is most often achieved by using the setMaxResults() method on the Query. If you must avoid specifying this in Java code, you could make a view in the database that contains your query and performs the limit. Then map an entity to this view as you would a table.

Example:

List<String> resultList= query.setMaxResults(100).getResultList();
tomrozb
  • 25,773
  • 31
  • 101
  • 122
Kevin Bowersox
  • 93,289
  • 19
  • 159
  • 189
43

In case you are working with spring-data you should use the Pageable Interface. A sample code below,

My Service, 

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;

@Service
public class MyModelService {

    @Autowired
    private MyModelRepository myModelRepository;

    @Transactional
    public Page<MyModel> findMyModelTop5() {
        return myModelRepository.findMyModelTop5(new PageRequest(0, 5));
    }
}

My Repository,

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

@Repository
public interface MyModelRepository extends JpaRepository<MyModel, Integer> {

    @Query("SELECT mm FROM MyModel mm")
    public Page<MyModel> findMyModelTop5(Pageable pageable);

}

You can find a more complete answer about the spring data available options here.

Georgios Syngouroglou
  • 18,813
  • 9
  • 90
  • 92
  • 1
    This is exactly what I've been looking for, and through all the searches, only you mention it this way... Thanks... – Wilhelm Sorban Aug 30 '17 at 11:59
  • This was very useful. Much appreciated :) – Bimde Aug 10 '18 at 17:41
  • 3
    Please note, this solution will start 2 SQL queries: One query for the SELECT with LIMIT and a second query with SELECT COUNT for the Page's metadata totalElements. This could be slow, when a query contains some additional WHERE clauses. – Plutoz Feb 27 '19 at 10:29
  • 1
    Works like a charm, but with one exception - I had to set my query as native, otherwise, it would not run my spring boot application. `@Query(value = "SELECT mm FROM MyModel mm", nativeQuery = true)` – Tomas Lukac Sep 01 '19 at 11:53
  • Very useful Thanks – hexhad Jun 15 '21 at 14:01
  • 1
    As of Spring Data 2.4.9 there is no `new PageRequest()`. You need to use the static method `PageRequest.of(page, size)`. – Christian Lischnig Mar 21 '22 at 07:49
8

For specific @NamedQueries where a limit is required, you can switch to @NamedNativeQuery

@NamedNativeQuery(
  name=UserNotification.QueryName.NOTIFICATION_DISPLAYED_LIMIT5,
  query="SELECT un.* FROM user_notification un " + 
        "WHERE un.user.id = ?1 LIMIT 5",
  resultClass=UserNotification.class
)

Not quite as smooth, but does the job.

muttonUp
  • 6,351
  • 2
  • 42
  • 54
0

while executing the query with entity manager just write .setMaxResults(no of obj)

Udit Agarwal
  • 46
  • 4