How to customize the results of a JPA query using aggregate functions?

Most of the time, when we use JPA queries, the results obtained are mapped to objects/specific data types. But when we use aggregate functions in queries, processing the results sometimes requires us to customize the JPA query.

Let us understand (department, employee) through an example −

Dept.java

@Entity
public class Dept {
   @Id
   private Long id;
   private String name;
   @OneToMany(mappedBy = "dep")
   private List<Employee> emp;
   //Getters
   //Setters
}

A department can have one or more employees, but an employee can only belong to one department.

Employee.java

@Entity
public class Employee {
   @Id
   private Long id;
   private Integer joiningyear;
   @ManyToOne
   private Dept dep;
   
   //Getters
   //Setters
}

Now, if we want to get the joining date and the number of employees grouped by joining date,

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
   // query methods
   @Query("SELECT e.joiningyear, COUNT(e.joiningyear) FROM Employee AS e GROUP BY e.joiningyear")
   List<Object[]> countEmployeesByJoiningYear();
}

The above query works fine, but storing values ​​in the form of List

The Chinese translation of

CountEmployees.java

is:

CountEmployees.java

package com.tutorialspoint;
public class CountEmployees {
   private Integer joinyear;
   private Long totalEmp;
   
   public CountEmployees(Integer joinyear, Long totalEmp) {
      this.joinyear = joinyear;
      this.totalEmp = totalEmp;
   }
   //Getters
   //Setters
}

Now, we can customize our JPA query as shown below −

@Query("SELECT new com.tutorialspoint.CountEmployees(e.joiningyear, COUNT(e.joiningyear)) " + "FROM Employee AS e GROUP BY e.joiningyear")
List<CountEmployees> countEmployeesByJoining();

The results of the above select query will be mapped to the CountEmployees class. In this way we can customize JPA queries and map the results to java classes.

The above is the detailed content of How to customize the results of a JPA query using aggregate functions?. For more information, please follow other related articles on the PHP Chinese website!