CODEWITHSUNDEEP
springspring-dataspring-data-jpaspring-data-restquerydsl
Dennis Laumen
Dennis Laumen

Reputation: 3223

Can Spring Data REST's QueryDSL integration be used to perform more complex queries?

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 the QuerydslBinderCustomizer 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?

Upvotes: 33

Views: 13201

Answers (3)

Jorge C
Jorge C

Reputation: 508

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

Upvotes: 16

gazal
gazal

Reputation: 322

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.

Upvotes: 5

Oliver Drotbohm
Oliver Drotbohm

Reputation: 83081

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.

Upvotes: 30

Related Questions