Master Dynamic Queries in Spring JPA
How to write fully dynamic queries with Spring Data
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:
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!
Keep reading with a 7-day free trial
Subscribe to Curious Devs Corner to keep reading this post and get 7 days of free access to the full post archives.