I have a sql query of this kind of structure:
......
......
where a.id=#{x} AND b.id!=#{x}
......
Now I want to change this query to accept a list of values of x. How should I write foreach loop for this?
1 Answer
I would change your WHERE to the form
SELECT ... FROM ... WHERE a.id IN (...x...) AND b.id NOT IN (...x...)
Then, your annotation or XML would contain:
SELECT ...
FROM ...
WHERE a.id IN <foreach item="item" index="index" collection="x"
open="(" separator="," close=")">
#{item}
</foreach>
AND b.id NOT IN <foreach item="item" index="index" collection="x"
open="(" separator="," close=")">
#{item}
</foreach>
This will produce more efficient SQL.
