myBatis 多表查询

多表查询

mybatis-config.xml---2个表都要注册

    <!--每一个Mappper.XML都需要在MyBatis核心配置文件中注册-->
    <mappers>
        <mapper class="dao.StudentMapper"/>
        <mapper class="dao.TeacherMapper"/>
<!--<mapper class="dao.UserDao"/>-->
    </mappers>

<!--给类起别名-->
    <typeAliases>
    <typeAlias type="pojo.Student" alias="Student"/>
    <typeAlias type="pojo.Teacher" alias="Teacher"/>
      <!--<package name="pojo"/>-->
    </typeAliases>

方法一

由于继承可以获取父类的所有属性，可以把多表的其他属性放到继承类里面去，再用继承类来输出，就有类所有表的属性
主表

从表

直接在studentmapper.xml 填写正常的连表查询sql

注意：数据库不分大小写，大小写不同的相同属性的可能会只有1个得到数据库字段
可以在sql其中一个列后面添加别名

    <select id="getStudentInfo" resultType="entityEx.StudentEx">
        select
        student.id,
           student.name,
           student.teacherID teacher_id,
           teacher.id teacherId,
           teacher.name teacherName
        from
            student
            left join teacher on teacher.id = student.teacherID
    </select>

方法二在studentmapper.xml

<!--
思路：
1. 查出所有学生信息
2. 根据查询出来的学生的tid，寻找对应的老师   子查询
-->
    <select id="getALLTeacher" resultMap="StudentTeacher">
        select *
        from myBatis.student;
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="teacher_id" column="teacherID"/>
        <association property="teacher" column="teacherID" javaType="Teacher" select="getTeacherid"/>
    </resultMap>

    <select id="getTeacherid" resultType="Teacher">
        select *
        from myBatis.teacher where id=#{id};
    </select>

======================================================================================================================================================
or

方法三在studentmapper.xml

    <!--按照结果欣套处理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s,teacher t
        where s.tid = t.id;
    </select>
    
    <resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
    </resultMap>

增删改一定要sqlsession.commit