What's the difference between the IN and MEMBER OF JPQL operators?

Question

What's the difference between the IN and MEMBER OF JPQL operators?

Solution 1

IN tests is value of single valued path expression (persistent attribute of your entity) in values you provided to query (or fetched via subquery).

MEMBER OF tests is value you provided to query (or defined with expression) member of values in some collection in your entity.

Lets's use following example entity:

@Entity
public class EntityA {
    private @Id Integer id;
    private Integer someValue;
    @ElementCollection
    List<Integer> listOfValues;

    public EntityA() { }

    public EntityA(Integer id, Integer someValue, List<Integer> listOfValues) {
        this.id = id;
        this.someValue = someValue;
        this.listOfValues = listOfValues;
    }
}

And following test data:

EntityA a1 = new EntityA(1, 1, Arrays.asList(4, 5, 6));
EntityA a2 = new EntityA(2, 2, Arrays.asList(7, 8, 9));

With following query we get a1 as result, because it's someValue is one of the (0,1,3). Using literals in query (SELECT a FROM EntityA a WHERE a.someValue IN (0, 1, 3)) produces same result.

TypedQuery<EntityA> queryIn = em.createQuery(
    "SELECT a FROM EntityA a WHERE a.someValue IN :values", EntityA.class);
queryIn.setParameter("values", Arrays.asList(0, 1, 3));
List<EntityA> resultIn = queryIn.getResultList();

With following query we get a2 as result, because 7 is one of the values in listOfValues:

TypedQuery<EntityA> queryMemberOf = em.createQuery(
    "SELECT a FROM EntityA a WHERE :value MEMBER OF a.listOfValues", EntityA.class);
queryMemberOf.setParameter("value", 7);
List<EntityA> resultMemberOf = queryMemberOf.getResultList();

This functionality (including collection as parameter) is defined in JPA 2.0 specification and is not specific to Hibernate (above code works for example with EclipseLink).

Solution 2

IN tests whether a value is one of an explicit fixed list of literals or query parameters.

MEMBER OF tests whether a value is present in a JPA collection, i.e. a collection that is actually part of the object model.

Comments

[Julio Faerman]

I don't know if it's a hibernate specific thing, but IN works with collections here... but member of seems more appropriate anyway.

[Alkanshel]

using JPA with Hibernate as a provider, using IN didn't work. Had to use MEMBER OF.

[TomasZ.]

Just a note for people using an older version of Hibernate like me (with List<Long>), this might not work,because hibernate had a bug hibernate.atlassian.net/browse/HHH-5209