How to customize the Result of JPA Queries with Aggregation Functions?

Most of the time when we use JPA queries, the result obtained is mapped to an object/particular data type. But When we use aggregate function in queries, handling the result sometimes require us to customize our JPA query.

Let’s understand this with help of an example (Department, Employee) −

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 will belong to only 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 fetch the joining date and count of employees grouped by joining date then,

@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 will work fine but this way of storing the values in List<Object[]> can be erroneous to work with. Instead, we can customize our JPA queries to map the result of the above query to a java class. This java class is simply a pojo (Plain Old Java Object), and it need not to be annotated by @Entity.

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 −

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

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

Manu

Updated on: 26-Aug-2022

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started