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

by

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).

More Related Contents:

  1. What’s the difference between JPA and Hibernate? [closed]
  2. Can someone explain mappedBy in JPA and Hibernate?
  3. Why does JPA have a @Transient annotation?
  4. JPQL Create new Object In Select Statement – avoid or embrace?
  5. How to create and handle composite primary key in JPA
  6. When to use EntityManager.find() vs EntityManager.getReference() with JPA
  7. Adding IN clause List to a JPA Query
  8. Parameter in like clause JPQL
  9. Hibernate bidirectional @ManyToOne, updating the not owning side not working
  10. No Persistence provider for EntityManager named X
  11. How to use multiple JOIN FETCH in one JPQL query
  12. MappedSuperclass – Change SequenceGenerator in Subclass
  13. Limit number of results in JPQL
  14. persistence.xml different transaction-type attributes
  15. How To Define a JPA Repository Query with a Join
  16. JPA Multiple Embedded fields
  17. Using the JPA Criteria API, can you do a fetch join that results in only one join?
  18. Doing an “IN” query with Hibernate
  19. Setting a parameter as a list for an IN expression
  20. JPQL: Receiving a Collection in a Constructor Expression
  21. How can I avoid the Warning “firstResult/maxResults specified with collection fetch; applying in memory!” when using Hibernate?
  22. Sharing a persistence unit across components in a .ear file
  23. IN-clause in HQL or Java Persistence Query Language
  24. JPA Native Query select and cast object
  25. Spring Data JPA and Exists query
  26. JPQL Like Case Insensitive
  27. PersistenceUnit vs PersistenceContext
  28. LOAD and CACHE application-scoped data with @Singleton and @Stateless
  29. JPA Select latest instance for each item
  30. JPA: difference between @JoinColumn and @PrimaryKeyJoinColumn?

Leave a Comment