01.Mybatis-XML模板

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 into sys_role (ROLE_ID,ROLE_NAME,REMARK,IS_ACTIVE,CREATE_DATETIME,CREATE_USER)
values (#{roleId}, #{roleName}, #{remark},#{isActive}, #{createDatetime}, #{createUser})

update sys_role


ROLE_NAME = #{roleName},


REMARK = #{remark},


IS_ACTIVE = #{isActive},


UPDATE_DATETIME = #{updateDatetime},


UPDATE_USER = #{updateUser}


where ROLE_ID = #{roleId}

update sys_role set IS_ACTIVE='N'
where ROLE_ID = #{roleId}

insert into sys_menu_role_ref (ID,ROLE_ID,MENU_ID)VALUES

(#{item.id},#{item.roleId},#{item.menuId})

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}