Simple Question: I have a (MySQL) table with two date columns (from, until) and ask it with
select * from mytable where until > from + interval 4 week;
This is really simple in MySQL.
How could you do that in JPA Query like
cq.where(cb.and(mytable_.get(until),...)
EDIT: Both dates come from the database row, I compare two fields of the database and not one field from application with one from database.
Here is the Explanation of Equivalent JPA Criteria Query of
select * from mytable where until > from + interval 4 week;
First you have to create unit expression and extend it from BasicFunctionExpression for which take "WEEK" parameter as a unit and override its rendor(RenderingContext renderingContext) method only.
import java.io.Serializable;
import org.hibernate.query.criteria.internal.CriteriaBuilderImpl;
import org.hibernate.query.criteria.internal.compile.RenderingContext;
import org.hibernate.query.criteria.internal.expression.function.BasicFunctionExpression;
public class UnitExpression extends BasicFunctionExpression<String> implements Serializable {
public UnitExpression(CriteriaBuilderImpl criteriaBuilder, Class<String> javaType,
String functionName) {
super(criteriaBuilder, javaType, functionName);
}
@Override
public String render(RenderingContext renderingContext) {
return getFunctionName();
}
}
then you use this unit expression in your JPA criteria Query.
CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<MyTable> cq = cb.createQuery(MyTable.class);
Root<MyTable> root = cq.from(MyTable.class);
Expression<String> week= new UnitExpression(null, String.class, "WEEK");
Expression<Integer> timeDiff = cb.function(
"TIMESTAMPDIFF",
Integer.class,
week,
root.<Timestamp>get(MyTable_.until),
root.<Timestamp>get(MyTable_.from));
List<Predicate> conditions = new ArrayList<>();
conditions.add(cb.greaterThan(timeDiff, 4));
cq.where(conditions.toArray(new Predicate[]{}));
return session.createQuery(cq);
It is working fine.
EDIT2: workaround found
Since we have to work only with functions that don't need a built-in parameter (like WEEK), I ended up with
cb.greaterThan(
cb.diff(
cb.function("unix_timestamp", Long.class, root.get(Table_.until)),
cb.function("unix_timestamp", Long.class, root.get(Table_.from))
)
, 3600L*longerThanHours)
For reference a version that leads to a dead end:
There is no way to make it work like this, you can not send "hour" without surrounding "" as a parameter to the database.
CriteriaBuilder cb = ...;
CriteriaQuery<MyTable> cq = cb.createQuery(MyTable.class);
Root<MyTable> mytable = cq.from(MyTable.class);
cb.greaterThan(
cb.function(
"timestampdiff"
, Integer.class
, WEEK // <-- this is where JPA gets unable to create SQL
, mytable.get(MyTable_.from)
, mytable.get(MyTable_.until)
)
, 4
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With