Generic Top

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE

If you have a few years of experience in the Java ecosystem, and you're interested in sharing that experience with the community (and getting paid for your work of course), have a look at the "Write for Us" page. Cheers. Eugen

1. Introduction

In this tutorial, we’re going to learn about limiting query results with JPA and Spring Data JPA.

First, we’ll take a look at the table we want to query as well as the SQL query we want to reproduce.

Then we’ll dive right into how to achieve that with JPA and Spring Data JPA.

Let’s get started!

2. The Test Data

Below we have the table that we’ll be querying throughout this article.

The question we want to answer is, “What is the first occupied seat and who is occupying it?”.

First Name Last Name Seat Number
Jill Smith 50
Eve Jackson 94
Fred Bloggs 22
Ricki Bobbie 36
Siya Kolisi 85

3. The SQL

With SQL we might write a query that looks something like this:

SELECT firstName, lastName, seatNumber FROM passengers ORDER BY seatNumber LIMIT 1;

4. JPA Setup

With JPA, we need an Entity first, to map our table:

@Entity
class Passenger {

    @Id
    @GeneratedValue
    @Column(nullable = false)
    private Long id;

    @Basic(optional = false)
    @Column(nullable = false)
    private String fistName;

    @Basic(optional = false)
    @Column(nullable = false)
    private String lastName;

    @Basic(optional = false)
    @Column(nullable = false)
    private int seatNumber;

    // constructor, getters etc.
}

Next we need a method which encapsulates our query code, implemented here as PassengerRepositoryImpl#findOrderedBySeatNumberLimitedTo(int limit):

@Repository
class PassengerRepositoryImpl {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Passenger> findOrderedBySeatNumberLimitedTo(int limit) {
        return entityManager.createQuery("SELECT p FROM Passenger p ORDER BY p.seatNumber",
          Passenger.class).setMaxResults(limit).getResultList();
    }
}

In our repository method, we use the EntityManager to create a Query on which we call the setMaxResults() method.

This call to Query#setMaxResults will eventually result in the limit statement appended to the generated SQL:

select
  passenger0_.id as id1_15_,
  passenger0_.fist_name as fist_nam2_15_,
  passenger0_.last_name as last_nam3_15_,
  passenger0_.seat_number as seat_num4_15_
from passenger passenger0_ order by passenger0_.seat_number limit ?

5. With Spring Data JPA

We can also generate our SQL using Spring Data JPA.

5.1. first or top

One way we could approach this is by using method name derivation with the keywords first or top.

We can, optionally, specify a number as the maximum result size that will be returned. If we omit it, Spring Data JPA assumes a result size of 1.

Remembering that we want to know what is the first occupied seat and who is occupying it, we can get it omitting the number in these two ways:

Passenger findFirstByOrderBySeatNumberAsc();
Passenger findTopByOrderBySeatNumberAsc();

If we limit to one instance result, as above, then we can also wrap the result using Optional:

Optional<Passenger> findFirstByOrderBySeatNumberAsc();
Optional<Passenger> findTopByOrderBySeatNumberAsc();

5.2. Pageable

Alternatively, we can use a Pageable object:

Page<Passenger> page = repository.findAll(PageRequest.of(0, 1, Sort.by(Sort.Direction.ASC, "seatNumber")));

If we take a look at the default implementation of JpaRepository, the SimpleJpaRepositorywe can see that it also calls Query#setMaxResults:

protected <S extends T > Page < S > readPage(TypedQuery < S > query, 
  Class < S > domainClass, Pageable pageable,
  @Nullable Specification < S > spec) {
    if (pageable.isPaged()) {
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
    }

    return PageableExecutionUtils.getPage(query.getResultList(), pageable, () -> {
        return executeCountQuery(this.getCountQuery(spec, domainClass));
    });
}

5.3. Comparison

Both of these alternatives will produce the SQL that we are after:

select
  passenger0_.id as id1_15_,
  passenger0_.fist_name as fist_nam2_15_,
  passenger0_.last_name as last_nam3_15_,
  passenger0_.seat_number as seat_num4_15_ 
from passenger passenger0_ order by passenger0_.seat_number asc limit ?

With first and top favoring convention and Pageable favoring configuration.

6. Conclusion

Limiting query results in JPA is slightly different to SQL – we don’t include the limit keyword directly into our JPQL.

Instead, we just make a single method call to Query#maxResults or include the keyword first or top in our Spring Data JPA method name.

As always, you can find the code over on GitHub.

Generic bottom

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE