Mybatis多表查询及结果封装

MyBatis多表查询及结果封装

1 多表查询（单层、多层嵌套）

1.1 一对多关系

一个Coronary对应多个 CoronaryDetail

private List<CoronaryDetail> coronaryDetailList;

 public List<CoronaryDetail> getCoronaryDetailList() {
        return coronaryDetailList;
 }
  public void setCoronaryDetailList(List<CoronaryDetail> coronaryDetailList) {
        this.coronaryDetailList = coronaryDetailList;
 }

一个CoronaryDetail又对应多个CoronaryEquipmentDetail，同时还对应一个coronaryInterveneDetail

  private CoronaryInterveneDetail coronaryInterveneDetail;
  private List<CoronaryEquipmentDetail> coronaryEquipmentDetailList;

 public CoronaryInterveneDetail getCoronaryInterveneDetail() {
        return coronaryInterveneDetail;
    }
 public void setCoronaryInterveneDetail(CoronaryInterveneDetail coronaryInterveneDetail) {
        this.coronaryInterveneDetail = coronaryInterveneDetail;
    }
  public List<CoronaryEquipmentDetail> getCoronaryEquipmentDetailList() {
        return coronaryEquipmentDetailList;
    }
    public void setCoronaryEquipmentDetailList(List<CoronaryEquipmentDetail>    coronaryEquipmentDetailList) {
        this.coronaryEquipmentDetailList = coronaryEquipmentDetailList;
    }

查询时 传入Coronary的accessionNumber，再根据Coronary的Index获取coronaryDetailList

Mapper接口写法

Coronary selectByAN(@Param("accessionNumber") String accessionNumber);

Mapper.xml

Coronary 这边是一个select连接 property 对应的bean中的coronaryDetailList对象， column对应的是传入的参数，select对应的连接的方法(不必在接口中定义),缺少column会报错,结果封装在resultMap中

 <resultMap id="BaseResultMap" type="com.angiocarpy.system.entity.Coronary">
    <result column="CoronaryIndex" jdbcType="INTEGER" property="coronaryindex" />
    <result column="AccessionNumber" jdbcType="VARCHAR" property="accessionnumber" />
 
    <collection property="coronaryDetailList" column="CoronaryIndex" ofType="com.angiocarpy.system.entity.CoronaryDetail"
                javaType="ArrayList" select="getCoronaryDetailList"></collection>
  </resultMap>

CoronaryDetail association连接一个一对一的coronaryInterveneDetail

 <resultMap id="CoronaryDetailMap" type="com.angiocarpy.system.entity.CoronaryDetail">
    <result column="DetailIndex" jdbcType="INTEGER" property="detailindex" />
    <result column="CoronaryIndex" jdbcType="INTEGER" property="coronaryindex" />
    <association property="coronaryInterveneDetail" javaType="com.angiocarpy.system.entity.CoronaryInterveneDetail" >
      <result column="DetailIndex" jdbcType="INTEGER" property="detailindex" />
      <result column="CoronaryDetailIndex" jdbcType="INTEGER" property="coronarydetailindex" />
    </association>

    <collection property="coronaryEquipmentDetailList" ofType="com.angiocarpy.system.entity.CoronaryEquipmentDetail" >
      <result column="DetailIndex" jdbcType="INTEGER" property="detailindex" />
      <result column="CoronaryDetailIndex" jdbcType="INTEGER" property="coronarydetailindex" />
      <result column="EquipmentIndex" jdbcType="INTEGER" property="equipmentindex" />
    </collection>
  </resultMap>		

查询语句

WHERE a.CoronaryIndex=#{CoronaryIndex} CoronaryIndex是select传入的参数，这边用到左连接，因为一个CoronaryDetail对应的CoronaryEquipmentDetail和coronaryInterveneDetail有可能不存在。用左连接就可以得到CoronaryDetail且包含空的CoronaryEquipmentDetail和coronaryInterveneDetail对象。

<select id="selectByAN" resultMap="BaseResultMap">
    SELECT * FROM Coronary WHERE AccessionNumber = #{accessionNumber}
  </select>

  <select id="getCoronaryDetailList" resultMap="CoronaryDetailMap">
    SELECT a.*,b.*,c.* from CoronaryDetail as a
    LEFT JOIN CoronaryEquipmentDetail as b ON a.DetailIndex = b.CoronaryDetailIndex
    LEFT JOIN CoronaryInterveneDetail as c on a.DetailIndex = c.CoronaryDetailIndex
    WHERE a.CoronaryIndex=#{CoronaryIndex}
  </select>

1.2 多对一的关系

一份报告对应一个病人，一个病人可以有多个报告

bean的Report 添加病人属性

private Patient patient;

    public Patient getPatient() {
        return patient;
    }
    public void setPuatiens(Patient patient) {
        this.patients = patient;
    }

Report.xml

WHERE 1=1 防止传入的参数为空报错

 <resultMap id="BaseResultMap" type="com.angiocarpy.system.entity.CISReport">
    <result column="ReportIndex" jdbcType="INTEGER" property="reportindex" />
    <result column="AccessionNumber" jdbcType="VARCHAR" property="accessionnumber" />
   
    <association property="pubPatients" javaType="com.angiocarpy.system.entity.PubPatients" >
      <result column="PatientsIndex" jdbcType="INTEGER" property="patientsindex" />
      <result column="PatientsName" jdbcType="VARCHAR" property="patientsname" />
      <result column="PatientsAlias" jdbcType="VARCHAR" property="patientsalias" />
      <result column="PatientsID" jdbcType="VARCHAR" property="patientsid" />
    </association>
  </resultMap>

 <select id="selectByTime" resultMap="BaseResultMap">
    SELECT cis.*,  p.* FROM CISReport as cis, Pub_Patients as p
     WHERE 1=1
     <if test="starttime !=null and starttime!='' ">
      AND cis.StudyDate &gt;= CONVERT(DATETIME, #{starttime}, 102)
     </if>
    <if test="endtime !=null and endtime!='' ">
      AND cis.StudyDate &lt;= CONVERT(DATETIME, #{endtime}, 126)
    </if>
    <if test="patientName !=null and  patientName!='' ">
      AND p.PatientsName = #{patientName}
    </if>
    <if test="accessionNumber !=null and  accessionNumber!='' ">
      AND cis.AccessionNumber = #{accessionNumber}
    </if>
     AND cis.PatientIndex=p.PatientsIndex;
  </select>