Learn how to use the JPA Standards API to write down absolutely dynamic queries
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.
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.date, 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:
Let’s identify the service that may question the information
ServerRecordFinder and acquire an
Let’s write a way that returns the data:
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 choose half will get the chosen columns from the consumer enter and places them into the
That’s the content material of the
aliastechnique 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
the placeclause 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 byclause. Word that we mix the
group byvalues with the
choosevalues. This ensures that every choice that doesn’t have an combination is included within the
Now it’s time to create a
TypedQuery and execute it:
question.getResultList()technique returns a
Tuplethat comprises the question outcomes.
- Create a brand new technique to entry 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
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(CriteriaQueryTupleTransformer.java:93)
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
- 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!