Matching a range of timestamps with JPA's Query by Example

Question

JPA Has a very neat option to query by example as explained here: Query by Example.

Is there any way to use this system to match to a timestamped object between two times?

For instance: I have logged sales in a database. I want to fetch all the sales of an item with ID: 7 between 07:00 and 13:00 on the 20th of October 2021.

In code terms, creating a query in the repository interface I would write:

@Query("SELECT s FROM Sale s "
         + "WHERE s.id IS ?1 "
         + "AND s.timestamp > ?2 "
         + "AND s.timestamp < ?3"
)
List<Sale> getSalesForIdBetweenTimestamps(
    Long ID,
    Timestamp after,
    Timestamp before
);

...and then call it from the service, but using example matching I could just call it like so:

Example<Sale> saleExample = Example.of(new Sale().withID(7));
List<Sale> salesWithID = this.salesRepository.findAll(saleExample);

...which looks neater and doesn't require writing in the repository.

I know how to provide the example with the ID to find all sales of that item, and I could even create a custom matcher using this class to do before and after a single time. I can use a different solution by avoiding the entire "Query by Example" system, which is what I'm doing right now, but I would like to use it and can't figure out how to match between two times.

score 0 · Accepted Answer · answered Oct 26 '21 at 13:38

I have found a solution in Specification thanks to this question.

A specification is built with a number of predicates, which can either be written manually or extracted from an Example. This means that creating an example makes the specification easier to write and read, and then you can tweak it to have the functionality that examples don't provide.

In my case the function that creates the specification looks like this:

public Specification<Sale> getSpecFromDatesAndExample(
      final Optional<Timestamp> after,
      final Optional<Timestamp> before,
      final Example<Sale> example
) {
    return (root, query, builder) -> {

      final List<Predicate> predicates = new ArrayList<>();

      after.ifPresent(timestamp -> predicates.add(builder.greaterThan(root.get("timestamp"), timestamp)));
      before.ifPresent(timestamp -> predicates.add(builder.lessThan(root.get("timestamp"), timestamp)));

      predicates.add(QueryByExamplePredicateBuilder.getPredicate(root, builder, example));

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

..which in turn can be passed into the repository like so:

public List<SaleDTO> getSales(
      final Pageable pageable,
      final Optional<String> productName,
      final Optional<String> after,
      final Optional<String> before,
      final Optional<Long> customerID,
      final Optional<Long> quantity
)
{
Sale example = new Sale();
productName.ifPresent(example::setProductName);
customerID.ifPresent(example::setCustomerID);
quantity.ifPresent(example::setQuantity);

Optional<Timestamp> afterTimestamp = after.map(Timestamp::valueOf);
Optional<Timestamp> beforeTimestamp = before.map(Timestamp::valueOf);

List<SaleDTO> saleList = new ArrayList<>();

return this.saleRepository.findAll(getSpecFromDatesAndExample(afterTimestamp, beforeTimestamp, Example.of(example)), pageable);

Matching a range of timestamps with JPA's Query by Example

1 Answers1