Parameter Passing Mechanisms

When invoking mapper methods, parameters can be transferred to the XML mapping file using several distinct strategies:

• JavaBean Properties: MyBatis utilizes the getter and setter methods of a Java object to bind parameters. The OGNL expression used in the XML should match the property name.
• Map Keys: When passing a Map instance, the keys of the map serve as the parameter names for lookup within the XML.
• @Param Annotation: To multiple parameters or to explicitly name parameters, the @Param("name") annotation can be used in the mapper interface method signature.

Dynamic SQL Construction

Dynamic SQL allows for flexible query generation based on runtime conditions. Below are the core elemnets used to construct these queries.

Conditional Logic with <if>

The <if> tag evaluates a test expression. If the condition is met, the SQL fragment inside the tag is included in the final query.

<if test="userName != null and userName != ''">
    AND user_name = #{userName}
</if>

Branching Logic with <choose>

Similar to a switch statement in Java, this structure allows for selecting one out of multiple mutually exclusive conditions.

<choose>
    <when test="sortType == 'date'">
        ORDER BY create_date DESC
    </when>
    <when test="sortType == 'name'">
        ORDER BY display_name ASC
    </when>
    <otherwise>
        ORDER BY id ASC
    </otherwise>
</choose>

Conditional WHERE Clause

The <where> element automatically inserts the WHERE keyword if any content is returned by its child tags. Furthermore, it strips any leading AND or OR keywords to prevent syntax errors.

<where>
    <if test="status != null">
        AND status = #{status}
    </if>
    <if test="minDate != null">
        AND create_time >= #{minDate}
    </if>
</where>

Dynamic UPDATE Statements with <set>

Used primarily for update operations, the <set> tag dynamically includes the SET keyword and removes any trailing commas from the final SQL string.

<update id="updateUserProfile">
    UPDATE sys_user
    <set>
        <if test="email != null">email = #{email},</if>
        <if test="phoneNumber != null">phone_number = #{phoneNumber},</if>
        <if test="lastLogin != null">last_login = #{lastLogin}</if>
    </set>
    WHERE user_id = #{userId}
</update>

Custom Trimming with <trim>

For fine-grained control over prefix and suffix handling, the <trim> tag allows customization of how SQL fragments are assembled.

<trim prefix="SET" suffixOverrides=",">
    <if test="fieldA != null">col_a = #{fieldA},</if>
    <if test="fieldB != null">col_b = #{fieldB},</if>
</trim>

Iteration with <foreach>

This tag is essential for iterating over collections, commonly used in IN clauses or batch insert operations.

<select id="selectByIds" resultType="Product">
    SELECT * FROM product
    WHERE id IN
    <foreach collection="idList" item="currentId" open="(" close=")" separator=",">
        #{currentId}
    </foreach>
</select>

SQL Fragment Reuse

To reduce redundancy, common SQL fragments can be defined using <sql> and included elsewhere using <include>.

<sql id="commonColumns">
    user_id, username, email, create_time
</sql>

<select id="findActiveUsers" resultType="User">
    SELECT <include refid="commonColumns"/>
    FROM app_user
    WHERE status = 1
</select>

Parameter Symbols: #{} vs ${}

There is a critical distinction between these two symbols. #{} creates a parameterized query placeholder (?), offering protection against SQL injection. In contrast, ${} performs direct string substitution into the SQL statement, which is necessary for dynamic table or column names but requires careful handling to avoid security vulnerabilities.

Mapping One-to-Many Relationships

Handling collections envolves using the <collection> tag within a <resultMap>. The following example maps a Department entity containing a list of Employee entities.

<resultMap type="com.example.model.Department" id="deptResultMap">
    <id column="dept_id" property="departmentId"/>
    <result column="dept_name" property="departmentName"/>
    
    <!-- Mapping the list of employees -->
    <collection property="staffList" ofType="com.example.model.Employee">
        <id column="emp_id" property="employeeId"/>
        <result column="emp_name" property="employeeName"/>
        <result column="emp_role" property="role"/>
    </collection>
</resultMap>

<select id="getDepartmentWithStaff" resultMap="deptResultMap">
    SELECT 
        d.id AS dept_id,
        d.name AS dept_name,
        e.id AS emp_id,
        e.name AS emp_name,
        e.role AS emp_role
    FROM departments d
    LEFT JOIN employees e ON d.id = e.dept_id
    WHERE d.id = #{deptId}
</select>

Mapping One-to-One Relationships

For single associated objects, the <association> tag is used. This example retrieves a User and their associated Profile.

<resultMap type="com.example.model.User" id="userProfileMap">
    <id column="user_id" property="id"/>
    <result column="login_name" property="loginName"/>
    
    <!-- Mapping the single profile object -->
    <association property="profile" javaType="com.example.model.Profile">
        <id column="profile_id" property="id"/>
        <result column="avatar_url" property="avatarUrl"/>
        <result column="bio_text" property="bio"/>
    </association>
</resultMap>

<select id="getUserWithProfile" resultMap="userProfileMap">
    SELECT 
        u.id AS user_id,
        u.login_name,
        p.id AS profile_id,
        p.avatar_url,
        p.bio_text
    FROM app_user u
    LEFT JOIN user_profile p ON u.id = p.user_id
    WHERE u.id = #{userId}
</select>

Implementing Recursive Queries

Recursive structures, such as category trees or comment threads, can be mapped by nesting a <collection> that calls another select statement using the current ID.

<!-- Base query for top-level comments -->
<select id="findCommentsByPost" resultMap="commentTreeMap">
    SELECT * FROM blog_comment
    WHERE parent_id IS NULL
    AND post_id = #{postId}
    ORDER BY created_at ASC
</select>

<!-- Recursive query for child comments -->
<select id="findRepliesByParent" resultMap="commentTreeMap">
    SELECT * FROM blog_comment
    WHERE parent_id = #{parentId}
    ORDER BY created_at ASC
</select>

<resultMap id="commentTreeMap" type="com.example.model.Comment">
    <id column="id" property="id"/>
    <result column="content" property="content"/>
    <result column="author" property="authorName"/>
    <result column="created_at" property="timestamp"/>
    
    <!-- Self-referencing collection -->
    <collection property="replies" 
                column="id" 
                select="findRepliesByParent"
                ofType="com.example.model.Comment"/>
</resultMap>