CODEWITHSUNDEEP
jpacriteria-api
user2751824
user2751824

Reputation: 21

JPA + Criteria + date handling

select * from xyz where  to_char(sdateAndTime,'DD-MM-YYYY')=(select max( to_char(sdateAndTime,'DD-MM-YYYY')) as dt from xyz))

sdateAndTime is timestamp field in oracle db. i am interested to fetch record for that given date and not in hh:mm:ss:zzzz AM/PM.

Trying to build criteria for it but :(

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<XYZ> criteria = builder.createQuery(XYZ.class);
Root<XYZ> root = criteria.from(XYZ.class);
criteria.select(root);
Subquery<XYZ> sub = criteria.subquery(XYZ.class);
Root subRoot = sub.from(XYZ.class);
sub.select(builder.max(subRoot.get("sdateAndTime")));
criteria.select(root).where(builder.in(root.get("sdateAndTime")).value(sub);
entityManager.createQuery(criteria);

i was able to reach extract max of dd:hh:mm:ss however unable to get equivalent of to_char(sdateAndTime,'DD-MM-YYYY')

inputs would be appreciated. Thanks

Upvotes: 1

Views: 2983

Answers (2)

user2751824
user2751824

Reputation: 21

Thanks James for your inputs...!!

I modified above criteria query with below changes to get desired result.

Subquery<String> sub = criteria.subquery(String.class);
        Root subRoot = sub.from(XYZ.class);
        sub.select(builder.substring(builder.greatest(builder.function("TO_CHAR", String.class, subRoot.get("sdateAndTime"))),0,10));
        criteria.select(root).where(
                builder.equal(builder.substring(builder.function("TO_CHAR", String.class, root.get("sdateAndTime")),0,10),sub)

Note: I was unable to pass DD-MM-YYYY as parameter, thereby as workaround used substring. Better soln would be appreciated.

Upvotes: 1

James
James

Reputation: 18389

You can use the function() API to call a database function, such as "TO_CHAR".

https://en.wikibooks.org/wiki/Java_Persistence/Criteria#Special_Operations

Upvotes: 2

Related Questions