Mybatis----实现多表查询

一.Mybatis实现多表查询

二.使用<resultMap>查询关联单个对象(N+1)和(联合查询方式)------<association>

<resultMap type="teacher" id="mymap">
    <!-- 主键使用 id 标签配置映射关系 -->
    <id column="id" property="id1" />
    <!-- 其他列使用 result 标签配置映射关系 -->
    <result column="name" property="name1"/>
</resultMap>
<select id="selAll" resultMap="mymap">
    select * from teacher
</select>

<resultMap type="student" id="stuMap">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="age" column="age"/>
    <result property="tid" column="tid"/>
    <!-- 如果关联一个对象 -->
    <association property="teacher" select="com.bjsxt.mapper.TeacherMapper.selById"
     column="tid"></association>
</resultMap>
<select id="selAll" resultMap="stuMap">
    select * from student
</select>

4.3.3.6 把上面代码简化成

<resultMap type="student" id="stuMap">
    <result column="tid" property="tid"/>
    <!-- 如果关联一个对象 -->
    <association property="teacher"
    select="com.bjsxt.mapper.TeacherMapper.selById"
    column="tid"></association>
</resultMap>
<select id="selAll" resultMap="stuMap">
    select * from student
</select>

<resultMap type="Student" id="stuMap1">
    <id column="sid" property="id"/>
    <result column="sname" property="name"/>
    <result column="age" property="age"/>
    <result column="tid" property="tid"/>
    <association property="teacher" javaType="Teacher" >
        <id column="tid" property="id"/>
        <result column="tname" property="name"/>
    </association>
</resultMap>
<select id="selAll1" resultMap="stuMap1">
    select s.id sid,s.name sname,age age,t.id tid,t.name tname FROM student s left outer join teacher t on s.tid=t.id
</select>

三.使用<resultMap>查询关联集合对象(N+1)-----------<collection>

    使用<resultMap>查询关联集合对象(联合方式)-----------<collection>

<resultMap type="teacher" id="mymap1">
    <id column="tid" property="id"/>
    <result column="tname" property="name"/>
    <collection property="list" ofType="student" >
        <id column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="age" property="age"/>
        <result column="tid" property="tid"/>
    </collection>
</resultMap>
<select id="selAll1" resultMap="mymap1">
    select t.id tid,t.name tname,s.id sid,s.name sname,age,tid from teacher t LEFT JOIN student s on t.id=s.tid;
</select>

四.使用 Auto Mapping 结合别名实现多表查询-----只适用于查询联合单个对象,不能查询关联集合(只能使用<collection>)

----------使用返单引号     ``

 

<select id="selAll" resultType="student">
    select t.id `teacher.id`,t.name `teacher.name`,s.id id,s.name name,age,tid from student s LEFT JOIN teacher t on t.id=s.tid
</select>