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

<div class="code-mirror  language-java" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token annotation punctuation">@Entity</span>
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">Dept</span> <span class="token punctuation">{</span>
   <span class="token annotation punctuation">@Id</span>
   <span class="token keyword">private</span> <span class="token class-name">Long</span> id<span class="token punctuation">;</span>
   <span class="token keyword">private</span> <span class="token class-name">String</span> name<span class="token punctuation">;</span>
   <span class="token annotation punctuation">@OneToMany</span><span class="token punctuation">(</span>mappedBy <span class="token operator">=</span> <span class="token string">"dep"</span><span class="token punctuation">)</span>
   <span class="token keyword">private</span> <span class="token class-name">List</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">Employee</span><span class="token punctuation">></span></span> emp<span class="token punctuation">;</span>
   <span class="token comment">//Getters</span>
   <span class="token comment">//Setters</span>
<span class="token punctuation">}</span>
</div>

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

Employee.java

<div class="code-mirror  language-java" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token annotation punctuation">@Entity</span>
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">Employee</span> <span class="token punctuation">{</span>
   <span class="token annotation punctuation">@Id</span>
   <span class="token keyword">private</span> <span class="token class-name">Long</span> id<span class="token punctuation">;</span>
   <span class="token keyword">private</span> <span class="token class-name">Integer</span> joiningyear<span class="token punctuation">;</span>
   <span class="token annotation punctuation">@ManyToOne</span>
   <span class="token keyword">private</span> <span class="token class-name">Dept</span> dep<span class="token punctuation">;</span>
   
   <span class="token comment">//Getters</span>
   <span class="token comment">//Setters</span>
<span class="token punctuation">}</span>
</div>

Now, if we want to fetch the joining date and count of employees grouped by joining date then,

<div class="code-mirror  language-java" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token annotation punctuation">@Repository</span>
<span class="token keyword">public</span> <span class="token keyword">interface</span> <span class="token class-name">EmployeeRepository</span> <span class="token keyword">extends</span> <span class="token class-name">JpaRepository</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">Employee</span><span class="token punctuation">,</span> <span class="token class-name">Long</span><span class="token punctuation">></span></span> <span class="token punctuation">{</span>
   <span class="token comment">// query methods</span>
   <span class="token annotation punctuation">@Query</span><span class="token punctuation">(</span><span class="token string">"SELECT e.joiningyear, COUNT(e.joiningyear) FROM Employee AS e GROUP BY e.joiningyear"</span><span class="token punctuation">)</span>
   <span class="token class-name">List</span><span class="token operator"><</span><span class="token class-name">Object</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token operator">></span> <span class="token function">countEmployeesByJoiningYear</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</div>

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

<div class="code-mirror  language-java" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token keyword">package</span> <span class="token namespace">com<span class="token punctuation">.</span>tutorialspoint</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">CountEmployees</span> <span class="token punctuation">{</span>
   <span class="token keyword">private</span> <span class="token class-name">Integer</span> joinyear<span class="token punctuation">;</span>
   <span class="token keyword">private</span> <span class="token class-name">Long</span> totalEmp<span class="token punctuation">;</span>
   
   <span class="token keyword">public</span> <span class="token class-name">CountEmployees</span><span class="token punctuation">(</span><span class="token class-name">Integer</span> joinyear<span class="token punctuation">,</span> <span class="token class-name">Long</span> totalEmp<span class="token punctuation">)</span> <span class="token punctuation">{</span>
      <span class="token keyword">this</span><span class="token punctuation">.</span>joinyear <span class="token operator">=</span> joinyear<span class="token punctuation">;</span>
      <span class="token keyword">this</span><span class="token punctuation">.</span>totalEmp <span class="token operator">=</span> totalEmp<span class="token punctuation">;</span>
   <span class="token punctuation">}</span>
   <span class="token comment">//Getters</span>
   <span class="token comment">//Setters</span>
<span class="token punctuation">}</span>
</div>

Now, we can customize our JPA query as ?

<span class="lit">@Query</span><span class="pun">(</span><span class="str">"SELECT new com.tutorialspoint.CountEmployees(e.joiningyear, COUNT(e.joiningyear)) "</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">"FROM Employee AS e GROUP BY e.joiningyear"</span><span class="pun">)</span><span class="pln">
</span><span class="typ">List</span><span class="pun"><</span><span class="typ">CountEmployees</span><span class="pun">></span><span class="pln"> countEmployeesByJoining</span><span class="pun">();</span>

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.