Mybatis三表连接查询

医生表、职务表、部门表

方法1：ResultMap

 // 医生职务类
 @Data
 @AllArgsConstructor
 @NoArgsConstructor
 public class Duty {
     private Integer d_id;
     private String d_name;
 }
 ​
 // 医生部门类
 @Data
 @AllArgsConstructor
 @NoArgsConstructor
 public class Work {
     private Integer w_id;
     private String w_name;
 }
 ​
 // 医生类
 @Data
 @AllArgsConstructor
 @NoArgsConstructor
 public class Doctor {
     private Integer d_id;
     private String d_name;
     private String d_gender;
     private Integer d_age;
     private String d_img;
     private String d_desc;
     private String d_best;
     private Work work;
     private String d_working;
     private Duty duty;
 }

需求：根据部门id查询，获取该职务的所有医生的所有信息

 /**
   * 根据部门id获取，该部门的所有医生
   * @param w_id 部门id
   * @return 所有医生的对象
   */
 List<Doctor> getDoctorByWorkID(int w_id);

实现类

 <select id="getWork" resultType="work">
     select *
     from hospital.work w
     where w.w_id = #{d_work_id};
 </select>
 ​
 <select id="getDuty" resultType="duty">
     select *
     from hospital.duty d
     where d.d_id = #{w_id};
 </select>
 ​
 <!--结果集映射-->
 <resultMap id="doctor_union_work_and_duty" type="doctor">
     <association property="work" column="d_work_id" javaType="Work" select="getWork"/>
     <association property="duty" column="d_duty" javaType="Duty" select="getDuty"/>
 </resultMap>
 ​
 <select id="getDoctorByWorkID" resultType="doctor" parameterType="_int" resultMap="doctor_union_work_and_duty">
     select *
     from hospital.doctor d
     inner join hospital.work w
     on w.w_id = d.d_work_id
     where d_working = '1'
     and d_work_id = #{w_id};
 </select>

方法2：数据库视图

 @Data
 @AllArgsConstructor
 @NoArgsConstructor
 public class Doctor {
     private Integer doc_id;
     private String doc_name;
     private String doc_gender;
     private Integer doc_age;
     private String doc_img;
     private String doc_desc;
     private String doc_best;
     // 职务
     private Integer w_id;
     private String w_name;
     // 部门
     private Integer d_id;
     private String d_name;
 }

需求：根据部门id查询，获取该职务的所有医生的所有信息

 /**
   * 根据部门id获取，该部门的所有医生
   * @param w_id 部门id
   * @return 所有医生的对象
   */
 List<Doctor> getDoctorByWorkID(int w_id);

创建视图（注意多表中有字段重复，会出现报错）

 create view doctor_work_duty as
 (select *
 from hospital.doctor t1
 inner join hospital.work t2
 on t1.doc_work_id = t2.w_id
 inner join hospital.duty t3
 on t1.doc_duty = t3.d_id);

实现类

 <select id="getDoctorByID" resultType="doctor" parameterType="_int">
     select *
     from doctor_work_duty
     where doc_id = #{id}
     and doc_working = '1';
 </select>