inquiries about foreach tag

[K tsup]

I have a question about using foreach tag.

I am sending a list of VOs which has a variety of values for each VO.

All those values for where clause and I want to run select query with the list of VOs.

Will it be possible?

Here is my query in XML.

VO has total 310 different set of testId, testName, realId, realTypevalues.

Thank you in advance.

<select id="sampleSelectQuery" parameterType="java.util.List" resultType="mybatis.test.vo.TestVO">
    <foreach collection="list" item="item" separator=";">
    <![CDATA[
        select
            split_part(a.id, '_', 1) as "test1",
            CAST (split_part(a.id, '_', 2) as INTEGER) as "test2",
            split_part(a.id, '_', 3) as "test3",
            split_part(a.id, '_', 4) as "test4",
            a.table_name as "test5"
        from
            test_table as a
        where
              a.test_id = #{item.testId}
            and
              a.test_name = #{item.testName}
            and
              a.real_d = #{item.realId}
            and
              a.real_type = #{item.realType}
            and
            (a.tested is not null
                 OR
              a.testValue != ':::::')
     ]]>
    </foreach>
</select>

[Guy Rouillier]

I'm not sure I'm following your scenario.  Your text says you want to use the the list of VOs in the WHERE clause.  But your sample query has the foreach loop around the entire SELECT statement.  If you really want to iterate the entire SELECT statement, then you would need to UNION all those SELECT statements together.  However, looking at your query, "item" appears only in the WHERE clause.  So, I think what you mean to do is to run the SELECT statement *once*, with the WHERE clause containing tests against all the VO values.  If I got that right, then move the foreach statement from outside the SELECT statement inside the WHERE clause.  The separator should be "OR".  Put a single set of parentheses around the entire set of clauses in your WHERE clause.  Something like this:

where

<foreach collection="list" item="item" separator=" OR ">
(

a.test_id = #{item.testId}
and
a.test_name = #{item.testName}
and
a.real_d = #{item.realId}
and
a.real_type = #{item.realType}

)

</foreach>

and
(a.tested is not null
OR
a.testValue != ':::::')

--

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/6f0a4527-799a-4d1b-81d6-ca907e839a8eo%40googlegroups.com.