Let say I have a column in Oracle DB like this:
SOMETHING_TS TIMESTAMP WITH TIME ZONE
And I would like to use CriteriaQuery to filter by this column.
I can used native query to achieve this:
SELECT *
FROM SOMETHING
WHERE TRUNC(SOMETHING_TS) = TO_DATE('2016-12-08','YYYY-MM-DD');
But in Java I have failed to do so, below is my sample code:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<MyClass> cq = cb.createQuery(MyClass.class);
Date date = new Date();
predicates.add(cb.equal(cb.function("TRUNC", Date.class, myClass.get("somethingTs")), cb.function("TO_DATE", Date.class, cb.parameter(Date.class, "somethingTs"), cb.literal("YYYY-MM-DD"))));
Predicate[] predArray = new Predicate[predicates.size()];
predicates.toArray(predArray);
cq.where(predArray);
TypedQuery<MyClass> query = em.createQuery(cq);
query.setParameter("somethingDt", date);
Seems like I have found my answer:
predicates.add(cb.equal(cb.function("TRUNC", Date.class, myClass.get("somethingTs")), cb.function("TO_DATE", String.class, cb.parameter(String.class, "somethingTs"), cb.literal("YYYY-MM-DD"))));
Then when I set parameter, I will set the String format of date to query.
Thanks!