Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 483
Julio Faerman Avatar asked May 06 '11 18:05

Julio Faerman


People also ask

Can we use inner join in JPQL?

[INNER] JOIN JPQL provides an additional type of identification variable, a join variable, which represent a more limited iteration over specified collections of objects. In JPQL, JOIN can only appear in a FROM clause. The INNER keyword is optional (i.e. INNER JOIN is equivalent to JOIN).

What is in JPQL?

JPQL is Java Persistence Query Language defined in JPA specification. It is used to create queries against entities to store in a relational database. JPQL is developed based on SQL syntax.

What is the difference between JPQL and Hql?

The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.


2 Answers

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

like image 124
Mikko Maunu Avatar answered Sep 24 '22 00:09

Mikko Maunu


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.

like image 31
Michael Borgwardt Avatar answered Sep 24 '22 00:09

Michael Borgwardt