mybatis-user

Use MyBatis batch update in sql server database

Classic

List

Threaded

4 messages Options

shang liu

Use MyBatis batch update in sql server database

Example:

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!

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/d1896470-6283-4ffb-ad39-7ed1d6cf8e7dn%40googlegroups.com.

Guy Rouillier-2

Re: Use MyBatis batch update in sql server database

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

Guy Rouillier

On 2/2/2021 7:44:53 PM, "shang liu" <[hidden email]> wrote:

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!

--
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 [hidden email].
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!

Re: Use MyBatis batch update in sql server database

In reply to this post by shang liu

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

On Wed, Feb 3, 2021 at 9:45 AM shang liu <[hidden email]> wrote:

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!

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/d1896470-6283-4ffb-ad39-7ed1d6cf8e7dn%40googlegroups.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2QeQd5cWVk6g0Nq9_ViH4PqTTfQRuORYDFiL2GqDRT0bg%40mail.gmail.com.

shang liu

Re: Use MyBatis batch update in sql server database

Oh i see thanks for your answer

在2021年2月4日星期四 UTC+8 上午12:15:04<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

On Wed, Feb 3, 2021 at 9:45 AM shang liu <[hidden email]> wrote:
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!

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/d1896470-6283-4ffb-ad39-7ed1d6cf8e7dn%40googlegroups.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/4c5f0625-e690-4186-90f6-62e03013f4cen%40googlegroups.com.