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’ AND sr.ROLE_NAME like CONCAT(‘%’,#{roleName},’%’)
<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>
<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>
<delete id="deleteRoleByRoleId" parametertype="java.lang.String">
update sys_role set IS_ACTIVE='N'
where ROLE_ID = #{roleId}
</delete>
<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>
<select id="getRoleListByUserId" parametertype="java.lang.String" 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,
sr.CREATE_USER AS createUser,
sr.UPDATE_DATETIME AS updateDatetime,
sr.UPDATE_USER AS updateUser
FROM
sys_role sr
LEFT JOIN sys_user_role_ref surr ON surr.ROLE_ID=sr.ROLE_ID
WHERE sr.IS_ACTIVE='Y' AND surr.USER_ID=#{userId}
</select>
Original: https://www.cnblogs.com/NIAN2011/p/16647785.html
Author: NIANER2011
Title: 1.Mybatis-XML模板
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/609021/
转载文章受原作者版权保护。转载请注明原作者出处！