CODEWITHSUNDEEP
jakarta-eejpaentitycriteria
Gadi
Gadi

Reputation: 1629

JPA Criteria join


I have two entity classes with many to one relation. I need to select all the parent records that has a child record with a min value smaller than provided.

@Entity
class Parent; {
String source;
}

@Entity
class child {
@ManyToOne Parent parent;
int value;
}

What I need is to select all the Parent classes where Parent.source = X and min(Child.value> < Y. How to add the join on the parent/child entity and the specific select criteria?

CriteriaBuilder cb = context.em().getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Parent.class);
Root r = cq.from(Parent.class);
Predicate p = cb.conjunction();
p = cb.and(p, cb.equal(r.get("source"), X));
Expression y = cb.literal(Y);
// what to do with y?
cq.where(p);
TypedQuery tq = context.em().createQuery(cq);
tq.setFirstResult(0);
tq.setMaxResults(100);
return tq.getResultList();

Upvotes: 1

Views: 3278

Answers (1)

James
James

Reputation: 18379

Might be easier if the parent has a child OneToMany back. But with your current model it should be something like,

CriteriaBuilder cb = context.em().getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Parent.class);
Root r = cq.from(Parent.class);
Root child = cq.from(Child.class);
Predicate p = cb.and(cb.equal(r.get("source"), X), cb.equal(child.get("parent"), r));
Predicate p = cb.and(p, cb.lt(child.get("value"), Y)));
cq.where(p);
TypedQuery tq = context.em().createQuery(cq);
tq.setFirstResult(0);
tq.setMaxResults(100);
return tq.getResultList();

That would be any child has a value less than the value. For all of, you would need to do a sub query.

Upvotes: 3

Related Questions