2023/8/15 Java EE复习笔记（mybatis-2 映射文件sql优化技巧）

一、动态sql拼接技巧

1、sql语句段的拼接

通用语句段

<sql id="partOfSql">
    id
    ,`name`,age,gender,banji_id
</sql>

拼接调用示例

<select id="selectById" parameterType="Integer" resultMap="studentMap">
    select
    <include refid="partOfSql"/>
    from student
    where id = #{id}
</select>

2、where+if标签拼接

这里不存在else的情况，满足条件就将相应信息添加在where中，没有满足的条件就不加where。

<select id="selectByDynamicCondition" parameterType="Student" resultMap="studentMap">
    select *
    from student 
    <where>
        <if test="name!=null and name!='' ">
            and x.name like concat('%',#{name},'%')
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
        <if test="gender!=null and gender!='' ">
            and gender = #{gender}
        </if>
    </where>
</select>

3、choose标签拼接

choose拼接时，choose中的语句最多执行一条，所以当参数没有满足任何条件时，where显得多余，此时需要在otherwise标签里加上1=1。

<select id="selectByChoose" parameterType="student" resultType="student">
    select id,name,age,gender
    from xuesheng
    where
    <choose>
        <when test="id!=null and id!=0">
            id=#{id}
        </when>
        <when test="name!=null and name!=''">
            name like concat('%',#{name},'%')
        </when>
        <when test="gender!=null and gender!=''">
            gender=#{gender}
        </when>
        <otherwise>
            1=1
        </otherwise>
    </choose>
</select>

4、in()循环拼接

（1）参数为List集合

<delete id="deleteAllByList">
    delete
    from student
    where id in
    <foreach collection="list" open="(" item="id" separator="," close=")">
        #{id}
    </foreach>
</delete>

（2）参数为数组

<delete id="deleteAllByArray">
    delete
    from student
    where id in
    <foreach collection="array" open="(" item="id" separator="," close=")">
        #{id}
    </foreach>
</delete>

5、set+if标签拼接

<update id="updateCondition" parameterType="student">
    update student
    <set>
        <if test="name!=null and name!=''">
            name=#{name},
        </if>
        <if test="age!=null">
            age=#{age},
        </if>
        <if test="gender!=null and gender!=''">
            gender=#{gender},
        </if>
    </set>
    <where>
        <if test="id!=null">
            and id=#{id}
        </if>
    </where>
</update>

二、resultMap返回属性自定义匹配标签

1、名称不匹配

<resultMap id="studentMap" type="Student">
        <result property="name" column="student_name"></result>
        <result property="banjiId" column="class_id"></result>
</resultMao>

2、单向一对一关系

举例：学生->班级

<resultMap id="studentMap" type="Student">
    <result property="id" column="id"></result>
    <result property="name" column="name"></result>
    <result property="age" column="age"></result>
    <result property="gender" column="gender"></result>
    <result property="banjiId" column="banjiId"></result>
    <!--association用于封装一对一的跟随属性-->
    <association property="banji" javaType="Banji">
        <id column="banjiId" property="id"></id>
        <result column="banjiName" property="name"></result>
    </association>
</resultMap>

3、单向一对多关系

举例：班级->学生

<resultMap id="banjiMap" type="Banji">
    <id property="id" column="id"></id>
    <result column="name" property="name"></result>
    <!--collection用于封装一对多的跟随属性-->
    <collection property="studentList" ofType="Student">
        <id property="id" column="studentId"></id>
        <result column="studentName" property="name"></result>
        <result column="studentAge" property="age"></result>
        <result column="studentGender" property="gender"></result>
        <result column="id" property="banjiId"></result>
    </collection>
</resultMap>