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

Question

Hi all, I'm trying to set a parameter in my query, for example:

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

And when I'm adding the parameter in my JavaClass is like:

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

And this is not working, is only working with only one parameter.
I've tried with "'RED','BLUE'" and is not working to.

If I put my parameters in the query is working for example: select * from Cars where Cars.color NOT IN ('RED','BLUE')

What I'm doing wrong!?

Thanks in advance

Answer 1

You must pass in a list of strings, not a single string. JPA doesn't parse your values, you must split them yourself.

Answer 2

You are supposed to pass a 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 could also do:

query.setParameter("color_params", colors);

---

As a general rule, it is often prefered to pass parameters to a fixed query, instead of customizing the String. The advantages could be:

1. Reduced parsing: JPA implementation (at least Hibernate) have a hard work parsing each query. So the parsed query goes into a cache, to be reused. If the query string is build at runtime from parameters, it might never be twice the same, so a lot of time, computing power and cache memory are lost. But if you use the same query string with different parameters, bingo : fast, low memory use, low cpu requirement.
2. Prevent SQL injection. This guarantee is offered if you use parameters. If you build your query string with the parameters, you have to provide yourself this guarantee ...!