mybatis表关联查询

作三张表的关联查询

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;
	}
      
}