You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Collin Peters <ca...@gmail.com> on 2007/03/14 01:16:58 UTC
update using iterate, a collection, and a SQL IN clause
Hi all,
I am wondering if it is possible to construct a SQL update using an
iterate tag inside an update tag, and sending a collection (i.e. a
java.util.List) as a parameter
I have tried this and it seems to work (I can't get log4j to work in
ibatis so I can't see the SQL it is running, but the flag column does
get updated)
<update id="clearFlaggedUsers" parameterClass="List">
UPDATE users SET
flag = false
WHERE
<iterate property="" conjunction=",">
user_id IN (#[]#)
</iterate>
</update
So now I am trying to figure out the syntax for sending in a List of
objects, instead of a list of Integers.
<update id="clearFlaggedUsers" parameterClass="userVO">
UPDATE users SET
flag = false
WHERE
<iterate property="?????" conjunction=",">
user_id IN (#????[].user_id#)
</iterate>
</update
Am I on the right track? What do I put in for the question marks?
To ask this another way... If I have a java.util.List of UserVO
(pojos), and the UserVO has a variable 'user_id', and in the end I
want the following SQL to run:
"UPDATE users SET flag = false WHERE user_id IN (1,2,3)". So in this
example I would have a list of 3 UserVO's, the user_ids in the 3 VOs
would be 1,2, and 3. I want to run one update statement, not three.
How do I do that?
Re: update using iterate, a collection, and a SQL IN clause
Posted by Jeff Butler <je...@gmail.com>.
Assuming there's a getUser_id() method...
<update id="clearFlaggedUsers" parameterClass="userVO">
UPDATE users SET flag = false
<iterate prepend="where" property="user_id" open="user_id in("
conjunction="," close=")">
#user_id[]#
</iterate>
</update
Jeff Butler
On 3/13/07, Collin Peters <ca...@gmail.com> wrote:
>
> Hi all,
>
> I am wondering if it is possible to construct a SQL update using an
> iterate tag inside an update tag, and sending a collection (i.e. a
> java.util.List) as a parameter
>
> I have tried this and it seems to work (I can't get log4j to work in
> ibatis so I can't see the SQL it is running, but the flag column does
> get updated)
> <update id="clearFlaggedUsers" parameterClass="List">
> UPDATE users SET
> flag = false
> WHERE
> <iterate property="" conjunction=",">
> user_id IN (#[]#)
> </iterate>
> </update
>
>
> So now I am trying to figure out the syntax for sending in a List of
> objects, instead of a list of Integers.
>
> <update id="clearFlaggedUsers" parameterClass="userVO">
> UPDATE users SET
> flag = false
> WHERE
> <iterate property="?????" conjunction=",">
> user_id IN (#????[].user_id#)
> </iterate>
> </update
>
> Am I on the right track? What do I put in for the question marks?
>
> To ask this another way... If I have a java.util.List of UserVO
> (pojos), and the UserVO has a variable 'user_id', and in the end I
> want the following SQL to run:
> "UPDATE users SET flag = false WHERE user_id IN (1,2,3)". So in this
> example I would have a list of 3 UserVO's, the user_ids in the 3 VOs
> would be 1,2, and 3. I want to run one update statement, not three.
>
> How do I do that?
>