Subquery example in hibernate 5.2 Criteria API

Question

I need to fire below query using hibernate 5.2+, but there is no such way given in hibernate official docs

SELECT * from candidate WHERE candidate.id NOT IN (SELECT candidate_id FROM interview)

N.B. I don't want to use named query or native query stuffs which makes my code more database specific.

In the image below, Black circle is Candidate, White one is Interview.

Any idea how to run this.

https://stackoverflow.com/questions/40720799/deprecated-createcriteria-method-in-hibernate-5/40720954#40720954 — Jens, Aug 03 '17 at 11:12
Hey @Jens welcome back.. but can you tell me how to use `Subqueries.propertyNotIn()` with this new JPA interface, coz, nowhere I could find one. — miiiii, Aug 03 '17 at 11:25
Read the [javadoc] (https://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/criterion/Subqueries.html#propertyNotIn(java.lang.String, org.hibernate.criterion.DetachedCriteria)) — Jens, Aug 03 '17 at 11:46
`Criteria criteria = session.createCriteria(Candidate.class).add(Subqueries.propertyNotIn("id", DetachedCriteria .forClass(Interview.class).createAlias("candidate", "c").setProjection(Property.forName("c.id")))); ` This is how it was possible before 5.2, but now `CriteriaQuery` of JPA has taken over, so no way to do the same. — miiiii, Aug 03 '17 at 12:21

score 2 · Accepted Answer · answered Nov 27 '17 at 18:33

2

Try something like this:

DetachedCriteria subquery = DetachedCriteria.forClass(Interview.class, "interview")
.setProjection(Projections.property("interview.candidate_id"));

DetachedCriteria criteria = DetachedCriteria.forClass(Candidate.class, "candidate")
.add(Subqueries.propertyNotIn("candidate.id", subquery));

Criteria executableCriteria = criteria.getExecutableCriteria(session);
List list = executableCriteria.list();

answered Nov 27 '17 at 18:33

Alex

316
2
13

Thanks Alex, I got it already. When I were in trouble with this, the docs were not updated, but later following Vlad Mihalcea's posts I got my answer. Although I forgot to update it here, I'll accept your answer as solution. Thanks Again :) !! – miiiii Nov 28 '17 at 07:13
You are welcome Manoj! By the way, can you post here link on Vlad Mihalcea's post about this topic? I wounder to see best practice from him – Alex Nov 28 '17 at 09:28

score 0 · Answer 2 · answered Aug 03 '17 at 10:29

0

You could use an exists command.

from Candidate as candidate
where exists (
    from Interview as interview where interview.candidate = candidate
)

answered Aug 03 '17 at 10:29

shalama

1,133
1
11
25

Subquery example in hibernate 5.2 Criteria API

2 Answers2