Curious Devs Corner

Curious Devs Corner

Share this post

Curious Devs Corner
Curious Devs Corner
Master Dynamic Queries in Spring JPA
Exclusive Content

Master Dynamic Queries in Spring JPA

How to write fully dynamic queries with Spring Data

KirshiYin's avatar
KirshiYin
Jul 13, 2024
∙ Paid

Share this post

Curious Devs Corner
Curious Devs Corner
Master Dynamic Queries in Spring JPA
Share
A MacBook with lines of code on its screen on a busy desk
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!

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.

Already a paid subscriber? Sign in
© 2025 Kirshi Yin
Privacy ∙ Terms ∙ Collection notice
Start writingGet the app
Substack is the home for great culture

Share