动态sql

最新推荐文章于 2022-05-14 19:47:42 发布

闹心。

最新推荐文章于 2022-05-14 19:47:42 发布

阅读量135

点赞数

本文链接：https://blog.csdn.net/hhhahahaha/article/details/107032426

版权

1-if(存在where 连接主外键)

<!--通过userName和userRole，查询用户列表（包含用户角色名称）-->
<!--    <select id="getUserListIncludeUserRoleName" parameterType="User" resultMap="zhoujielun">
        SELECT u.*,r.`roleName`  FROM smbms_user u,smbms_role r
        WHERE userName LIKE CONCAT('%',#{userName},'%')
        AND userRole =#{userRole}
        AND u.`userRole`=r.`id`;
    </select>-->
     <!--动态sql语句-->
      <select id="getUserListIncludeUserRoleName" parameterType="User" resultMap="zhoujielun">
          select u.*,r.`roleName`
          from smbms_user u,smbms_role r
          where u.userRole=r.id  
          <if test="userName!=null and userName!=''">
              and userName LIKE CONCAT('%',#{userName},'%')
          </if>
          <if test="userRole!=null and userRole!=''">
              and userRole =#{userRole}
          </if>

      </select>

2-if+where（where可以剔除开头的and）

 <!--需求：根据用户名和用户角色， 模糊查询用户列表，:#{必须对象的属性名，能随便写}-->
<!--    <select id="getUserListByUserNameAndUserRole"  parameterType="User" resultType="User">
        SELECT *
        FROM smbms_user
        WHERE userName LIKE CONCAT('%',#{userName},'%')
        AND userRole=#{userRole}
    </select>-->
   <!-- where 标签遇到 and or  ，自动删除-->

    <select id="getUserListByUserNameAndUserRole" parameterType="User" resultType="User">
        select *
        from smbms_user
        <where>
            <if test="userName!=null and userName!=''">
                and userName LIKE CONCAT('%',#{userName},'%')
            </if>
            <if test="userRole!=null and userRole!=''">
                AND userRole=#{userRole}
            </if>
        </where>

    </select>

3-set+if（set可以剔除末尾，）

<!--需求：根据指定id，修改用户信息-->
 <!--   <update id="modifyUser" parameterType="User" >
            UPDATE smbms_user
            SET userCode=#{userCode},userName=#{userName},userPassword=#{userPassword},
            gender=#{gender},birthday=#{birthday},
            phone=#{phone},address=#{address},
            userRole=#{userRole},modifyBy=#{modifyBy},modifyDate=#{modifyDate}
            WHERE id=#{id}
    </update>-->

    <update id="modifyUser" parameterType="User">
        update smbms_user
        <set>
            <if test="userCode!=null and userCode!=''">userCode=#{userCode},</if>
            <if test="userName!=null and userName!=''">userName=#{userName},</if>
            <if test="userPassword!=null and userPassword!=''">userPassword=#{userPassword},</if>
            <if test="gender!=null and gender!=''">gender=#{gender},</if>
            <if test="birthday!=null and birthday!=''">birthday=#{birthday},</if>
            <if test="phone!=null and phone!=''">phone=#{phone},</if>
            <if test="address!=null and address!=''">address=#{address},</if>
            <if test="userRole!=null and userRole!=''">userRole=#{userRole},</if>
            <if test="modifyBy!=null and modifyBy!=''">modifyBy=#{modifyBy},</if>
            <if test="modifyDate!=null and modifyDate!=''">modifyDate=#{modifyDate}</if>
        </set>
        where id=#{id}
    </update>

4-trim+if :查询

<select id="getUserListByUserNameAndUserRole" parameterType="User" resultType="User">
    select *
    from smbms_user
    <trim prefix="where" prefixOverrides="and|or" >
        <if test="userName!=null and userName!=''">
            and userName LIKE CONCAT('%',#{userName},'%')
        </if>
        <if test="userRole!=null and userRole!=''">
            AND userRole=#{userRole}
        </if>
    </trim>
</select>

4-trim+if：修改

<update id="modifyUser" parameterType="User">
    update smbms_user
    <trim prefix="set" suffix="where id=#{id}" suffixOverrides=",">
        <if test="userCode!=null and userCode!=''">userCode=#{userCode},</if>
        <if test="userName!=null and userName!=''">userName=#{userName},</if>
        <if test="userPassword!=null and userPassword!=''">userPassword=#{userPassword},</if>
        <if test="gender!=null and gender!=''">gender=#{gender},</if>
        <if test="birthday!=null and birthday!=''">birthday=#{birthday},</if>
        <if test="phone!=null and phone!=''">phone=#{phone},</if>
        <if test="address!=null and address!=''">address=#{address},</if>
        <if test="userRole!=null and userRole!=''">userRole=#{userRole},</if>
        <if test="modifyBy!=null and modifyBy!=''">modifyBy=#{modifyBy},</if>
        <if test="modifyDate!=null and modifyDate!=''">modifyDate=#{modifyDate}</if>
    </trim>
</update>