How can I make an iBatis parameter mapping for a query with multiple series of various paramaters?

I need to map a sql statement of this general form:

SELECT ... 
FROM x, y, z
WHERE ( x.id = #x1# OR x.id = #x2# OR ... ) 
  AND ( y.id = #y1# OR y.id = #y2# OR ... )
  AND z.name = #name#;

So I have two lists of parameters and 1 stri开发者_C百科ng. I am wondering if something can be made from perhaps iBatis dynamic SQL or something. If I just could build my own WHERE clause and give it perhaps. Because somehow I want to use iBatis for mapping the result into Java objects...

How would you do?

---

Your problem seens to be a result of the way in which you framed the query in your iBatis (or myBatis) configuration file.

Instead of a string of or conditions why not use one in condition.

Here is an example:

SELECT ... 
FROM x, y, z
WHERE ( x.id in (#xstring# ) 
  AND ( y.id in (#ystring# )
  AND z.name = #name#;

---

You can use the <iterate> tag to iterate over your parameter list. I suspect that you have 2 lists, one containing the parameter names, the other containing the parameter value. Is that the case?

If so: To build a query from that, you would have to iterate both lists simultaneously, which is not possible in iBatis. Instead, you will have to create a class containing both name and value and pass the list of it. Then you can use iBatis to iterate over that (single) list and access name and value using the "current" item from the list. Try to use this approach, I've used it successfully in the past. I'll try to find some source code for you as soon as I'm home.

EDIT: Ok, it seems you want to generate two blocks that have many OR-joined conditions. In that case you can use 2 <iterate> tags one after the other. Do you need help on how to pass multiple parameters into the query?

---

The foreach element (available only in ibatis3 - mybatis) is very powerful, and allows you to specify a collection, declare item and index variables that can be used inside the body of the element. It also allows you to specify opening and closing strings, and add a separator to place in between iterations. The element is smart in that it won’t accidentally append extra separators.