How to Create Fully Dynamic Queries in Spring Data

Learn how to use the JPA Standards API to write down absolutely dynamic queries

coding screen
Picture by Oskar Yildiz on Unsplash

Lately, I encountered a fancy drawback the place I needed to create a totally dynamic question. Since discovering the answer was a bit time-consuming, it’s value writing about it in a devoted article. I hope it helps others.


Think about the next state of affairs:

You could have an entity with 5 columns. The group by clause varies primarily based on the consumer enter. As an example, assume that there’s a UI the place customers can select which columns to pick out and how you can group them.

For instance:

Instance of chosen columns by the creator

The fields in inexperienced have been chosen by the consumer.

Suppose that your app produces the next JPA question. What’s the drawback with it?

choose sr.consumer, sr.timestamp, sr.standing,, sr.cases from SERVER_RECORD sr the place sr.consumer = 'user123' group by sr.standing;

It can present an error saying that the remainder of the columns, particularly, “consumer, timestamp, date, cases”, usually are not included within the group by clause or not aggregated:

Column ‘the column identify’ is invalid within the choose record as a result of it isn't contained in both an combination operate or the GROUP BY clause

All columns within the choose clause that don’t have an combination should be within the group by half.

On this submit, I’m going to point out you a method how you can deal with the problem.

This tutorial assumes that you simply’re already aware of the Java Persistence Criteria API.

Let’s dive into it!

For instance the state of affairs, let’s create a easy instance. Think about that you simply wish to question the data from a desk known as SERVER_RECORD. Right here is the entity class:

The ServerRecord entity

Let’s identify the service that may question the information ServerRecordFinder and acquire an EntityManager occasion:

Creating the report finder service

Let’s write a way that returns the data:

The getRecords() technique first half

Since we don’t have a UI, we’ll hard-code the consumer enter for demo functions:

The choose half:

The group by half:

Let’s assemble the question:

The getRecords() technique second half

The choose half will get the chosen columns from the consumer enter and places them into the Choice record.

That’s the content material of the getSelectedColumns(root) technique:

  • The alias technique is essential — that is how we will seek advice from the aliases when mapping the end result to the entity.
  • We go the choice record to the multiselect question technique.
  • The the place clause provides predicates to the criteriabuilder. On this instance, we wish to question the data the place the consumer=”xyz”.
  • Within the last question half, we assemble the group by clause. Word that we mix the group by values with the choose values. This ensures that every choice that doesn’t have an combination is included within the group by half.

Now it’s time to create a TypedQuery and execute it:

  • The question.getResultList() technique returns a Tuple that comprises the question outcomes.
  • Create a brand new technique to entry the TupleElement. The getAlias() technique collects all aliases we outlined earlier.

Now, we will iterate by means of the question end result and map it to the ServerRecord entity:

That is how the getValueByColumnName() technique appears to be like like:

You is likely to be questioning what’s the aim of this technique. Keep in mind that we outlined a dynamic record of alternatives for the choose clause. Due to this fact, it may not include all columns. So, in the event you attempt to use the tuple.get(“some alias identify”), and it doesn’t exist, it’ll throw an exception like this:

java.lang.IllegalArgumentException: Given alias [some alias] didn't correspond to a component within the end result tuple at org.hibernate.jpa.spi.CriteriaQueryTupleTransformer$TupleImpl.get(

Due to this fact we have to set solely these values whose column is within the selectedResultColumns set. In any other case, we set the property worth to null to skip it.

Lastly, the generated question appears to be like like this:

choose sr.TIMESTAMP, sr.USER from SERVER_RECORD sr the place sr.USER = 'xyz' group by sr.TIMESTAMP, sr.USER, sr.DATE;

On this tutorial, you realized how you can write superior dynamic JPA queries.

Let’s cowl the important thing factors:

  • We used CriteriaQuery and CriteriaBuilder to assemble a JPA question.
  • We assigned aliases to the columns within the choose clause.
  • We mapped the Tuple question end result to the entity by referring to the aliases.

The entire code snippet for this instance might be discovered here.

I hope that this tutorial has been useful. Thanks for studying, and glad coding!

More Posts