Can Spring Data REST's QueryDSL integration be used to perform more complex queries?
Solution 1
I think you should be able to get this to work using the following customization:
bindings.bind(user.dateOfBirth).all((path, value) -> {
Iterator<? extends LocalDate> it = value.iterator();
return path.between(it.next(), it.next());
});
The key here is to use ?dateOfBirth=…&dateOfBirth=
(use the property twice) and the ….all(…)
binding which will give you access to all values provided.
Make sure you add the @DateTimeFormat
annotation to the dateOfBirth
-property of User
so that Spring is able to convert the incoming Strings
into LocalDate
instances correctly.
The lambda currently gets a Collection<? extends T>
which makes untangling the individual elements a bit more pain that it needs to be, but I think we can change this in a future release to rather expose a List
.
Solution 2
As it was posted in some comment I also had the need to have different behaviour according to the field name creationDateFrom
and creationDateTo
. In order to make it work I did the following:
First I added the @QueryEntity
annotation and two more fields to my entity class. The fields were annotated with:
@Transient
so the fields are not persisted@Getter(value = AccessLevel.PRIVATE)
as we are using Lombok, the annotation hides the field from the response body@DateTimeFormat(iso = DateTimeFormat.ISO.DATE)
takes care of the format for parsing the date on the url query parameter
@QueryEntity
@Entity
public class MyEntity implements Serializable {
...
@Column(updatable = false)
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE)
private Date creationDate;
@Transient
@Getter(value = AccessLevel.PRIVATE)
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE)
private Date creationDateTo;
@Transient
@Getter(value = AccessLevel.PRIVATE)
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE)
private Date creationDateFrom;
...
}
Then I changed the way of generating the querydsl classes from JPAAnnotationProcessor
to QuerydslAnnotationProcessor
. This way fields annotated with @Transient
are still generated on QMyEntity
but are not persisted. Plugin configuration in pom:
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/annotations</outputDirectory>
<processor>com.querydsl.apt.QuerydslAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
Finally I extended the QuerydslBinderCustomizer
and customized the bindings related with the creationDateFrom
and creationDateTo
but applying the right logic over creationDate
@Override
default void customize(QuerydslBindings bindings, QMyEntity root) {
bindings.bind(root.creationDateFrom).first((path, value) ->
root.creationDate.after(value));
bindings.bind(root.creationDateTo).first((path, value) ->
root.creationDate.before(value));
}
With all of this you can do date range queries using one, both or none of the criterias:
http://localhost:8080/myentities?creation_date_to=2017-05-08
http://localhost:8080/myentities?creation_date_from=2017-01-01
http://localhost:8080/myentities?creation_date_from=2017-01-01&creation_date_to=2017-05-08
Solution 3
This is what I used for a generic binding for all date fields, always expecting 2 values, from and to.
bindings.bind(Date.class).all((final DateTimePath<Date> path, final Collection<? extends Date> values) -> {
final List<? extends Date> dates = new ArrayList<>(values);
Collections.sort(dates);
if (dates.size() == 2) {
return path.between(dates.get(0), dates.get(1));
}
throw new IllegalArgumentException("2 date params(from & to) expected for:" + path + " found:" + values);
});
This is for datetime fields. For a date field, when getting a single parameter, path.eq()
makes sense I guess.
Dennis Laumen
I’m Dennis Laumen: a Dutch programmer, all-round nerd, and loving all things programming, tech, films & tv, games, coffee and Oxford commas.
Updated on June 07, 2022Comments
-
Dennis Laumen almost 2 years
I'm currently building a REST API in which I want clients to easily filter on most properties of a specific entity. Using QueryDSL in combination with Spring Data REST (an example by Oliver Gierke) allows me to easily get to 90% of what I want by allowing clients to filter by combining query parameters which refer to properties (e.g.
/users?firstName=Dennis&lastName=Laumen
).I can even customize the mapping between the query parameters and an entity's properties by implementing the
QuerydslBinderCustomizer
interface (e.g. for case insensitive searches or partial string matches). This is all great, however I also want the clients to be able to filter some types using ranges. For example with regards to a property like date of birth I'd like to do something like the following,/users?dateOfBirthFrom=1981-1-1&dateOfBirthTo=1981-12-31
. The same goes for number based properties,/users?idFrom=100&idTo=200
. I have the feeling this should be possible using theQuerydslBinderCustomizer
interface but the integration between these two libraries isn't documented very extensively.Concluding, is this possible using Spring Data REST and QueryDSL? If so, how?
-
Dennis Laumen about 8 yearsThanks @oliver-gierke! I got it to work based on the example you gave and added some more conditional logic so if only one date is given it's used as a "from" date. Could you give some more additional explanation whether it's also possible to add non-existent paths to the bindings? Is my example of adding a "dateOfBirthFrom" query parameter even possible using Spring Data REST and QueryDSL? (Just curious, you solved my problem already! Thanks again!)
-
gazal about 8 years@DennisLaumen "dateOfBirthFrom" style query parameter might be helpful. Your query binding customization assumes that if there is only one date its used as from date, right? But filtering using just a to date would not be possible. Any suggestions @oliver-gierke?
-
Dennis Laumen about 8 years@gazal, I fixed this using the following code, hope it helps.
bindings.bind(Date.class).all((DateTimePath<Date> path, Collection<? extends Date> value) -> { Iterator<? extends Date> it = value.iterator(); Date firstTimestamp = it.next(); if (it.hasNext()) { Date secondTimestamp = it.next(); return path.between(firstTimestamp, secondTimestamp); } else { return path.after(firstTimestamp); } });
-
gazal about 8 years@DennisLaumen thanks for that. What I meant is for the client to have the flexibility of specifying whether a single argument for a datefield should be applied as a before filter or after filter. On further examination, I think its safe to just expect the client to send a date like
-99999-01-01
as a lower infinity bound or99999-01-01
as a positive infinity bound in such cases. -
gazal about 8 years@DennisLaumen check my answer
-
Dennis Laumen about 8 years@gazal thanks for the additional info! I'm sure this will help future visitors to this page.
-
mancini0 over 6 years@Oliver-gierke - Are there plans for the QuerydslPredicateArgumentResolver to eventually construct the predicate from the URL itself? Most often it is too constraining to set the predicate behavior in advance, it would be nice if the QuerydslPredicateArgumentResolver could construct a Predicate from a query string like this: <endpoint>/products/?price.greaterThan=100&barcode.contains=ABC&saleEndDate.between=2017-08-10,2017-08-15