Spring - Limiting Query Result with Spring Data JPA

Sometimes we may need to limit the result returned by query. Adding LIMIT clause is the solution if we use SQL query. Unfortunately, it's not supported by Spring Data JPA's @Query annotation. It will throw error if you add LIMIT to the query. If you are using Spring or Spring Boot, here are the simple solutions to limit the query result.

Using Pageable

Maybe you're already familiar with Pageable. It's usually used for pagination. But, even if you only need to limit the query without using pagination, it can be useful too. In the repository, where you define the method, just add Pageable as the last parameter.

ItemRepository.java

  package com.woolha.example.querylimit.repository;

  import com.woolha.example.querylimit.model.Item;
  import org.springframework.data.domain.Page;
  import org.springframework.data.domain.Pageable;
  import org.springframework.data.repository.PagingAndSortingRepository;

  public interface ItemRepository extends PagingAndSortingRepository<Item, UUID> {
      Page<Item> findAllByTypeAndIsActive(String type, Boolean isActive, Pageable pageable);
  }

That means we have to pass an instance of Pageable as the third argument. Below is the example of limiting query result to 20 (the second argument). The first argument is the offset, while the third argment allows us to define the ORDER BY clause. To get the result as List<Item>, use .getContent().

ItemServiceImpl.java

  Pageable pageable = PageRequest.of(0, 20, Sort.by(Sort.Direction.DESC, "updatedAt"));

  return this.itemRepository.findAllByTypeAndIsActive(user, true, pageable).getContent();

Using EntityManager

Another way is using EntityManager. Use the createQuery method to define the query, set all parameters and define the limit with setMaxResults.

  package com.woolha.example.querylimit.repository;

  import com.woolha.example.querylimit.model.Item;
  import org.springframework.stereotype.Repository;

  import javax.persistence.EntityManager;
  import javax.persistence.PersistenceContext;
  import java.util.List;

  @Repository
  public class ItemRepositoryImpl {

      @PersistenceContext
      private EntityManager entityManager;

      public List<Item> findAllByTypeAndIsActive(String type,
                                                 Boolean isActive,
                                                 int limit) {
          return entityManager.createQuery("SELECT i FROM Item i"
                          + " WHERE (i.type IS :type)"
                          + " AND (i.isActive = :isActive)",
                  Item.class)
                  .setParameter("type", type)
                  .setParameter("isActive", isActive)
                  .setMaxResults(limit).getResultList();
      }
  }