mybatis表关联查询_MySQL

作三张表的关联查询

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>		<result property="requirementId" column="requirement_id"></result>		<collection property="requirementVendorList" oftype="com.NestedRequirementVendor">		    <result property="id" column="requirement_vendor_id"></result> 			<result property="cfmRequirementId" column="cfm_requirement_id"></result>			<result property="vendorId" column="vendor_id"></result>			<collection property="requirementVendorDateList" oftype="com.RequirementVendorDate">				<result property="id" column="requirement_vendor_date_id"></result>			</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 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;	}      }</nestedrequirementvendor></nestedrequirementvendor></nestedrequirementvendor>