Close

Spring Data JPA - Limiting Query Results

[Updated: Jul 18, 2018, Created: Jul 17, 2018]

Spring Data JPA supports keywords 'first' or 'top' to limit the query results (e.g. findTopBy....).

An optional numeric value can be appended after 'top' or 'first' to limit the maximum number of results to be returned (e.g. findTop3By....). If this number is not used then only one entity is returned.

There's no difference between the keywords 'first' and 'top'.

Example

Entity

@Entity
public class Employee {
  private @Id
  @GeneratedValue
  Long id;
  private String name;
  private String dept;
  private int salary;
    .............
}

Repository

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

  Employee findTopByOrderBySalaryDesc();

  Employee findTopByOrderBySalaryAsc();

  List<Employee> findTop3ByOrderBySalaryDesc();

  List<Employee> findTop3ByOrderBySalaryAsc();

  List<Employee> findFirst2BySalary(int salary);

  List<Employee> findFirst2ByDeptOrderBySalaryDesc(String deptName);
}

Example Client

@Component
public class ExampleClient {
  @Autowired
  private EmployeeRepository repo;

  public void run() {
      List<Employee> employees = createEmployees();
      repo.saveAll(employees);

      System.out.println(" -- finding all employees --");
      Iterable<Employee> all = repo.findAll();
      all.forEach(System.out::println);

      System.out.println(" -- finding the employee with max salary --");
      Employee emp = repo.findTopByOrderBySalaryDesc();
      System.out.println(emp);

      System.out.println(" -- finding the employee with min salary --");
      emp = repo.findTopByOrderBySalaryAsc();
      System.out.println(emp);

      System.out.println(" -- finding the top 3 employees with max salary --");
      List<Employee> list = repo.findTop3ByOrderBySalaryDesc();
      list.stream().forEach(System.out::println);

      System.out.println(" -- finding the top 3 employees with min salary --");
      list = repo.findTop3ByOrderBySalaryAsc();
      list.stream().forEach(System.out::println);

      System.out.println(" -- finding the first 2 employees with salary 3000 --");
      list = repo.findFirst2BySalary(3000);
      list.stream().forEach(System.out::println);

      System.out.println(" -- finding the top 2 employees with max salary in IT dept --");
      list = repo.findFirst2ByDeptOrderBySalaryDesc("IT");
      list.stream().forEach(System.out::println);
  }
    .............
}

Main class

public class ExampleMain {

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      ExampleClient exampleClient = context.getBean(ExampleClient.class);
      exampleClient.run();
      EntityManagerFactory emf = context.getBean(EntityManagerFactory.class);
      emf.close();
  }
}
Hibernate: create table Employee (id bigint not null, dept varchar(255), name varchar(255), salary integer not null, primary key (id))
Hibernate: insert into Employee (dept, name, salary, id) values (?, ?, ?, ?)
Hibernate: insert into Employee (dept, name, salary, id) values (?, ?, ?, ?)
Hibernate: insert into Employee (dept, name, salary, id) values (?, ?, ?, ?)
Hibernate: insert into Employee (dept, name, salary, id) values (?, ?, ?, ?)
Hibernate: insert into Employee (dept, name, salary, id) values (?, ?, ?, ?)
Hibernate: insert into Employee (dept, name, salary, id) values (?, ?, ?, ?)
-- finding all employees --
Hibernate: select employee0_.id as id1_0_, employee0_.dept as dept2_0_, employee0_.name as name3_0_, employee0_.salary as salary4_0_ from Employee employee0_
Employee{id=1, name='Diana', dept='Admin', salary=3000}
Employee{id=2, name='Mike', dept='Sale', salary=1000}
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='Admin', salary=3500}
Employee{id=5, name='Tanaka', dept='IT', salary=3000}
Employee{id=6, name='Charlie', dept='IT', salary=2500}
-- finding the employee with max salary --
Hibernate: select employee0_.id as id1_0_, employee0_.dept as dept2_0_, employee0_.name as name3_0_, employee0_.salary as salary4_0_ from Employee employee0_ order by employee0_.salary desc limit ?
Employee{id=3, name='Rose', dept='IT', salary=4000}
-- finding the employee with min salary --
Hibernate: select employee0_.id as id1_0_, employee0_.dept as dept2_0_, employee0_.name as name3_0_, employee0_.salary as salary4_0_ from Employee employee0_ order by employee0_.salary asc limit ?
Employee{id=2, name='Mike', dept='Sale', salary=1000}
-- finding the top 3 employees with max salary --
Hibernate: select employee0_.id as id1_0_, employee0_.dept as dept2_0_, employee0_.name as name3_0_, employee0_.salary as salary4_0_ from Employee employee0_ order by employee0_.salary desc limit ?
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='Admin', salary=3500}
Employee{id=5, name='Tanaka', dept='IT', salary=3000}
-- finding the top 3 employees with min salary --
Hibernate: select employee0_.id as id1_0_, employee0_.dept as dept2_0_, employee0_.name as name3_0_, employee0_.salary as salary4_0_ from Employee employee0_ order by employee0_.salary asc limit ?
Employee{id=2, name='Mike', dept='Sale', salary=1000}
Employee{id=6, name='Charlie', dept='IT', salary=2500}
Employee{id=5, name='Tanaka', dept='IT', salary=3000}
-- finding the first 2 employees with salary 3000 --
Hibernate: select employee0_.id as id1_0_, employee0_.dept as dept2_0_, employee0_.name as name3_0_, employee0_.salary as salary4_0_ from Employee employee0_ where employee0_.salary=? limit ?
Employee{id=1, name='Diana', dept='Admin', salary=3000}
Employee{id=5, name='Tanaka', dept='IT', salary=3000}
-- finding the top 2 employees with max salary in IT dept --
Hibernate: select employee0_.id as id1_0_, employee0_.dept as dept2_0_, employee0_.name as name3_0_, employee0_.salary as salary4_0_ from Employee employee0_ where employee0_.dept=? order by employee0_.salary desc limit ?
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=5, name='Tanaka', dept='IT', salary=3000}

As seen above the 'limit' clause of H2 database was used to find the top results. Oracle will use 'ROWNUM' clause to achieve the same.

Example Project

Dependencies and Technologies Used:

  • spring-data-jpa 2.0.8.RELEASE: Spring Data module for JPA repositories.
    Uses org.springframework:spring-context version 5.0.7.RELEASE
  • hibernate-core 5.3.2.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • h2 1.4.197: H2 Database Engine.
  • JDK 1.8
  • Maven 3.5.4

Limiting Query Results Select All Download
  • spring-data-jpa-limiting-query-results
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • EmployeeRepository.java
          • resources
            • META-INF

    See Also