11

How can I use IN at my namedQuery?

@NamedQueries(  
 {   
 @NamedQuery(name = "GetAvailableProducts", query = new StringBuilder("").append("SELECT   p   FROM Product p WHERE p.type= :type AND (p.available IN ('I want to define changeable size of an array or sometinhg like that') OR p.available = :available)")),  
 }

I mean that I can set 'type' parameter (I defined it as a variable----> :type) and I want to define variables inside of IN statement to. However the number of parameters are not constant. I want to define an array or something like that :array[] and I want to set it when I call that namedQuery.

kamaci
  • 72,915
  • 69
  • 228
  • 366

3 Answers3

29

NamedQuery

@NamedQueries(  
{   
 @NamedQuery(name = "GetAvailableProducts", query = "FROM Product p WHERE p.type= :type AND (p.available IN (:availableCollection) OR p.available = :available)",  
}

Set parameters

Hibernate:

query.setParameterList('availableCollection', yourCollection);

JPA:

query.setParameter('availableCollection', yourCollection);
Jacob van Lingen
  • 8,989
  • 7
  • 48
  • 78
Tadeusz Kopec for Ukraine
  • 12,283
  • 6
  • 56
  • 83
  • My query variable is a "Query" type variable. It has setParameter() method but I couldn't see setParameterList() method? Link: http://download.oracle.com/javaee/5/api/javax/persistence/Query.html – kamaci Dec 20 '10 at 13:28
  • import javax.persistence.Query; //I use this import for my query variable. – kamaci Dec 20 '10 at 13:30
  • I used Hibernate API. JPA query indeed does not have `setParameterList` method. According to http://www.roseindia.net/jpa/eclipsejpaexamples/named-parameter-list.shtml you just use `setParameter` and pass a collection. – Tadeusz Kopec for Ukraine Dec 20 '10 at 13:54
  • I can confirm that using JPA, query.setParameter does work for a list. – Software Prophets Oct 11 '16 at 12:08
0

Did you try something like

SELECT   p   FROM Product p WHERE p.type= :type AND (p.available IN 
('foo', 'bar') OR p.available = :available)

(if "available" is a string)

or 

SELECT   p   FROM Product p WHERE p.type= :type AND (p.available IN 
(1, 2, 3) OR p.available = :available)

(if available is a number)?

AlexR
  • 114,158
  • 16
  • 130
  • 208
  • In my program, number of variables will be changeable it maybe (1, 2) or (1, 2, 3) it depends. So I should define array like variable for it. – kamaci Dec 20 '10 at 12:53
0

Expression 'IN' in JPQL is equal to writing multiple 'OR' statements, in your case an example of 'IN' would be:

... p.available IN ('US', 'GB') ...

Which I believe is equal to:

... p.available = 'US' OR p.available = 'GB' ...

Here's a link that explains it well: OpenJPA: IN Expression

EDIT P.S. Assuming p.available is String (character type)

EDIT2 The author has edited question so many times that I can't follow up him :) As for the actual question about passing array for IN-expression, here's a link of similar question on StackOverflow: JPQL IN clause - Arrays

Community
  • 1
  • 1
Art Licis
  • 3,619
  • 1
  • 29
  • 49
  • I'm sorry :) but I wanted to make more clear for everybody according to comments so I edittes it to make my question clear :) – kamaci Dec 20 '10 at 13:24