直接给个例子,该例子来自:http://www.cnblogs.com/xdp-gacl/p/4264440.html
<!--
37 方式二:嵌套查询:通过执行另外一个SQL映射语句来返回预期的复杂类型
38 SELECT * FROM class WHERE c_id=1;
39 SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id的值
40 -->
41 <select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
42 select * from class where c_id=#{id}
43 </select>
<!-- 使用resultMap映射实体类和字段之间的一一对应关系 -->
45 <resultMap type="me.gacl.domain.Classes" id="ClassResultMap2">
46 <id property="id" column="c_id"/>
47 <result property="name" column="c_name"/>
48 <association property="teacher" column="teacher_id" select="getTeacher"/>
49 </resultMap>
50
51 <select id="getTeacher" parameterType="int" resultType="me.gacl.domain.Teacher">
52 SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
53 </select>
这个例子中查询分了两次,其中第二条查询语句:
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
中的id,如果是一个参数,名字随便取,写成
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{yutao}
也是可以的。
原因参考:http://cczakai.iteye.com/blog/1276722
但是如果,需要传递的是多个参数。
片段一
<select id="selectOrderAllotByOrderId" parameterType="Integer" resultMap="findHandlerId">
select a.id oa_id,
schoolBranchId,
schoolCourseId,
channelId
from t_order_allot a left join t_sys_user b on a.handlerId = b.id
where orderId = #{orderId}
</select>
片段二
<resultMap type="com.zexiaoqiao.admin.model.OrderAllotInfo" id="findHandlerId">
<id property="id" column="oa_id"/>
<association property="courseInfo"
column="{id=channelId,courseId=schoolCourseId}" select="getSchool"/>
</resultMap>
片段三
<select id="getSchool" resultType="com.zexiaoqiao.admin.model.GeneralCourseInfo" parameterType="map">
<if test="id ==1">
SELECT
gs.id AS schoolId,
gs.name AS schoolName,
gc.id AS courseId,
gc.title AS courseName,
1 AS channelId,
"国际学校" AS channelName
FROM t_guoji_school gs INNER JOIN t_guoji_course gc ON gs.id = gc.school_id
where gc.id = #{courseId}
</if>
</select>
片段三中 两个参数 courseId与id,是希望从片段二中传过来。这里就需要对
association中column多参数配置
<association property="courseInfo"
column="{id=channelId,courseId=schoolCourseId}" select="getSchool"/>
{key=value},column中其实就是键值对的形式,key是传给下个sql的取值名称,value是片段一中sql查询的字段名。