Spring JPA: Use CriteriaBuilder to filter on a datetime between two instants-CodePudding

I'm setting up a method to filter entries by the "lastupdated" column. I'm trying to filter entries of which the lastupdated value is between a given startTime and endTime. I'm using the simplified code below:

public List<SomeEntity> getItemsByLastUpdated() {

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<SomeEntity> criteriaQuery = cb.createQuery(SomeEntity.class);

    var root = criteriaQuery.from(SomeEntity.class);
    var predicates = new ArrayList<>();

    var startTime = Instant.now();
    var endTime = Instant.now().plus(5, MINUTES);

    predicates.add(cb.greaterThanOrEqualTo(root.get("lastupdated"), startTime));
    predicates.add(cb.lessThan(root.get("lastupdated"), endTime));

    criteriaQuery.where(predicates.toArray(new Predicate[0]));
    return entityManager.createQuery(criteriaQuery).getResultList();
}

For some reason, the criteria for datetime fields are not (correctly) being applied: All the entities are being returned instead of only the items within the startTime-endTime range.

I'm guessing that I need to explicitly state that the "lastupdated" field is a datetime field; when I set startTime to Instant.MAX, I receive an error that seems to hint on a date filter instead of datetime:

Invalid value for EpochDay (valid values -365243219162 - 365241780471): 365241780838

Would anyone know how I can filter on a datetime field being between two given java Instants?

PS. I'm aware of using derived queries such as findByXGreaterThanOrEqualToAndXLessThan(Instant instant1, Instant instant2); but since there are several other criterias which I have not included, this option is not feasible.

CodePudding user response：

I think you could easily do the same using EntityManager and JPQL instead of Criteria API:

public List<SomeEntity> getItemsByLastUpdated() {
    var query = "select e from SomeEntity e
                  where e.lastupdated >= :from
                    and e.lastupdated <  :to
                    and your_other_criteria";

    return entityManager.createQuery(query)
                .setParameter("from", startTime)
                .setParameter("to", endTime)
                .getResultList();
}

CodePudding user response：

It took me quite some time to figure it out; turned out that the lack of filtering didn't have anything to do with the CriteriaBuilder setup.

I had created a test to verify the method, and prepared some entities before running the test. However, the "lastupdated" field in the entity class was annotated with @UpdateTimestamp, which would result in all the saved entities having pretty much the same 'lastupdated' value.