MyBatis annotation function where IN list of uuids

Question

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: org.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: org.apache.ibatis.binding.BindingException: Parameter 'uuid' not found. Available parameters are [amount, param1, uuids, param2]

ave · Accepted Answer · 2025-01-29 21:52:51Z

2

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.org/mybatis-3/dynamic-sql.html#script

answered Jan 29 at 21:52

ave

3,7242 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vetalll Sep 12 at 10:33

Sorry.. I forgot to mark your answer.. Thank you.. BTW now i have another question. Please take a look stackoverflow.com/questions/79762817/…

Collectives™ on Stack Overflow

MyBatis annotation function where IN list of uuids

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related