How can I make a Hibernate Criteria Query with Restriction Greater than Period based on a integer field in hours

Question

Suppose that I have the following entity:

@Entity
@Table(name="ORDER_ITEM")
public class Order {

    @Id
    @Column(name = "ORDER_ID")
    private Long id;

    @Column(name = "ORDER_VALUE")
    private BigDecimal value;

    @Temporal(TemporalType.TIMESTAMP)
    @DateTimeFormat(style = "M-")
    @Column(name = "CREATED_AT", columnDefinition = "date")
    private Date createdAt;

    @Column(name = "HOURS_MAX")
    private Integer hoursMax = 24;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public BigDecimal getValue() {
        return value;
    }

    public void setValue(BigDecimal value) {
        this.value = value;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public Integer getHoursMax() {
        return hoursMax;
    }

    public void setHoursMax(Integer hoursMax) {
        this.hoursMax = hoursMax;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", value=" + value +
                ", createdAt=" + createdAt +
                ", hoursMax=" + hoursMax +
                '}';
    }
}

So i need make a criteria query of orders limiting by createAt date field in a period maximun of hoursMax integer field. I see a lot of examples using a Date instance java minus a integer literal value in hours like example below:

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Order> cq = cb.createQuery(Order.class);
    Root<Order> orderRoot = cq.from(Order.class);

    Calendar c = Calendar.getInstance();
    c.add(Calendar.HOUR_OF_DAY, -24);

     cq.select(orderRoot).where(
            cb.greaterThan(orderRoot.get(Order_.createdAt) ,c.getTime())
     );

    TypedQuery<Order> query = em.createQuery(cq);
    List<Order> result = query.getResultList();

But, in this case i need use the field hoursMax that represents this maximun period what i should put into ???? below ?

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Order> cq = cb.createQuery(Order.class);
    Root<Order> orderRoot = cq.from(Order.class);

    cq.select(orderRoot).where(
            cb.greaterThan(orderRoot.get(Order_.createdAt) , ??? )
    );

    TypedQuery<Order> query = em.createQuery(cq);
    List<Order> result = query.getResultList();

Note: Also, I want to avoid executing a additional pre query for hoursMax.

Answer 1

Well, i find a workaround for it, however i don't know if is there a better solution because it makes the implementation dependent of database specific provider.

In this case i used a @Formula from hibernate like following examples.

Note: I'm omitting the GETTERS and SETTERS just for simplify answer.

For h2 database.

@Entity
@Table(name="ORDER_ITEM")
public class Order {

    @Id
    @Column(name = "ORDER_ID")
    private Long id;

    @Column(name = "ORDER_VALUE")
    private BigDecimal value;

    @Temporal(TemporalType.TIMESTAMP)
    @DateTimeFormat(style = "M-")
    @Column(name = "CREATED_AT", columnDefinition = "date")
    private Date createdAt;

    @Column(name = "HOURS_MAX")
    private Integer hoursMax = 24;

    @Formula("DATEADD('HOUR',HOURS_MAX *(-1), CURRENT_DATE())")
    private Date createSinceLimitHours;

}

For Oracle database is not so simple, because there is not a Oracle 11g Dialect yet : Look it for more information : https://stackoverflow.com/a/32019401/1488761

And finally just make the criteria using the new field:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Order> cq = cb.createQuery(Order.class);
Root<Order> orderRoot = cq.from(Order.class);

cq.select(orderRoot).where(
        cb.greaterThan(orderRoot.get(Order_.createdAt) , orderRoot.get(Order_.createSinceLimitHours))
);

TypedQuery<Order> query = em.createQuery(cq);
List<Order> result = query.getResultList();