02.Mybatis-常用sql

1.Mybatis常用select语句

<select id="getRolesByParam" parameterType="java.util.HashMap" resultType="com.serlyf.system.entity.Role">
    SELECT
    sr.ROLE_ID AS roleId,
    sr.ROLE_NAME AS roleName,
    sr.IS_ACTIVE AS isActive,
    sr.REMARK AS remark,
    sr.CREATE_DATETIME AS createDatetime,
    CONCAT(su.FIRST_NAME,su.LAST_NAME) AS createUser,
    sr.UPDATE_DATETIME AS updateDatetime,
    CONCAT(su2.FIRST_NAME,su2.LAST_NAME) AS createUser
    FROM
    sys_role sr
    LEFT JOIN sys_user su ON su.USER_ID=sr.CREATE_USER 
    LEFT JOIN sys_user su2 ON su2.USER_ID=sr.UPDATE_USER 
    WHERE sr.IS_ACTIVE='Y'
    <if test="roleName!=null and roleName!=''">
        AND sr.ROLE_NAME like CONCAT('%',#{roleName},'%') 
    </if>
</select>

2.Mybatis常用insert语句

<insert id="insertRole" parameterType="com.serlyf.system.entity.Role">
    insert into sys_role (ROLE_ID,ROLE_NAME,REMARK,IS_ACTIVE,CREATE_DATETIME,CREATE_USER)
    values (#{roleId}, #{roleName}, #{remark},#{isActive}, #{createDatetime}, #{createUser})
</insert>

3.Mybatis常用批量insert语句

<insert id="batchInsertMenuRoleRef" parameterType="java.util.List">
    insert into sys_menu_role_ref (ID,ROLE_ID,MENU_ID)VALUES
    <foreach collection="list" item="item" index="index"
             separator=",">
        (#{item.id},#{item.roleId},#{item.menuId})
    </foreach>
</insert>

4.Mybatis常用update语句

<update id="updateRole" parameterType="com.serlyf.system.entity.Role">
    update sys_role
    <set>
        <if test="roleName != null">
            ROLE_NAME = #{roleName},
        </if>
        <if test="remark != null">
            REMARK = #{remark},
        </if>
        <if test="isActive != null">
            IS_ACTIVE = #{isActive},
        </if>
        <if test="updateDatetime != null">
            UPDATE_DATETIME = #{updateDatetime},
        </if>
        <if test="updateUser != null">
            UPDATE_USER = #{updateUser}
        </if>
    </set>
    where ROLE_ID = #{roleId}
</update>

5.Mybatis常用delete语句

<delete id="deleteRoleByRoleId" parameterType="java.lang.String">
    update sys_role set IS_ACTIVE='N'
    where ROLE_ID = #{roleId}
</delete>

6.Mybatis常用的Like语句

WHERE sr.IS_ACTIVE='Y'
<if test="roleName!=null and roleName!=''">
    AND sr.ROLE_NAME like CONCAT('%',#{roleName},'%') 
</if>

WHERE sr.IS_ACTIVE='Y'
<if test="roleName!=null and roleName!=''">
    AND sr.ROLE_NAME like "%"#{roleName}"%" 
</if>

7.Mybatis常用select字段拼接

CONCAT(su2.FIRST_NAME,su2.LAST_NAME) AS createUser