CODEWITHSUNDEEP
javaspringhibernatejpamany-to-many
Jsh0s
Jsh0s

Reputation: 599

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

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();
    }
}

Upvotes: 0

Views: 71

Answers (2)

Oleksii Valuiskyi
Oleksii Valuiskyi

Reputation: 2841

You should try JPQL query like this 

select distinct p 
from Pet p join fetch p.caretakers caretaker
where caretaker=:caretaker

It returns pet with fetched caretakers. If you don't need to load caretakers - member of can be used (see another answer)

Upvotes: 0

Anthony
Anthony

Reputation: 621

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();
    }
}

Upvotes: 1

Related Questions