2021-11-03

用户和部门一对多关系。

// An highlighted block

	<resultMap type="SysUser" id="SysUserResult">
		<id     property="userId"       column="user_id"      />
		<result property="deptId"       column="dept_id"      />
		<result property="userName"     column="user_name"    />
		<collection  property="roles"   javaType="java.util.List"        resultMap="RoleResult" />
		<collection  property="depts"   javaType="java.util.List" ofType="deptDirectory" select="selectDeptDirectoryByUserId" column="user_id"/>
	</resultMap>
	

首先查出一对多中这个“一”的数据，然后再查“多”的数据。
select=“selectDeptDirectoryByUserId” 这个就是子查询。
ofType=“deptDirectory” 设置查询数据对应实体类。
type=“DeptDirectory” 对应 resultMap=“DeptResult” 数据库表和实体类的映射关系。

<!--科室目录-->
	<resultMap id="DeptResult" type="DeptDirectory">
		<id     property="deptId"   column="dept_id"     />
		<result property="deptName" column="dept_name"   />
		<result property="deptCode"   column="dept_code" />
		<result property="deptAttribution" column="dept_attribution"   />
		<result property="deptTypeCode"   column="dept_type_code" />
		<result property="deptPlaceCode" column="dept_place_code"   />
		<result property="deptMedCode"   column="dept_med_code" />
	</resultMap>

<!--查询用户-->
	<sql id="selectUserDeptVo">
		select user_id , user_name, nick_name, email, avatar, phonenumber, password, sex, status,
		       del_flag, login_ip, login_date, create_by, create_time, remark,
			   medical_practitioner_num,emp_number,inservice_status,appointment,job_level
		from sys_user
	</sql>
<!--人员列表查询-->
	<select id="selectEmpList" parameterType="SysUser" resultMap="SysUserResult">
		<include refid="selectUserDeptVo"/>
		where del_flag=0
		<!-- 根据员工姓名、工号模糊查询 -->
		<if test="keyword != null and keyword != ''">
			and user_name  like concat('%', #{keyword} , '%')
			or  emp_number like concat('%', #{keyword}, '%')
		</if>
		<if test="userId != null and userId != ''">
		   and	user_id = #{userId}
		</if>
		order by user_id asc
	</select>

<!--查询用户-部门的一对多关系-->
	<select id="selectDeptDirectoryByUserId" resultMap="DeptResult">
		select d.dept_id dept_id, dept_code , dept_name , dept_attribution, dept_type_code, dept_place_code, dept_med_code from dept_directory d
		inner join sys_user_dept ud on ud.dept_id = d.dept_id and ud.user_id = #{user_id}
	</select>