Spring queryByExample with Range between

Question

I have Spring Application with a repository

interface EventRepository extends JpaRepository<Event, Long>, QueryByExampleExecutor<Event> { }

Event e = new Event();
e.setTest('ABC');

eventRepository.findAll(Example.of(e), pageable);

Is working great and I am almost there. But I need to restrict to a date range between 'from' and 'to'

I have seen some post that it is not working with QBE but this was in 2015.

I have created a Range object but I don't know how to apply it.

http://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Range.html

I can not use the default spring way like

@Transactional
interface EventRepository extends JpaRepository<Event, Long>, QueryByExampleExecutor<Event> {

    def findBetween(Date lower, Date upper)

}

because I have a bunch of dynamic search parameters.

Epa

I found an asked question on SOF. – Andonis

Answer 1

You can extends from JpaSpecificationExecutor<T> too and get the Predicate with QueryByExamplePredicateBuilder from Example<T>. Keeping in mind that birthdateField belongs to the Event entity.

public Specification<Event> getSpecFromDatesAndExample(
      LocalDateTime from, LocalDateTime to, Example<Event> example) {
return (Specification<Event>) (root, query, builder) -> {
  final List<Predicate> predicates = new ArrayList<>();

  if (from != null) {
    predicates.add(builder.greaterThan(root.get("birthdateField"), from));
  }
  if (to != null) {
    predicates.add(builder.lessThan(root.get("birthdateField"), to));
  }
  predicates.add(QueryByExamplePredicateBuilder.getPredicate(root, builder, example));

  return builder.and(predicates.toArray(new Predicate[predicates.size()]));
};

}

And use Specification in findAll with JpaSpecificationExecutor.

List<Event> events = eventRepository.findAll(getSpecFromDatesAndExample(from, to, Example.of(Event)));

Sybil