IN CLAUSE using createNativeQuery

Question

How to write IN clause using createNativeQuery ?

Example

codeList = "123 ,456";

Then I get the codeList like this :

CODE IN (:codeList)

But I can't get the data. What is the correct way to write IN clause using createNativeQuery ?

Answer 1

IN clause accept a List not a String so what you should to do is to convert this String to a List like this then set the parameter

List<Integer> listCode = Stream.of(codeList.split("\\s*,\\s*"))
       .map(Integer::valueOf)
       .collect(toList());// this will return a list [123, 456]
query.setParameter("codeList", listCode);

---

Now about your problem

When you try to use :

query.setParameter("codeList", "123 ,456");

Your query is converted like this :

CODE IN ('123 ,456')
         ^________^-----------------this treated as a String not as a List

---

There are a solution with concatenate this parameter with the query, but I don't advice with this solution!