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 很必要,,