2

I have the following database model:

A aId AB aId bId B bId status

In a Spring data Specification, I want to return the instances of A when B.status is 'X'. The JPQL code is the following:

select a from A a where a in (select ab.id.a from AB ab where ab.id.b.status= :status)

These are the model classes:

@Entity public class A { private Long aId; @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "id.a") private Set<AB> ab; } @Entity public class B { private Long bId; private String Status; @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "id.b") private Set<AB> ab; } @Entity public class AB { private ABPK id; } public class ABPK { @ManyToOne @JoinColumn(name="aId") private A a; @ManyToOne @JoinColumn(name="bId") private B b; }

How would be the JPA Criteria in the Spring Specification?

public class ASpecifications { public static Specification<A> test(final String status) { return new Specification<Party>() { @Override public Predicate toPredicate(Root<A> a, CriteriaQuery<?> query, CriteriaBuilder cb) { return null; } }; } }
Improve this question

4 Answers 4

Reset to default
3

The Specification that returns instances of A using Criteria API is the following:

public class ASpecifications { public static Specification<A> test(final String status) { return new Specification<Party>() { @Override public Predicate toPredicate(Root<A> a, CriteriaQuery<?> query, CriteriaBuilder cb) { Subquery<A> sq = query.subquery(A.class); Root<AB> ab = sq.from(AB.class); sq.select(ab.get(AB_.id).get(ABPK_.a)); sq.where(cb.equal(ab.get(AB_.id).get(ABPK_.b).get(B_.status), status)); Predicate p = cb.in(a).value(sq); return cb.and(p); } }; } }
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

what is "a" here? cb.in(a).value(sq);
its the name of the Element Root<A> as you can see in the parameterlist of "toPredicate"
0

There are some good examples included on a previous post that address exactly what you're trying to accomplish here: jpa-2-0-criteria-api-subqueries-in-expressions.

Improve this answer

1 Comment

Thank you codeturner. Unfortunately, the examples in that link don't help me with the embeddedId. In my case, I don't have the mapping attribute for the subQuery. I need aId, but ABPK has A, not aId.
0

I suppose you wanted to select "A entities from AB entities where B is of provided status":

CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<A> cq = cb.createQuery(A.class); Root<AB> ab = cq.from(AB.class); cq.select(ab.get("id").get("a")); cq.where(cb.equal(ab.get("id").get("b.status"), status));
Improve this answer

2 Comments

Referring to the answer I provided with type safe. Thank you!
@Techky if this solves your problem you mentioned in the question, please accept this answer as the best answer.
0

Select In - is an option but you could achieve same result with double join. And its easier to make such a jpa specification:
SELECT A.ID FROM A LEFT JOIN AB ON A.ID = AB.A_ID LEFT JOIN B ON AB.B_ID = B.ID WHERE B.STATUS = 'STATUS'
Method would look like this:

public static Specification<A> findB(String input) { return new Specification<A>() { @Override public Predicate toPredicate(Root<A> root, CriteriaQuery<?> cq, CriteriaBuilder cb) { Join<A,AB> AjoinAB = root.joinList(A_.AB_LIST,JoinType.LEFT); Join<AB,B> ABjoinB = AjoinAB.join(AB_.B,JoinType.LEFT); return cb.equal(ABjoinB.get(B_.NAME),input); } }; }

Or shorter

public static Specification<A> findB(String input) { return (Specification<A>) (root, cq, cb) -> { Join<A,AB> AjoinAB = root.joinList(A_.AB_LIST,JoinType.LEFT); Join<AB,B> ABjoinB = AjoinAB.join(AB_.B,JoinType.LEFT); return cb.equal(ABjoinB.get(B_.NAME),input); }; }

I know it's been a long time since this question arose, but I came across it when I tried to do the same. This is my solution that works and I hope it helps someone.

Entities below

@Entity public class A { @Id private Long id; private String name; @OneToMany(mappedBy = "a") private List<AB> abList; } @Entity public class B { @Id private Long id; private String status; @OneToMany(mappedBy = "b") private List<AB> abList; } @Entity public class AB { @Id private Long id; private String name; @ManyToOne @JoinColumn(name = "a_id") private A a; @ManyToOne @JoinColumn(name = "b_id") private B b; }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.