动态sql

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>
展开阅读全文

Windows版YOLOv4目标检测实战:训练自己的数据集

04-26
©️2020 CSDN 皮肤主题: 游动-白 设计师: 上身试试 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值