How to handle date ranges with QueryDSL Predicate in Spring?

Question

I have a db with various events, and a corresponding entity:

@NoArgsConstructor
@Data
@Table(name="Event")
public class Event {
    @Id
    private String id;
    private String source;
    private String action;
    private Date timestamp;
    private String data;
}

And a controller that takes a QueryDSLPredicate and parameters:

    @GetMapping("/events")
    public List<Event> getEvents(@QuerydslPredicate(root = Event.class) Predicate predicate,
                                           @RequestParam(name = "id", required = false) String id,
                                           @RequestParam(name = "source", required = false) String source,
                                           @RequestParam(name = "action", required = false) String action,
                                           @RequestParam(name = "startTimestamp", required = false) String startTimestamp,
                                           @RequestParam(name = "endTimestamp", required = false) String endTimestamp,
                                           @RequestHeader(required = false) String requestId) {
        return StreamSupport.stream(eventRepository.findAll(predicate).spliterator(), false).collect(Collectors.toList());

This works great for sending specified parameters like this http://localhost:8080/events?source=testSource&action=testAction

But how can I handle the date ranges by sending in a starttime and endtime, and pulling all events between those times? I'm new to querydsl/spring (in fact I haven't written java since college) and can't seem to find a great example on how to do this.

score 1 · Accepted Answer · answered Aug 26 '22 at 21:45

Got this working with help from this answer https://stackoverflow.com/a/35158320/2930025

Changed my controller like so:

@GetMapping("/events")
    public List<Event> getEvents(@QuerydslPredicate(root = Event.class) Predicate predicate,
                                           @RequestHeader(required = false) String requestId) {
        return StreamSupport.stream(eventRepository.findAll(predicate).spliterator(), false).collect(Collectors.toList());

And then added an overloaded customize method to the repository class:

@Repository
public interface EventRepository extends JpaRepository<Event,String>, QuerydslPredicateExecutor<Event>, QuerydslBinderCustomizer<QAuditEvent> {
        @Override
    default void customize(QuerydslBindings bindings, QEvent root) {
        bindings.bind(root.timestamp).all((path, value) ->
        {
            Iterator<? extends Date> it = value.iterator();
            return Optional.ofNullable(path.between(it.next(), it.next()));
        });
    }
}

And then to call simply send in two timestamps, one for the startTime and one for the endTime:

http://localhost:8080/events?timestamp=2022-07-05T22:16:18Z&timestamp=2022-08-27T22:16:18Z

Looks like the second answer on the linked question would also work, however I couldn't get the imports working correctly so went with the accepted answer's method.

How to handle date ranges with QueryDSL Predicate in Spring?

1 Answers1