Can I use the SQL IN (...) statement for namedQuery?

Question

How can I use IN in 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, I can set the parameter to "type" (I defined it as a variable ---->: type), and I want to define the variables inside the IN statement. However, the number of parameters is not constant. I want to define an array or something like this: array [], and I want to set it when I call this namedQuery.

Answer 1

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

and then in the code for sleep mode:

 query.setParameterList('availableCollection', yourCollection); 

EDIT
In jpa you write

 query.setParameter('availableCollection', yourCollection); 

and according to this it should work.

Answer 2

The expression "IN" in JPQL is equal to writing several "OR" statements, in your case, an example of "IN" would be:

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

Which I consider equal:

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

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

EDIT Postscript Assuming p.available is a string (character type)

EDIT2 The author has edited the question so many times that I can’t trace it :) Regarding the actual issue of passing an array to an IN expression, here is a link to a similar question on StackOverflow: JPQL IN clause - Arrays

Answer 3

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 a number is available)?