Close

Spring Data JPA - Using @Query Annotation

[Updated: May 28, 2018, Created: May 28, 2018]

Other than using @NamedQuery (last tutorial) to specify declared query, Spring provides another option for that. As queries are executed by the repository methods, they should closely be tied to them.
We can bind the repository methods directly to the JPQL queries by using Spring specific @Query annotation.

Queries declared by @Query take precedence over queries defined using @NamedQuery.

Example

Entity

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

Repository Interface

package com.logicbig.example;

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

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

  @Query("SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept HAVING e.dept in ?1")
  public List<Object[]> findMaxSalariesByDept(List<String> deptNames);
}

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 max salaries in Admin and IT depts  --");
      List<Object[]> list = repo.findMaxSalariesByDept(Arrays.asList("Admin", "IT"));
      list.forEach(arr -> {
          System.out.println(Arrays.toString(arr));
      });
  }

  private List<Employee> createEmployees() {
      return Arrays.asList(
              Employee.create("Diana", "Admin", 2000),
              Employee.create("Mike", "Sale", 1000),
              Employee.create("Rose", "IT", 4000),
              Employee.create("Sara", "Admin", 3500),
              Employee.create("Randy", "Sale", 3000),
              Employee.create("Charlie", "IT", 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='Admin', salary=2000}
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='Randy', dept='Sale', salary=3000}
Employee{id=6, name='Charlie', dept='IT', salary=2500}
-- finding max salaries in Admin and IT depts --
[IT, 4000]
[Admin, 3500]

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

Using @Query Annotation Select All Download
  • spring-data-query-annotation
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • EmployeeRepository.java
          • resources
            • META-INF

    See Also