当前位置 博文首页 > 空手道丶大师兄的博客：Mybatis多表查询及结果封装
一个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>
一份报告对应一个病人，一个病人可以有多个报告
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 >= CONVERT(DATETIME, #{starttime}, 102)
</if>
<if test="endtime !=null and endtime!='' ">
AND cis.StudyDate <= CONVERT(DATETIME, #{endtime}, 126)
</if>
<if test=
