MyBatis

1. forEach cycle

The attributes of   forEach element mainly include item, idnex, collection, open, separator, close.

1. collection: incoming List or Array or self encapsulated Map.
2. item: alias of the element iteration in the collection.
3. idnex: index of element iteration yes in the collection.
4. open: where is followed by what to start with, such as' ('.
5. Separator: a separator that represents the value of each iteration.
6. close: where is followed by what to end with, such as') '.

//A container needs to be passed in mapper
public List<User> queryByIdList(List<Integer> userIdList);

<select id="queryByIdList" resultMap="BaseResultMap" parameterType="map">
    SELECT * FROM user
    WHERE userId IN
    <foreach collection="userIdList" item="id" index="index" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

2. concat fuzzy query

//Fuzzy query using concat to splice sql
<select id="queryByName" resultMap="BaseResultMap" paramterType"string">
    SELECT * FROM user
    <where>
        <if test="name != null">
            name like concat('%', concat(#{name}, '%'))
        </if>
    </where>
</select>

3. if + where tag

   use the if tag to determine whether the parameter is valid for condition query.

<select id="getUserList" resultMap="BaseResultMap" paramterType="com.demo.User">
    SELECT * FROM user
    <where>
        <if test="userId !=null and userId!= ''">
            userId= #{userId}
        </if>
        <if test="name !=null and name!= ''">
            AND name= #{name}
        </if>
        <if phone="userId !=null and phone!= ''">
            AND phone= #{phone}
        </if>
    </where>
</select>

In a where dynamic statement, the where tag automatically removes AND OR. Prevent WHERE AND errors.

4,if + set

   use the set tag to dynamically configure the set keyword, use the if + set tag, and do not update if an item is null.

<update id="updateUser" paramterType="com.demo.User">
    UPDATE user
    <set>
        <if test=" name != null and name != ''">
            name = #{name},
        </if>
        <if test=" phone != null and phone != ''">
            phone = #{phone},
        </if>
    </set>
    WHERE userId = #{userId}
</update>

5. if + trim replace where/set tag

                     .

<select id="getUserList" resultMap="BaseResultMap" paramterType="com.demo.User">
    SELECT * FROM user
    <trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="userId !=null and userId!= ''">
            userId= #{userId}
        </if>
        <if test="name !=null and name!= ''">
            AND name= #{name}
        </if>
        <if phone="userId !=null and phone!= ''">
            AND phone= #{phone}
        </if>
    </trim>
</select>

<update id="updateUser" paramterType="com.demo.User">
    UPDATE user
    <trim prefix="SET" suffixOverrides=",">
        <if test=" name != null and name != ''">
            name = #{name},
        </if>
        <if test=" phone != null and phone != ''">
            phone = #{phone},
        </if>
    </trim>
    WHERE userId = #{userId}
</update>

5. choose (when, otherwise) label

The                        . When all the conditions of when in choose are not satisfied, execute sql in otherwise. Similar to the switch statement in java, choose is switch, when is case, and other is default.

<select id="selectCustomerByCustNameAndType" parameterType="map" resultMap="BaseResultMap">
    SELECT * FROM user
    <choose>
        <when test="Utype == 'name'">
            WHERE name = #{name} 
        </when>
        <when test="Utype == 'phone'">
            WHERE phone= #{phone}
        </when>
        <when test="Utype == 'email'">
            WHERE email= #{email}
        </when>
        <otherwise>
            WHERE name = #{name}
        </otherwise>
    </choose>
</select>