你就说灵不灵活吧
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.system.mapper.SysDeptMapper"><resultMap type="SysDept" id="SysDeptResult"><id property="deptId" column="dept_id" /><result property="parentId" column="parent_id" /><result property="ancestors" column="ancestors" /><result property="deptName" column="dept_name" /><result property="orderNum" column="order_num" /><result property="leader" column="leader" /><result property="phone" column="phone" /><result property="email" column="email" /><result property="status" column="status" /><result property="delFlag" column="del_flag" /><result property="parentName" column="parent_name" /><result property="createBy" column="create_by" /><result property="createTime" column="create_time" /><result property="updateBy" column="update_by" /><result property="updateTime" column="update_time" /></resultMap><sql id="selectDeptVo">select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.order_num, d.leader, d.phone, d.email, d.status, d.del_flag, d.create_by, d.create_timefrom sys_dept d</sql><select id="selectDeptList" parameterType="SysDept" resultMap="SysDeptResult"><include refid="selectDeptVo"/>where d.del_flag = '0'<if test="deptId != null and deptId != 0">AND dept_id = #{deptId}</if><if test="parentId != null and parentId != 0">AND parent_id = #{parentId}</if><if test="deptName != null and deptName != ''">AND dept_name like concat('%', #{deptName}, '%')</if><if test="status != null and status != ''">AND status = #{status}</if><!-- 数据范围过滤 -->${params.dataScope}order by d.parent_id, d.order_num</select><select id="selectDeptListByRoleId" resultType="Long">select d.dept_idfrom sys_dept dleft join sys_role_dept rd on d.dept_id = rd.dept_idwhere rd.role_id = #{roleId}<if test="deptCheckStrictly">and d.dept_id not in (select d.parent_id from sys_dept d inner join sys_role_dept rd on d.dept_id = rd.dept_id and rd.role_id = #{roleId})</if>order by d.parent_id, d.order_num</select><select id="selectDeptById" parameterType="Long" resultMap="SysDeptResult">select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.order_num, d.leader, d.phone, d.email, d.status,(select dept_name from sys_dept where dept_id = d.parent_id) parent_namefrom sys_dept dwhere d.dept_id = #{deptId}</select><select id="checkDeptExistUser" parameterType="Long" resultType="int">select count(1) from sys_user where dept_id = #{deptId} and del_flag = '0'</select><select id="hasChildByDeptId" parameterType="Long" resultType="int">select count(1) from sys_deptwhere del_flag = '0' and parent_id = #{deptId} limit 1</select><select id="selectChildrenDeptById" parameterType="Long" resultMap="SysDeptResult">select * from sys_dept where find_in_set(#{deptId}, ancestors)</select><select id="selectNormalChildrenDeptById" parameterType="Long" resultType="int">select count(*) from sys_dept where status = 0 and del_flag = '0' and find_in_set(#{deptId}, ancestors)</select><select id="checkDeptNameUnique" resultMap="SysDeptResult"><include refid="selectDeptVo"/>where dept_name=#{deptName} and parent_id = #{parentId} and del_flag = '0' limit 1</select><insert id="insertDept" parameterType="SysDept">insert into sys_dept(<if test="deptId != null and deptId != 0">dept_id,</if><if test="parentId != null and parentId != 0">parent_id,</if><if test="deptName != null and deptName != ''">dept_name,</if><if test="ancestors != null and ancestors != ''">ancestors,</if><if test="orderNum != null">order_num,</if><if test="leader != null and leader != ''">leader,</if><if test="phone != null and phone != ''">phone,</if><if test="email != null and email != ''">email,</if><if test="status != null">status,</if><if test="createBy != null and createBy != ''">create_by,</if>create_time)values(<if test="deptId != null and deptId != 0">#{deptId},</if><if test="parentId != null and parentId != 0">#{parentId},</if><if test="deptName != null and deptName != ''">#{deptName},</if><if test="ancestors != null and ancestors != ''">#{ancestors},</if><if test="orderNum != null">#{orderNum},</if><if test="leader != null and leader != ''">#{leader},</if><if test="phone != null and phone != ''">#{phone},</if><if test="email != null and email != ''">#{email},</if><if test="status != null">#{status},</if><if test="createBy != null and createBy != ''">#{createBy},</if>sysdate())</insert><update id="updateDept" parameterType="SysDept">update sys_dept<set><if test="parentId != null and parentId != 0">parent_id = #{parentId},</if><if test="deptName != null and deptName != ''">dept_name = #{deptName},</if><if test="ancestors != null and ancestors != ''">ancestors = #{ancestors},</if><if test="orderNum != null">order_num = #{orderNum},</if><if test="leader != null">leader = #{leader},</if><if test="phone != null">phone = #{phone},</if><if test="email != null">email = #{email},</if><if test="status != null and status != ''">status = #{status},</if><if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>update_time = sysdate()</set>where dept_id = #{deptId}</update><update id="updateDeptChildren" parameterType="java.util.List">update sys_dept set ancestors =<foreach collection="depts" item="item" index="index"separator=" " open="case dept_id" close="end">when #{item.deptId} then #{item.ancestors}</foreach>where dept_id in<foreach collection="depts" item="item" index="index"separator="," open="(" close=")">#{item.deptId}</foreach></update><update id="updateDeptStatusNormal" parameterType="Long">update sys_dept set status = '0' where dept_id in<foreach collection="array" item="deptId" open="(" separator="," close=")">#{deptId}</foreach></update><delete id="deleteDeptById" parameterType="Long">update sys_dept set del_flag = '2' where dept_id = #{deptId}</delete></mapper>
多表联查
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.system.mapper.SysRoleMapper"><resultMap type="SysRole" id="SysRoleResult"><id property="roleId" column="role_id" /><result property="roleName" column="role_name" /><result property="roleKey" column="role_key" /><result property="roleSort" column="role_sort" /><result property="dataScope" column="data_scope" /><result property="menuCheckStrictly" column="menu_check_strictly" /><result property="deptCheckStrictly" column="dept_check_strictly" /><result property="status" column="status" /><result property="delFlag" column="del_flag" /><result property="createBy" column="create_by" /><result property="createTime" column="create_time" /><result property="updateBy" column="update_by" /><result property="updateTime" column="update_time" /><result property="remark" column="remark" /></resultMap>// 多表查询<sql id="selectRoleVo">select distinct r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.menu_check_strictly, r.dept_check_strictly,r.status, r.del_flag, r.create_time, r.remark from sys_role rleft join sys_user_role ur on ur.role_id = r.role_idleft join sys_user u on u.user_id = ur.user_idleft join sys_dept d on u.dept_id = d.dept_id</sql><select id="selectRoleList" parameterType="SysRole" resultMap="SysRoleResult"><include refid="selectRoleVo"/>where r.del_flag = '0'<if test="roleId != null and roleId != 0">AND r.role_id = #{roleId}</if><if test="roleName != null and roleName != ''">AND r.role_name like concat('%', #{roleName}, '%')</if><if test="status != null and status != ''">AND r.status = #{status}</if><if test="roleKey != null and roleKey != ''">AND r.role_key like concat('%', #{roleKey}, '%')</if><if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->and date_format(r.create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')</if><if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->and date_format(r.create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')</if><!-- 数据范围过滤 -->${params.dataScope}order by r.role_sort</select><select id="selectRolePermissionByUserId" parameterType="Long" resultMap="SysRoleResult"><include refid="selectRoleVo"/>WHERE r.del_flag = '0' and ur.user_id = #{userId}</select><select id="selectRoleAll" resultMap="SysRoleResult"><include refid="selectRoleVo"/></select><select id="selectRoleListByUserId" parameterType="Long" resultType="Long">select r.role_idfrom sys_role rleft join sys_user_role ur on ur.role_id = r.role_idleft join sys_user u on u.user_id = ur.user_idwhere u.user_id = #{userId}</select><select id="selectRoleById" parameterType="Long" resultMap="SysRoleResult"><include refid="selectRoleVo"/>where r.role_id = #{roleId}</select><select id="selectRolesByUserName" parameterType="String" resultMap="SysRoleResult"><include refid="selectRoleVo"/>WHERE r.del_flag = '0' and u.user_name = #{userName}</select><select id="checkRoleNameUnique" parameterType="String" resultMap="SysRoleResult"><include refid="selectRoleVo"/>where r.role_name=#{roleName} and r.del_flag = '0' limit 1</select><select id="checkRoleKeyUnique" parameterType="String" resultMap="SysRoleResult"><include refid="selectRoleVo"/>where r.role_key=#{roleKey} and r.del_flag = '0' limit 1</select><insert id="insertRole" parameterType="SysRole" useGeneratedKeys="true" keyProperty="roleId">insert into sys_role(<if test="roleId != null and roleId != 0">role_id,</if><if test="roleName != null and roleName != ''">role_name,</if><if test="roleKey != null and roleKey != ''">role_key,</if><if test="roleSort != null">role_sort,</if><if test="dataScope != null and dataScope != ''">data_scope,</if><if test="menuCheckStrictly != null">menu_check_strictly,</if><if test="deptCheckStrictly != null">dept_check_strictly,</if><if test="status != null and status != ''">status,</if><if test="remark != null and remark != ''">remark,</if><if test="createBy != null and createBy != ''">create_by,</if>create_time)values(<if test="roleId != null and roleId != 0">#{roleId},</if><if test="roleName != null and roleName != ''">#{roleName},</if><if test="roleKey != null and roleKey != ''">#{roleKey},</if><if test="roleSort != null">#{roleSort},</if><if test="dataScope != null and dataScope != ''">#{dataScope},</if><if test="menuCheckStrictly != null">#{menuCheckStrictly},</if><if test="deptCheckStrictly != null">#{deptCheckStrictly},</if><if test="status != null and status != ''">#{status},</if><if test="remark != null and remark != ''">#{remark},</if><if test="createBy != null and createBy != ''">#{createBy},</if>sysdate())</insert><update id="updateRole" parameterType="SysRole">update sys_role<set><if test="roleName != null and roleName != ''">role_name = #{roleName},</if><if test="roleKey != null and roleKey != ''">role_key = #{roleKey},</if><if test="roleSort != null">role_sort = #{roleSort},</if><if test="dataScope != null and dataScope != ''">data_scope = #{dataScope},</if><if test="menuCheckStrictly != null">menu_check_strictly = #{menuCheckStrictly},</if><if test="deptCheckStrictly != null">dept_check_strictly = #{deptCheckStrictly},</if><if test="status != null and status != ''">status = #{status},</if><if test="remark != null">remark = #{remark},</if><if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>update_time = sysdate()</set>where role_id = #{roleId}</update><delete id="deleteRoleById" parameterType="Long">update sys_role set del_flag = '2' where role_id = #{roleId}</delete><delete id="deleteRoleByIds" parameterType="Long">update sys_role set del_flag = '2' where role_id in<foreach collection="array" item="roleId" open="(" separator="," close=")">#{roleId}</foreach> </delete></mapper>
字典信息
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.system.mapper.SysDictDataMapper"><resultMap type="SysDictData" id="SysDictDataResult"><id property="dictCode" column="dict_code" /><result property="dictSort" column="dict_sort" /><result property="dictLabel" column="dict_label" /><result property="dictValue" column="dict_value" /><result property="dictType" column="dict_type" /><result property="cssClass" column="css_class" /><result property="listClass" column="list_class" /><result property="isDefault" column="is_default" /><result property="status" column="status" /><result property="createBy" column="create_by" /><result property="createTime" column="create_time" /><result property="updateBy" column="update_by" /><result property="updateTime" column="update_time" /></resultMap><sql id="selectDictDataVo">select dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark from sys_dict_data</sql><select id="selectDictDataList" parameterType="SysDictData" resultMap="SysDictDataResult"><include refid="selectDictDataVo"/><where><if test="dictType != null and dictType != ''">AND dict_type = #{dictType}</if><if test="dictLabel != null and dictLabel != ''">AND dict_label like concat('%', #{dictLabel}, '%')</if><if test="status != null and status != ''">AND status = #{status}</if></where>order by dict_sort asc</select><select id="selectDictDataByType" parameterType="SysDictData" resultMap="SysDictDataResult"><include refid="selectDictDataVo"/>where status = '0' and dict_type = #{dictType} order by dict_sort asc</select><select id="selectDictLabel" resultType="String">select dict_label from sys_dict_datawhere dict_type = #{dictType} and dict_value = #{dictValue}</select><select id="selectDictDataById" parameterType="Long" resultMap="SysDictDataResult"><include refid="selectDictDataVo"/>where dict_code = #{dictCode}</select><select id="countDictDataByType" resultType="Integer">select count(1) from sys_dict_data where dict_type=#{dictType} </select><delete id="deleteDictDataById" parameterType="Long">delete from sys_dict_data where dict_code = #{dictCode}</delete><delete id="deleteDictDataByIds" parameterType="Long">delete from sys_dict_data where dict_code in<foreach collection="array" item="dictCode" open="(" separator="," close=")">#{dictCode}</foreach> </delete><update id="updateDictData" parameterType="SysDictData">update sys_dict_data<set><if test="dictSort != null">dict_sort = #{dictSort},</if><if test="dictLabel != null and dictLabel != ''">dict_label = #{dictLabel},</if><if test="dictValue != null and dictValue != ''">dict_value = #{dictValue},</if><if test="dictType != null and dictType != ''">dict_type = #{dictType},</if><if test="cssClass != null">css_class = #{cssClass},</if><if test="listClass != null">list_class = #{listClass},</if><if test="isDefault != null and isDefault != ''">is_default = #{isDefault},</if><if test="status != null">status = #{status},</if><if test="remark != null">remark = #{remark},</if><if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>update_time = sysdate()</set>where dict_code = #{dictCode}</update><update id="updateDictDataType" parameterType="String">update sys_dict_data set dict_type = #{newDictType} where dict_type = #{oldDictType}</update><insert id="insertDictData" parameterType="SysDictData">insert into sys_dict_data(<if test="dictSort != null">dict_sort,</if><if test="dictLabel != null and dictLabel != ''">dict_label,</if><if test="dictValue != null and dictValue != ''">dict_value,</if><if test="dictType != null and dictType != ''">dict_type,</if><if test="cssClass != null and cssClass != ''">css_class,</if><if test="listClass != null and listClass != ''">list_class,</if><if test="isDefault != null and isDefault != ''">is_default,</if><if test="status != null">status,</if><if test="remark != null and remark != ''">remark,</if><if test="createBy != null and createBy != ''">create_by,</if>create_time)values(<if test="dictSort != null">#{dictSort},</if><if test="dictLabel != null and dictLabel != ''">#{dictLabel},</if><if test="dictValue != null and dictValue != ''">#{dictValue},</if><if test="dictType != null and dictType != ''">#{dictType},</if><if test="cssClass != null and cssClass != ''">#{cssClass},</if><if test="listClass != null and listClass != ''">#{listClass},</if><if test="isDefault != null and isDefault != ''">#{isDefault},</if><if test="status != null">#{status},</if><if test="remark != null and remark != ''">#{remark},</if><if test="createBy != null and createBy != ''">#{createBy},</if>sysdate())</insert></mapper>