CODEWITHSUNDEEP
javahibernatejpaejbcriteria-api
Yablargo
Yablargo

Reputation: 3596

JPA 2.0 CriteriaBuilder help - How do I select the Greatest(max) value that matches a certain where query?

Sorry for this rather basic question, but I have to get some sort of prototype working very quickly and this is my first foray into JPA.

I have a class, System that has a List of Snapshot items, each has a numeric ID, and a SystemID.

How do I query Snapshots to say something like:

select top 1 ID from Snapshots
where Snapshots.SystemID = X 
order by Snapshots.ID desc;

I know how to put the where query in, not sure where to put my "greatest" bit.

Thanks!!

public Snapshot GetMostRecentSnapshotByID(int systemID) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<mynamespace.Snapshot> criteria = 
            cb.createQuery(mynamespace.Snapshot.class);
    Root<mynamespace> snapshot = criteria.from(mynamespace.Snapshot.class);
    criteria.where(cb.equal(snapshot.get(Snapshot_.systemID), systemID));

    //OK -- where does this guy go?
    cb.greatest(snapshot.get(Snapshot_.id));

    return JPAResultHelper.getSingleResultOrNull(em.createQuery(criteria));
}

Clarification: I have the following (snippet) of my snapshot class @

Entity
public class Snapshot implements Serializable {



    @Id
    @GeneratedValue
    private int id;

    @ManyToOne
    @JoinColumn(name = "systemID", nullable = false)
    private System system;

Can I query against a numerical id, vs using a System object, to find a particular System's snapshots?

Sorry if that was confusing!

Upvotes: 2

Views: 9361

Answers (1)

guido
guido

Reputation: 19224

You are a bit confused about jpa working with entities and properties instead of tables and columns; if you are learning I suggest you to first try to implement your query using jpql, something like:

String q = "from Snapshot s where s.systemID = :systemID order by s.id desc";
TypedQuery<Snapshot> query = em.createTypedQuery(q, Snapshot.class);
query.setParameter("systemID", systemID);
return query.getFirstResult();
// return a Snapshot object, get the id with the getter

(it would have been better to map (@OneToMany) Snapshot to System entity instead of using primitive ID)

then you could make a try with CriteriaBuilder (not using metamodel here):

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object> cq = cb.createQuery();
Root<Snapshot> r = cq.from(Snapshot.class);
cq.where(cb.equal(r.get("systemID"), systemID));
cd.orderBy(cb.desc(r.get("id")));
em.createQuery(cq).geFirsttResult();

if you wanted to make a where...and... (but it's not your case in this question), it would have been:

[...]
Predicate p1 = cb.equal(r.get("systemID"), systemID));
Predicate p2 = cb. /* other predicate */
cb.and(p1,p2);
[...]

EDIT:

Can I query against a numerical id, vs using a System object, to find a particular System's snapshots?

Sure, you can do it like that (given that System has an @Id property named id):

String q = "from Snapshot s where s.system.id = :systemID order by s.id desc";
[...]

where s.system.id means: property id (integer) of the property system (class System) of s (Snapshot).

Or, if you had the System entity, you could compare directly the objects:

String q = "from Snapshot s where s.system = :system order by s.id desc";
query.setParameter("system", system);
[...]

Using CriteriaBuilder (and metamodel):

Metamodel m = em.getMetamodel();
Root<Snapshot> snapshot = cq.from(Snapshot.class);
Join<Snapshot, System> system = snapshot.join(Snapshot_.system);
cq.where(cb.equal(System_.id, systemID));
[...]

Upvotes: 2

Related Questions