I have a list of UUIDS of users where I need to decrease a number of gold by a given amount.

MyBatis function:

@Update(
    """
    UPDATE users
    SET gold = gold - #{amount}
    <where>
        gold >= #{amount}
        AND uuid IN 
        <foreach 
            collection="uuids" item="uuid" open="(" separator="," close=")" > #{uuid}
        </foreach>
    </where>
"""
)
fun reduceUsersGold(
    @Param("uuids") uuids: List<String>,
    @Param("amount") amount: Int,
): Int

And this is what the database looks like (simplified):

create TABLE IF NOT EXISTS users
(
    pk                     INT PRIMARY KEY AUTO_INCREMENT,
    uuid                   VARCHAR(255) NOT NULL,
    name                   VARCHAR(255) NOT NULL,
    email                  VARCHAR(255) NOT NULL,
    gold                   INT          NOT NULL,
    unique (uuid),
    unique (email)
);

Unfortunately for my function, every time I have the following error:

07:59:14.340 DEBUG c.s.g.d.d.U.reduceUsersGold - ==>  Preparing: UPDATE users SET gold = gold - ? <where> gold >= ? AND uuid IN <foreach collection="uuids" item="uuid" open="(" separator="," close=")" > ? </foreach> </where>
### Error updating database.  Cause: .apache.ibatis.binding.BindingException: Parameter 'uuid' not found. Available parameters are [amount, param1, uuids, param2]
### The error may exist in com/simple/games/data/dao/UserDao.java (best guess)
### The error may involve com.simple.games.data.dao.UserDao.reduceUsersGold-Inline
### The error occurred while setting parameters
### SQL: UPDATE users         SET gold = gold - ?         <where>             gold >= ?             AND uuid IN              <foreach                  collection="uuids" item="uuid" open="(" separator="," close=")" > ?             </foreach>         </where>
### Cause: .apache.ibatis.binding.BindingException: Parameter 'uuid' not found. Available parameters are [amount, param1, uuids, param2]

I have a list of UUIDS of users where I need to decrease a number of gold by a given amount.

MyBatis function:

@Update(
    """
    UPDATE users
    SET gold = gold - #{amount}
    <where>
        gold >= #{amount}
        AND uuid IN 
        <foreach 
            collection="uuids" item="uuid" open="(" separator="," close=")" > #{uuid}
        </foreach>
    </where>
"""
)
fun reduceUsersGold(
    @Param("uuids") uuids: List<String>,
    @Param("amount") amount: Int,
): Int

And this is what the database looks like (simplified):

create TABLE IF NOT EXISTS users
(
    pk                     INT PRIMARY KEY AUTO_INCREMENT,
    uuid                   VARCHAR(255) NOT NULL,
    name                   VARCHAR(255) NOT NULL,
    email                  VARCHAR(255) NOT NULL,
    gold                   INT          NOT NULL,
    unique (uuid),
    unique (email)
);

Unfortunately for my function, every time I have the following error:

07:59:14.340 DEBUG c.s.g.d.d.U.reduceUsersGold - ==>  Preparing: UPDATE users SET gold = gold - ? <where> gold >= ? AND uuid IN <foreach collection="uuids" item="uuid" open="(" separator="," close=")" > ? </foreach> </where>
### Error updating database.  Cause: .apache.ibatis.binding.BindingException: Parameter 'uuid' not found. Available parameters are [amount, param1, uuids, param2]
### The error may exist in com/simple/games/data/dao/UserDao.java (best guess)
### The error may involve com.simple.games.data.dao.UserDao.reduceUsersGold-Inline
### The error occurred while setting parameters
### SQL: UPDATE users         SET gold = gold - ?         <where>             gold >= ?             AND uuid IN              <foreach                  collection="uuids" item="uuid" open="(" separator="," close=")" > ?             </foreach>         </where>
### Cause: .apache.ibatis.binding.BindingException: Parameter 'uuid' not found. Available parameters are [amount, param1, uuids, param2]

• kotlin
• annotations
• mybatis

1 Answer 1

To use dynamic tags like <where> or <foreach> in an annotation, you need to enclose the SQL in <script>.
So, it would look as follows:

@Update(
    """
    <script>
    UPDATE users
    SET gold = gold - #{amount}
    <where>
        gold >= #{amount}
        AND uuid IN 
        <foreach 
            collection="uuids" item="uuid" open="(" separator="," close=")" > #{uuid}
        </foreach>
    </where>
    </script>
"""
)
fun reduceUsersGold(
    @Param("uuids") uuids: List<String>,
    @Param("amount") amount: Int,
): Int

Here is the doc:
https://mybatis./mybatis-3/dynamic-sql.html#script