-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: JPA 2.0 Criteria: Find most recent state change
PostPosted: Tue May 31, 2011 6:10 am 
Newbie

Joined: Fri Nov 30, 2007 6:37 am
Posts: 3
Hi,

I have this entity:

Code:
StateChange{
    Date date
    String state;
}


How can I find the most recent state change (= the current state) using JPA 2.0 Criteria?

I think I would need to sort by date and limit 1, but how? Or do I need "having"?

To further complicate things I really need to query Items with id == statechange.item_id?

Thanks!


Top
 Profile  
 
 Post subject: Re: JPA 2.0 Criteria: Find most recent state change
PostPosted: Tue May 31, 2011 9:34 am 
Newbie

Joined: Wed May 04, 2011 2:24 am
Posts: 10
It would be easiest to sort and get the first record. The JPA query would look something like this.

Code:
//Obtain entity manager
EntityManager em = ...

//Required item ID
int id = ...

CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<StateChange> query = builder.createQuery(StateChange.class);
Root<StateChange> root = query.from(StateChange.class);
query.where(builder.isNotNull(root.get("date")), builder.equal(root.get("itemId"), id));
query.select(root);
query.orderBy(builder.desc(root.get("date")));

StateChange p = em.createQuery(query).setMaxResults(1).getSingleResult();


Another approach is selecting the maximum date, then selecting the entity that matches this date.
Code:
//Obtain entity manager
EntityManager em = ...

//Required item ID.
int id = ...;

CriteriaBuilder builder = em.getCriteriaBuilder();
EntityType<StateChange> type = em.getMetamodel().entity(StateChange.class);

//Select the max value of date from the StateChange entity
//where the ID matches the required ID and the date is not null (ANSI requirement, prevent aggregating null values).
CriteriaQuery<Date> query1 = builder.createQuery(Date.class);
Root<StateChange> root1 = query1.from(StateChange.class);
Expression<Date> maxDateExpression = builder.greatest(root1.get(type.getDeclaredSingularAttribute("date", Date.class)));
Expression<Date> thisDateExpression = root1.get("date");
query1.select(maxDateExpression);
query1.where(builder.isNotNull(thisDateExpression), builder.equal(root1.get("itemId"), id));

Date maxDate = em.createQuery(query1).getSingleResult();

//We have found the highest date for the provided ID. Select the StateChange that matches the maximum date.
CriteriaQuery<StateChange> query2 = builder.createQuery(StateChange.class);
Root<StateChange> root2 = query2.from(StateChange.class);
query2.where(builder.equal(root2.get("date"), maxDate), builder.equal(root2.get("itemId"), id));
query2.select(root2);

StateChange p = em.createQuery(query2).setMaxResults(1).getSingleResult();

Wouldn't know how to put this in one query though.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.