JPA setParameter when working with "NOT IN (: param)"

Question

I am trying to set a parameter in my request, for example:

select * from Cars where Cars.color NOT IN (:color_params) 

And when I add the parameter to my JavaClass, it looks like:

 ... query.setParameter("color_params", "RED,BLUE"); ... 

And this does not work, it works with only one parameter .
I tried with "'RED','BLUE'" and does not work.

If I put my parameters in a query, for example:

 select * from Cars where Cars.color NOT IN ('RED','BLUE') 

What am I doing wrong?

Thanks in advance

Answer 1

You must pass the list.

 List<String> colors = ....; String query = "select * from Cars where Cars.color NOT IN (:color_params)"; Map<String, Object> params = new HashMap<String, Object>(); params.put("color_params", colors); // ... execute the query with the param. 

You can also do:

 query.setParameter("color_params", colors); 

---

As a rule, they often prefer to pass parameters to a fixed request rather than tune a string. Benefits may include:

• Reduced parsing . The JPA implementation (at least Hibernate) has a strong job of parsing each request. Thus, the parsed request goes into the cache so that it can be reused. If a query string is created at runtime from parameters, it may never be twice the same, so a lot of time, processing power and cache are lost. But if you use the same query string with different parameters, bingo: fast, low memory, low CPU requirement.
• Prohibit SQL injection . This warranty is provided if you use the options. If you build a query string with parameters, you must give yourself this guarantee ...!

Answer 2

You should pass a list of lines, not a single line. JPA does not analyze your values, you must separate them yourself.