嵌套查询:
注:StudentVO中有Class类
当查询完所有student后,再用c_id去查对应的class(mybatis对c_id进行了去重优化,不必每个student都查一次class,但效率仍然比较低,所以不推荐数据大时使用)
<!--嵌套查询 查询效率低 要多查一轮-->
<select id="getStudent" resultMap="studentvoQTXC">
select * from student
</select>
<resultMap id="studentvoQTXC" type="com.example.test.qiantaoSelect.vo.StudentVO" autoMapping="true">
<association property="c" column="c_id" select="getClass" ></association>
</resultMap>
<select id="getClass" resultType="com.example.test.qiantaoSelect.entity.Class">
select * from class where id=#{id}
</select>
(推荐且常用)嵌套结果1:
<!--嵌套结果-->
<resultMap id="studentvo" type="com.example.test.qiantaoSelect.vo.StudentVO">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="age" column="age"></result>
<!--方式1:缺点是要写每个字段一一对应-->
<association property="c" >
<id property="id" column="c_id"></id>
<result property="name" column="cname"></result>
</association>
<!--方式2:自动映射 *在没有相同字段*时可以使用!!-->
<!-- <association property="c" autoMapping="true"></association>-->
</resultMap>
(常用)嵌套结果2:
注:ClassVO类中包含Student集合属性时
<select id="getAllClass" resultMap="allClassMap">
select c.* ,s.id as `sId`,s.name as `sName` from class c JOIN student s ON s.c_id=c.id
</select>
<resultMap id="allClassMap" type="com.example.test.qiantaoSelect.vo.ClassVO" >
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<collection property="students" ofType="com.example.test.qiantaoSelect.entity.Student" >
<id property="id" column="sId"></id>
<result property="name" column="sName"></result>
</collection>
</resultMap>