How to native query WHERE IN by using a Many to Many field?

Question

EXPECTED

Single native query to obtain all the pets of a certain caretaker by using the Many to Many field.

I'm aware that I could simply obtain the pet list from the caretaker entity, the thing is I'm going to apply other filters as well by using the AND keyword, so I need to have the WHERE IN working to begin with.

BRIEF SAMPLE

public class Caretaker {

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "caretaker_pet",
        joinColumns = @JoinColumn(name = "caretaker_id"),
        inverseJoinColumns = @JoinColumn(name = "pet_id"))
    private List<Pet> pets;
}

public class Pet {

    @ManyToMany // skipped for clarity
    private List<Caretaker> caretakers;
}

PROBLEM

The following native query is throwing:

java.sql.SQLException: unknown escape sequence {non-qualified-property-ref} org.hibernate.exception.GenericJDBCException: could not prepare statement

public List<Pet> getPetsBy(Caretaker caretaker) {
    try {
        Query query = entityManager.createQuery(
                    "FROM Pet WHERE :caretaker IN (caretakers)")
                    .setParameter("caretaker", caretaker);

        return query.getResultList();
    }
    catch (Exception exception) {
        exception.printStackTrace();
    }
}

Answer 1

You could use member of keyword to express it:

public List<Pet> getPetsBy(Caretaker caretaker) {
    try {
        Query query = entityManager.createQuery(
                    "SELECT p FROM Pet p WHERE :caretaker member of p.caretakers")
                    .setParameter("caretaker", caretaker);

        return query.getResultList();
    }
    catch (Exception exception) {
        exception.printStackTrace();
    }
}