作三张表的关联查询
cfm_requirement_vendor_date为cfm_requirement_vendor的子表
cfm_requirement_vendor为cfm_requirement的子表
一、mybatis映射文件定义
<resultMap id="NestedRequirementResultMap"
type="com.NestedRequirement">
<result property="id" column="id" />
<result property="requirementId" column="requirement_id" />
<collection property="requirementVendorList"
ofType="com.NestedRequirementVendor">
<result property="id" column="requirement_vendor_id" />
<result property="cfmRequirementId" column="cfm_requirement_id" />
<result property="vendorId" column="vendor_id" />
<collection property="requirementVendorDateList"
ofType="com.RequirementVendorDate">
<result property="id" column="requirement_vendor_date_id" />
</collection>
</collection>
</resultMap>
<!-- 通过requirementId查询 -->
<select id="selectNestedByRequirementId" resultMap="NestedRequirementResultMap">
SELECT
r.id,
r.requirement_id,
v.id as requirement_vendor_id,
v.vendor_id,
d.id as requirement_vendor_date_id,
FROM
cfm_requirement r
JOIN cfm_requirement_vendor v ON r.id = v.cfm_requirement_id
AND v.del_flag = 0
join cfm_requirement_vendor_date d on v.id = d.cfm_requirement_vendor_id and d.del_flag=0
WHERE
r.requirement_id = #{requirementId} and r.del_flag=0
</select>
二、java类定义
1、mapper.java
List<NestedRequirement> selectNestedByRequirementId(int requirementId);
2、持久化对象
public class NestedRequirement extends Requirement {
private List<NestedRequirementVendor> requirementVendorList;
public List<NestedRequirementVendor> getRequirementVendorList() {
return requirementVendorList;
}
public void setRequirementVendorList(
List<NestedRequirementVendor> requirementVendorList) {
this.requirementVendorList = requirementVendorList;
}
}