Master Dynamic Queries in Spring JPA

How to write fully dynamic queries with Spring Data

Photo by Christopher Gower on Unsplash

Recently, I faced a challenging task that required me to create a fully dynamic query. Since the solution was somewhat time-consuming to discover, I decided it would be beneficial to write a dedicated article about it. I hope this will assist others facing similar issues.

Scenario

Consider the following situation:

You have an entity with 5 columns. The group by clause varies based on the user input. For instance, assume that there is a UI where users can choose which columns to select and how to group them.

For example:

Example of selected columns by the author

The fields in green have been selected by the user.

Suppose that your app produces the following JPA query. What is the problem with it?

select sr.user, sr.timestamp, sr.status, sr.date, sr.instances from SERVER_RECORD sr where sr.user = 'user123' group by sr.status;

It will show an error saying that the rest of the columns, namely, “user, timestamp, date, instances”, are not included in the group by clause or not aggregated:

Column ‘{the column name}’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

All columns in the select clause that don’t have an aggregate need to be in the group by part.

In this post, I’m going to show you one way how to address the issue.

This tutorial assumes that you’re already familiar with the Java Persistence Criteria API.

Let’s dive into it!