How to use dynamic SQL in MyBatis?

Dynamic SQL in MyBatis is a way to dynamically generate SQL statements based on conditions, allowing for the creation of different SQL statements depending on the conditions, enabling dynamic queries. Here are some common methods for using dynamic SQL in MyBatis:

1. Conditions are added to SQL statements using tags, so that the corresponding SQL statement is only executed when the condition is met. Here is an example code:

<select id="getUserList" parameterType="map" resultType="User">
    SELECT * FROM user
    <where>
        <if test="name != null">
            AND name = #{name}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</select>

1. We can use if-else statements and labels to achieve similar functionality to a switch-case statement. Below is an example code:

<select id="getUserList" parameterType="map" resultType="User">
    SELECT * FROM user
    <where>
        <choose>
            <when test="name != null">
                AND name = #{name}
            </when>
            <when test="age != null">
                AND age = #{age}
            </when>
            <otherwise>
                AND gender = #{gender}
            </otherwise>
        </choose>
    </where>
</select>

1. Use tags to remove unnecessary parts of the SQL statements. Example code is shown below:

<select id="getUserList" parameterType="map" resultType="User">
    SELECT * FROM user
    <where>
        <trim prefix="AND" prefixOverrides="AND">
            <if test="name != null">
                AND name = #{name}
            </if>
            <if test="age != null">
                AND age = #{age}
            </if>
        </trim>
    </where>
</select>

1. Tags are used to perform looping operations, typically for constructing conditions for IN statements. Example code is as follows:

<select id="getUserList" parameterType="map" resultType="User">
    SELECT * FROM user
    WHERE id IN
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

By using the above methods, you can dynamically generate different SQL statements based on conditions to achieve dynamic query functionality. For more information and examples of how to use dynamic SQL in MyBatis, refer to the official MyBatis documentation.