0

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.

Santi Gallego
  • 202
  • 1
  • 18
  • FYI, `java.sql.Timestamp` was supplanted years ago by the *java.time* classes with the adoption of JSR 310. Replaced by `java.time.Instant` or `java.time.OffsetDateTime`, with support for the first being optional in JDBC 4.2 and the second being required. – Basil Bourque Jul 15 '20 at 01:25

1 Answers1

0

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.

Marc
  • 2,738
  • 1
  • 17
  • 21
  • I understand how to do it this way, however this is not viable for me. I need to build up a complex query based off several different fields thats that may or may not be provided in the request. If I used the CrudRepository interface I would need to create an unrealistic amount of methods to achieve the functionality I want. That means I have to use a a custom repo and build up the query using CriteriaBuilder. I could what you suggest and in my manager call both my custom method and something like the above and take the intersection of both result sets, but I would rather do it all at once. – Santi Gallego Jul 15 '20 at 19:37