Spring Data JPA : How to write subquery having IN operator

Question

I am a new to Spring Data JPA, wanted to know how do I write the following subquery:

select o from Owner o where o.ownerId IN (Select c.ownerId from Cars c)

Here Owner is one entity class and Cars is another entity class and I'll be having two repositories one as OwnerRepository and the other as CarRepository, both extending JPARepository.

Help needed in writing this sort of custom queries with IN operator.

Thanks in advance.

Please see http://stackoverflow.com/questions/21826773/querydsl-subquery-in-collection-expression — shankarsh15, May 20 '16 at 15:55

score 7 · Answer 1 · edited Apr 15 '21 at 13:47

7

You could always just use your query as a custom query:

@Query(value = "select o from Owner o where o.ownerId IN (Select c.ownerId from Cars c")
Owner getOwner();

If you need to pass variables to the query, use the @Param tag, like so:

@Query(value = "SELECT * FROM Owner o WHERE o.ownerId = :id")
Owner getOwnerWithId(@Param("id") Long id);

edited Apr 15 '21 at 13:47

Islam Hassan

673
1
10
21

answered May 22 '16 at 01:01

randyr

1,679
1
11
17

Also take a look at [this](http://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-three-custom-queries-with-query-methods/) – randyr May 22 '16 at 01:02
As long as your examples use JPQL `nativeQuery = true` should be omitted. – rilaby May 04 '20 at 09:55

Spring Data JPA : How to write subquery having IN operator

1 Answers1