Use MyBatis batch update in sql server database

[shang liu]

Example:

<update type='User' id='batchUpdate' parameterType="java.util.List">

   <foreach item="item" collection="list" separator=";" >

        update dbo.t12_hrs_position

        <set >
             <if test="item.userName != null" >
                 user_name=#{item.userName}
             </if>

              ......

      <set>

      where id=#{id}

    </foreach>

</update>

int updateCount = userMapper.batchUpdate(....)

I want to get the number of updated rows.

MySQL driver seems to have an  option useAffectedRows ,Can get the number of updated rows, but sql server driver does not seem to have similar parameters.

When I get the number of updated rows, I always can't get all the updated rows, It seems that it always returns the result of the first update statement

so,Can someone help me or how do you do it when using mybatis batch update sql in sql server database.

Looking forward to your reply, thanks!

[Guy Rouillier]

Are you using the ExecutorType.BATCH?  What version of MyBatis are you using?

--

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/d1896470-6283-4ffb-ad39-7ed1d6cf8e7dn%40googlegroups.com.

[Iwao AVE!]

Hello,

That is a little bit different than #1313 actually.
https://github.com/mybatis/mybatis-3/issues/1313#issuecomment-770539572
When you put multiple statements separated by semicolon, the result depends on the driver implementation.
In fact, mysql-connector-java also returns the result for the first statement.
If you want to know the number of affected rows, you need to execute them separately and sum up the results [1].

People usually do it for efficiency, but it is not very efficient especially when the list contains a lot of items.
What it does in JDBC level, basically, is something like this:

String sql = "update ...;" + "update ...;" + "update ...;";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, "john");
ps.setInt(2, 124);
...
ps.setString(981, "foo");
ps.execute();
int c = ps.getUpdateCount();

Compared to executing each UPDATE separately, it leaves a bigger memory footprint.

[1] When all statements are the same, JDBC provides an efficient way that reuses the same PreparedStatement (see https://stackoverflow.com/a/58914577 ), but this is not applicable to your case (because of `<if />`s, PreparedStatement cannot be reused).

Regards,
Iwao

[shang liu]

Oh i see thanks for your answer