Milkmaid Milkmaid - 2 years ago 69
Java Question

How to check in JPA if joined column contains data with condition

I have two tables.

Person and his vacation plannings. For this question everything we need to know is that Person contains Id and vp contains this id as a foreign key, and year and start date end date. Person can contains more vp.

Person VacPlan
_______ ____________
|id |----->|pers_id |
|year |
|start date |
|end date |

I need to select every person who don't have a single record in vp for specified year.

Something like:
select from person where (joined vp where vp.year = :year) is not empty

Do you have any ideas how to make this in JPA(eclipse link)

this is how criteria looks like

List<Predicate> predicates = new ArrayList<>();
List<Predicate> subPredicates = new ArrayList<>();
CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<PersonVP> q = cb.createQuery(PersonVP.class);
Root<PersonVP> person = q.from(PersonVP.class);

Subquery<VacationPlanning> subquery = q.subquery(VacationPlanning.class);
Root<VacationPlanning> subRoot = subquery.from(VacationPlanning.class);

Expression<String> exp = person.get("personnelNumber");
subPredicates.add(cb.equal(subRoot.get("year"), year));
subPredicates.add(cb.equal(exp, subRoot.get("perNr")));
subquery.where(cb.and(subPredicates.toArray(new Predicate[subPredicates.size()])));

if (STATUS.FILLED.equals(searchFillStatus)) {
} else if (STATUS.NOT_FILLED.equals(searchFillStatus)) {

q.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));;
TypedQuery<PersonVP> query = getEntityManager().createQuery(q);

return query.setFirstResult(from)

Answer Source

Something like this should work:

select p from Person p where not exists(
    select from Person p2 
    join p2.vacPlans vp
    where =
    and vp.year = :year)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download