LocalDate between using JPA 2.1 Criteria API

Question

In my entity I have two fields :

private LocalDate startDate = LocalDate.of(1900, 1, 1);
private LocalDate endDate = LocalDate.of(3000, 1, 1);

Using JPA Criteria API I want to select entities where LocalDate.now() > startDate and LocalDate.now() < endDate.

I tried as following :

predicates.add(builder.greaterThan(LocalDate.now(), path.<LocalDate> get(Entity_.startDate)));
predicates.add(builder.lessThan(builder.currentDate(), path.<LocalDate> get(Entity_.endDate)));

But I get this error :

The method greaterThan(Expression<? extends Y>, Expression<? extends Y>) in the type CriteriaBuilder is not applicable for the arguments (LocalDate, Path<LocalDate>)

I tried this too :

predicates.add(builder.between(builder.currentDate(), path.<LocalDate> get(Entity_.startDate), path.<LocalDate> get(Entity_.endDate)));

Where I get the following error :

The method between(Expression<? extends Y>, Expression<? extends Y>, Expression<? extends Y>) in the type CriteriaBuilder is not applicable for the arguments (Expression<Date>, Path<LocalDate>, Path<LocalDate>)

How can I solve this ?

Not 100% sure, but you should probably use the method `greaterThan(Expression extends Y> x, Y y)` where the expession comes first and the normal variable second. So you probably need to change the order of your arguments and have `LocalDate.now()` as the second parameter. Same for the `lessThan` method and others. — OH GOD SPIDERS, Dec 21 '17 at 16:09

pirho · Answer 1 · 2017-12-25T15:01:46.040

It seems that you need an AttributeConverter since JPA 2.1 does not yet support LocalDate directly. Assuming you have an Entity like

@Entity
@Getter
public class LocalDateEntity {
   @Id
   @GeneratedValue
   private Long id;
   @Setter
   private LocalDate startDate = LocalDate.of(1900, 1, 1);
   @Setter
   private LocalDate endDate = LocalDate.of(3000, 1, 1);
}

you can use AttributeConverter like

// import java.sql.Date not java.util.Date;
@Converter(autoApply = true) // this makes it to apply anywhere there is a need
public class LocalDateConverter implements AttributeConverter<LocalDate, Date> {

   @Override
   public Date convertToDatabaseColumn(LocalDate date) {
      return Date.valueOf(date);
   }

   @Override
   public LocalDate convertToEntityAttribute(Date value) {      
      return value.toLocalDate();
   }
}

after that it is possible to make CriteriaQuery like

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<LocalDateEntity> cq = cb.createQuery(LocalDateEntity.class);
Root<LocalDateEntity> from = cq.from(LocalDateEntity.class);
Expression<Date> expCurrDate = cb.currentDate();
cq.where(
      cb.and(
            cb.greaterThan(from.get("endDate"), expCurrDate)
            ,cb.lessThan(from.get("startDate"), expCurrDate)
           // OR for example
           // cb.lessThan(expCurrDate, from.get("endDate"))
           // ,cb.greaterThan(expCurrDate, from.get("startDate"))
           // both are expressions no matter in what order
           // but note the change in Predicate lt vs. gt
      )
);
TypedQuery<LocalDateEntity> tq = em.createQuery(cq);

NOTE: while Predicate between(..) will also work but a bit different. It includes the starting & ending dates.

score 1 · Answer 2 · answered Oct 20 '21 at 20:32

In case anyone has this issue,
Like @OH GOD SPIDERS commented, the order of the parameters is wrong in builder.greaterThan(LocalDate.now(), path.<LocalDate>get(Entity_.startDate)).
greaterThan and lessThan expect the expression first and the value (or another expression) second.

predicates.add(builder.lessThan(path.<LocalDate>get(Entity_.startDate), LocalDate.now()));
predicates.add(builder.greaterThan(path.<LocalDate> get(Entity_.endDate), LocalDate.now()));

will do the trick.
If you think that decreases readability of your code because of the switched inequality, you also have the option to wrap LocalDate.now in builder.literal():

predicates.add(builder.greaterThan(builder.literal(LocalDate.now()), path.<LocalDate> get(Entity_.startDate)));
predicates.add(builder.lessThan(builder.literal(LocalDate.now()), path.<LocalDate> get(Entity_.endDate)));

Since LocalDate.now() and builder.currentDate() might not return the same date for any number of reasons, it might also be interesting to use Expression typecasting:

predicates.add(builder.greaterThan(builder.currentDate().as(LocalDate.class), path.<LocalDate> get(Entity_.startDate)));
predicates.add(builder.lessThan(builder.currentDate().as(LocalDate.class), path.<LocalDate> get(Entity_.endDate)));

And, like @pirho noted, using between will give you off-by-one day errors.

LocalDate between using JPA 2.1 Criteria API

2 Answers2

Linked