mybatis级联查询，子查询多条件，且参数包含父查询没有的字段

mybaits中的级联查询，一对一，一对多等，有两种情况

1.一个参数，一般来说，这个参数是子查询与父查询的共有字段

mapper.xml

 <resultMap id="ProjectResult" type="com.spms.entity.CompleteProject">
        <result column="projid" jdbcType="NUMERIC" javaType="int" property="id"/>
        <result column="deptid" property="deptId"/>
        <result column="id_" property="applyUserId"/>
        <result column="projapplytype" property="subjectId"/>
        <result column="projcode" property="code"/>
        <result column="projname" property="name"/>
        <result column="projcreatedate" property="createDate"/>
        <result column="projconstructdate" property="constructDate"/>
       

        <association property="owner" column="id_" javaType="User" select="com.spms.dao.UserDao.getUserById"/>
        <association property="preparer" column="projdraftpreparer" javaType="com.spms.entity.User" select="com.spms.dao.UserDao.getUserById"/>

        <association property="subject" column="projapplytype" select="com.spms.dao.SubjectDao.getSubjectById"/>
        <association property="constructDepartment" column="deptId"  select="com.spms.dao.DepartmentDao.getConstructDepartmentById"/>
        <association property="relevantDepartment" column="relevantdept"  select="com.spms.dao.DepartmentDao.getRelevantDepartmentById"/>
</resultMap>

父查询：

<select id="getById" parameterType="int" resultMap="ProjectResult">
        select
        <include refid="PROJECT_BASE"/>
        from project p
        <where>
            p.projid= #{projectId}
        </where>
    </select>

子查询：（举其中个例）

 <select id="getRelevantDepartmentById" parameterType="integer" resultMap="Department">
        select
        <include refid="Base_Column_List" />
        from department
        where deptid = #{deptId}
    </select>

property:指定子查询到父查询类中的对象属性；

column:子查询条件，为共有字段，自动映射

select:指定到子查询的位置以及id名

2.子查询多参数，非共有字段子查询私有

mapper.xml

<collection property="equipmentLists" column="{projId=projID,listStatus=listStatus}" select="com.spms.dao.EquipmentDao.getEquipmentListsByProjectId" />
        <collection property="schedules" column="{projId=projID,scheStatus=scheStatus}" select="com.spms.dao.ProjectScheduleDao.getProjSchedulesByProjId"/>

父查询中添加临时列，以便映射到子查询中：

case when ('${scheStatus}' != '') then '' else '${scheStatus}' end as scheStatus,添加临时列

 <select id="getByproId" resultMap="ProjectResult">
        select
        <include refid="PROJECT_BASE"/>,
        case when ('${scheStatus}' != '') then '' else '${scheStatus}' end as scheStatus,
        case when ('${scheStatus}' != '') then '' else '${scheStatus}' end as listStatus
        from project
        where 1=1
        <if test="projId != null">
            and projid= #{projId}
        </if>
    </select>

子查询：

<select id="getEquipmentListsByProjectId" parameterType="java.util.Map" resultMap="equipmentListResult">
        select
        <include refid="Base_Column_EquipmentList" />
        from PROJEQPTLIST
        WHERE projID = #{projectId,jdbcType=INTEGER} and listStatus=#{listStatus,jdbcType=VARCHAR}
        order by annualSchedule
    </select>

子查询DAO：

List<EquipmentList> getEquipmentListsByProjectId(@Param("projectId") int projectID,@Param("listStatus")String listStatus);

父查询DAO：

CompleteProject getByproId(@Param("projId") int projectId,@Param("scheStatus") String scheStatus);

column:{projId=projId,listStatus=listStatus}

parameterType:java.util.Map   很必要，，