Issue with Updating Field Value Using MyBatis Dynamic SQL

[zgqq]

I am trying to construct a dynamic SQL query to update the balance field in the user table. The goal is to increment the balance field by a certain value for a specific uid. However, I'm facing difficulties in getting the query to execute correctly.

I am looking to achieve an effect similar to the following code snippet:

   @Update("UPDATE user SET balance = balance + #{balance} WHERE uid = #{uid}")
    void addUserBalance(@Param("uid") Integer uid, @Param("balance") Integer balance);

Attempted Solutions:
I tried using .set(balance).equalToWhenPresent(...) but it didn't work as expected.
Could you please provide some guidance on how to correctly construct this query using MyBatis Dynamic SQL? Any help would be greatly appreciated.

[hermer]

I also have the same confusion

[jeffgbutler]

You can do something like this:

int addToBalance(Integer uid, Integer balance) {
  UpdateStatementProvider updateStatement = update(user)
                .set(user.balance).equalTo(add(user.balance, constant(balance.toString())))
                .where(user.uid, isEqualTo(uid))
                .build().render(RenderingStrategies.MYBATIS3);
  ...
}

This isn't exactly what you asked for because the balance is not rendered as a bind parameter - it will be rendered as a constant string.

I will add something to the library that supports rendering this with a bind parameter.

[jeffgbutler]

Starting in version 1.5.1, you can do this:

int addToBalance(Integer uid, Integer balance) {
  UpdateStatementProvider updateStatement = update(user)
                .set(user.balance).equalTo(add(user.balance, value(balance)))
                .where(user.uid, isEqualTo(uid))
                .build().render(RenderingStrategies.MYBATIS3);
  ...
}

The value() function will be released in version 1.5.1. It should be in a snapshot build now.

[hermer]

good job