Querying with Timestamps using SpringBoot and JPA Criteria Query in custom repository

Question

How do you query based off timestamps in custom JPA repositories using JPA Criteria Query?

I have a startTime and endTime that I want to use to query for entires with a field time between them, but I need to do this in a custom repository because there are a lot of other fields that I need to use to build a complex query.

I've can succcessfully build up a complex query and get correct results, except when I try to query by startTime and endTime. I've been trying to do something like the following but with no luck

@PersistenceContext private EntityManager em;
...
  final CriteriaBuilder cb = this.em.getCriteriaBuilder();
  final CriteriaQuery<AutoenrollmentEvent> cr = cb.createQuery(Event.class);
  final Root<AutoenrollmentEvent> root = cr.from(Event.class);
  final List<Predicate> predicates = new ArrayList<>();
  ...
    predicates.add(cb.between(root.get("time"), data.getStartTime(), data.getEndTime()));
  ...
  cr.select(root).where(predicates.toArray(new Predicate[0]));
  return this.em.createQuery(cr).getResultList();

where data.getStartTime() and data.getEndTime() are of type java.sql.Timestamp.

The idea is to get all the entries with a timestamp in field time between startTime and endTime.

Any ideas on how to do this?

Thanks.

Answer 1

You can achieve that by using Spring Data JPA CrudRepository interface:

public interface EventRepository extends CrudRepository<Event, Long> {
    List<Event> findAllByTimeAfterAndTimeBefore(Timestamp start, Timestamp end);
}

And then using it:

public class MyClass {
    @Autowired
    private EventRepository eventRepository;

    public void myMethod() {
        Timestamp startTime = // create start timestamp
        Timestamp endTime = // create end timestamp
        List<Event> events = eventRepository.findAllByTimeAfterAndTimeBefore(startTime,endTime);
    }
}

Spring will implement the query automatically and return the results. Official docs.