By default, Spring Data JPA uses position-based parameter binding, as we saw in our previous tutorials. We can also use named parameter with @Param annotation to give a method parameter a concrete name and bind the name in the query. That makes refactoring of code easier in case we have to add/remove additional parameters.

@Param works with both @Query and @NamedQuery.

Example

Entity

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

Repository

Following query finds the employees having top N salaries in a given department.

package com.logicbig.example;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
  @Query("SELECT e FROM Employee e WHERE e.dept = :dept AND "
          + "(SELECT COUNT(DISTINCT e2.salary) FROM Employee e2 "
          + "WHERE e.salary < e2.salary AND e2.dept = :dept) < :topSalNum "
          + "ORDER BY e.salary DESC")
  List<Employee> findByDeptTopNSalaries(@Param("topSalNum") long topSalaryNum, @Param("dept") String dept);
}

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);

      for (int i = 1; i <= 6; i++) {
          System.out.printf(" --  finding employees have top %s salaries in IT dept  --%n", i);
          List<Employee> es = repo.findByDeptTopNSalaries(i, "IT");
          es.forEach(System.out::println);
      }
  }

  private List<Employee> createEmployees() {
      return Arrays.asList(
              Employee.create("Diana", "IT", 2000),
              Employee.create("Mike", "IT", 1000),
              Employee.create("Rose", "IT", 4000),
              Employee.create("Sara", "IT", 3500),
              Employee.create("Randy", "Admin", 3000),
              Employee.create("Linda", "IT", 3000),
              Employee.create("Charlie", "Admin", 2500)
      );
  }
}

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();
  }
}

 -- finding all employees --
Employee{id=1, name='Diana', dept='IT', salary=2000}
Employee{id=2, name='Mike', dept='IT', salary=1000}
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='IT', salary=3500}
Employee{id=5, name='Randy', dept='Admin', salary=3000}
Employee{id=6, name='Linda', dept='IT', salary=3000}
Employee{id=7, name='Charlie', dept='Admin', salary=2500}
 --  finding employees have top 1 salaries in IT dept  --
Employee{id=3, name='Rose', dept='IT', salary=4000}
 --  finding employees have top 2 salaries in IT dept  --
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='IT', salary=3500}
 --  finding employees have top 3 salaries in IT dept  --
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='IT', salary=3500}
Employee{id=6, name='Linda', dept='IT', salary=3000}
 --  finding employees have top 4 salaries in IT dept  --
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='IT', salary=3500}
Employee{id=6, name='Linda', dept='IT', salary=3000}
Employee{id=1, name='Diana', dept='IT', salary=2000}
 --  finding employees have top 5 salaries in IT dept  --
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='IT', salary=3500}
Employee{id=6, name='Linda', dept='IT', salary=3000}
Employee{id=1, name='Diana', dept='IT', salary=2000}
Employee{id=2, name='Mike', dept='IT', salary=1000}
 --  finding employees have top 6 salaries in IT dept  --
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='IT', salary=3500}
Employee{id=6, name='Linda', dept='IT', salary=3000}
Employee{id=1, name='Diana', dept='IT', salary=2000}
Employee{id=2, name='Mike', dept='IT', salary=1000}

Example Project

Dependencies and Technologies Used:

• spring-data-jpa 2.0.7.RELEASE: Spring Data module for JPA repositories.
  Uses org.springframework:spring-context version 5.0.6.RELEASE
• hibernate-core 5.3.1.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.3.9