CODEWITHSUNDEEP
javaspringjpaspring-dataspring-data-jpa
Johnny
Johnny

Reputation: 1393

spring data jpa query value in a Set

I have an entity class A which has a Set of entities of class B with a ManyToMany relationship (out of scope why I need this)

class A {

    @ManyToMany(cascade = CascadeType.ALL)
    Set<B> setOfB;
}

Now, given an object of class B, how can I retrieve the object(s) of class A which has the B object in its Set??

I have tried in my class A repository with this:

interface Arepository extends JpaRepository<A, Long> {

    @Query("from A a where ?1 in a.setOfB")
    List<A> findByB(B b)
}

But it gives me a SQLGrammarException, so which is the correct syntax?

Thank you for your help.

Upvotes: 6

Views: 15708

Answers (2)

VB_
VB_

Reputation: 45692

Metamodel class:

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(A.class)
public class A_ {
    public static volatile SetAttribute<A, B> bSet;
}

Specification utils:

public class ASpecs {

    public static Specification<A> containsB(B b) {
        return (root, query, cb) -> {
            Expression<Set<B>> bSet = root.get(A_.bSet);
            return cb.isMember(b, bSet);
        };
    }
}

Your repository:

public interface ARepository extends JpaRepository<A, Long>, JpaSpecificationExecutor<A> {
}

Usage:

@Service
public class YourService {

    @Resource
    private ARepository repository;

    public List<A> getByB(B b) {
        return repository.findAll(ASpecs.containsB(b));
    }
}

Upvotes: 0

Predrag Maric
Predrag Maric

Reputation: 24433

Try with @Query("SELECT a from A a where ?1 member of a.setOfB").

Upvotes: 14

Related Questions