Using MyBatis Dynamic SQL Tags: foreach, if, choose, trim, selectKey and More

The author, a senior architect, introduces MyBatis dynamic SQL tags that enable flexible query construction. Key tags covered are foreach , if , choose , trim , and selectKey , each with their attributes such as item , index , collection , open , separator , and close .

foreach iterates over collections for IN clauses. The collection attribute must be specified and can be a list , array , or a custom Map . Example:

public List<Entity> queryById(List<String> userids);
SELECT * FROM entity
  WHERE id IN
#{userid}

concat fuzzy query demonstrates conditional SQL building with if to include a LIKE clause only when a parameter is not null.

SELECT * FROM entity
name LIKE concat('%', #{name}, '%')

choose works like a switch statement, selecting the first when whose test evaluates to true, otherwise executing otherwise . Example:

SELECT * FROM User u
u.username LIKE CONCAT('%', #{username}, '%')
AND u.sex = #{sex}

selectKey generates auto‑increment keys for inserts, useful for Oracle sequences or MySQL functions.

select nextval('student')
INSERT INTO STUDENT_TBL (STUDENT_ID, STUDENT_NAME, ... ) VALUES (#{studentId}, #{studentName}, ...)

The article also covers if combined with where to avoid dangling AND/OR, if + set for update statements, and using trim to replace where or set tags, automatically removing redundant keywords.

STUDENT_NAME LIKE CONCAT('%', #{studentName}, '%')
...

Further examples illustrate foreach with array and list parameters for IN clauses, and the use of reusable <sql> fragments with <include> .

o.order_id, o.cid, o.address, i.product_id, i.count
SELECT
FROM ordertable o JOIN orderitem i ON o.orderitem_id = i.orderitem_id
  WHERE o.order_id = #{orderId}