MyBatis基于注解和xxxxMapper.xml文件的多表联合

基于注解的时候没有xxxMapper.xml文件
要把配置文件中要改：

 <mappers>
        <mapper class="dao.IPerson"></mapper>
    </mappers>

基于注解的 一对一查询

package dao;

import domain.Idcard;
import domain.Person;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

public interface IPerson {
    /*基于注解的方式*/
    //根据人的id查询人的信息+身份证号  自定义规则查询
    @Select("select * from person where pid=#{pid}")
    @Results( /*<resultMap id="selectPerson" type="domain.Person">*/
            id = "selectOne",
            value ={
            /*字段名，id为true时表示是主键*/
            @Result(property="pid",column ="pid",id = true),
            @Result(property="pname",column ="pname"),
            // <association property="idcard" javaType="domain.Idcard"
            // column="cardid" select="selectIdcardId"></association>
            //javaType可以省略
            @Result(property ="idCard",javaType = Idcard.class,column = "cardid",
                    one =@One(select = "selectCard",
                    fetchType = FetchType.LAZY//fetchType相当于setting设置第二个对象为懒加载机制
            ))
        }
    )
    public Person selectOne(Integer pid);
    //第二次查询的方法
    @Select("select * from idcard where cardid=#{cardid}")
    public Idcard selectCard(int cardid);

    //查询多条记录
    @Select("select * from person")
    @ResultMap("selectOne")//返回值类型，在返回多条记录使用，和results中的id对应
    public List<Person> selectAll();

}

基于注解的一对多查询

package dao;

import domain.Dept;
import domain.Emp;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

public interface IDept {

    //查询部门下的员工信息
    @Select("select * from dept where deptno=#{deptno}")
    @Results(
            id = "selectDept",
            value = {
              @Result(property = "deptno",column = "deptno",id = true),
              @Result(property = "dname",column = "dname"),
              @Result(property = "loc",column = "loc"),
              @Result(property = "empList",column = "deptno",
                      many=@Many(select = "selectEmp"))
            }

    )
    public List<Dept> selectOne(int deptno);

    //辅助方法
    @Select("select * from emp where deptno=#{deptno}")
    @ResultMap("selectDept")
    public Emp selectEmp(int deptno);
}

基于注解的多对多查询

注：tea_stu：存学生编号和老师编号，是一个中间表

package dao;

import domain.Student;
import domain.Teacher;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface IStudent {
    //通过id查询一个学生的信息+学生所上课程的所有老师信息
    @Results(
            id = "selectStudent",
            value = {
                    @Result(property = "sid",column = "sid",id = true),
                    @Result(property = "sname",column = "sname"),
                    @Result(property = "ssex",column = "ssex"),
                    @Result(property = "sage",column = "sage"),
                    @Result(property = "teacherList",column = "sid",
                    javaType = List.class,many=@Many(select = "selectTeacher")),
            }
    )
    @Select("select * from student where sid=#{sid}")
    public Student selectOne(int sid);
    //辅助方法，负责通过学生的id找到teacher
    @Select("select t.* from tea_stu ts inner join teacher t on ts.tis=t.tid where ts.sid=#{sid}")
    public Teacher selectTeacher(int sid);

    //负责查询所有学生+学过课程的所有老师
    @Select("select * from student")
    @ResultMap("selectStudent")
    public List<Student> selectAll();

}

基于xxxMapper.xml文件的一对一查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.PersonDao">
    <!--方法一：采用两次查询的方法-->
   <!-- <resultMap id="selectPerson" type="domain.Person">
        <id property="pid" column="pid"></id>
        <result property="pname" column="pname"></result>
        <association property="idcard"  javaType="domain.Idcard" column="cardid" select="selectIdcardId"></association>
    </resultMap>
    <select id="selectIdcardId" resultType="domain.Idcard">
            select * from idcard where cardid=#{idcard}
    </select>

    <select id="selectOne" resultMap="selectPerson">
        select pid,pname,cardid from person where pid=#{pid};
    </select>
    <select id="selectAll" resultMap="selectPerson">
        select pid,pname,cardid from person;
    </select>
    -->

    <!--方法二：采用多表联合查询的语句查询，如：等值连接  内连接  外连接-->
    <resultMap id="selectPerson" type="domain.Person">
            <id property="pid" column="pid"></id>
            <result property="pname" column="pname"></result>
            <association property="idcard" javaType="domain.Idcard">
                <id property="cardid" column="cardid"></id>
                <result property="address" column="address"></result>
            </association>
    </resultMap>

    <select id="selectOne" resultMap="selectPerson">
        select p.pid,p.pname,i.cardid from person p inner join idcard i  on p.cardid=i.cardid where p.pid=#{pid}

    </select>
    <select id="selectAll" resultMap="selectPerson">
        select p.pid,p.pname,i.cardid,i.address from person p inner join idcard i on p.cardid=i.cardid

    </select>

</mapper>

基于xxxMapper.xml文件的一对多查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.DeptDao">
    <!--<resultMap id="selectDept" type="domain.Dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        &lt;!&ndash;如果属性为集合则用collection&ndash;&gt;
        <collection property="empList" javaType="list" ofType="domain.Emp"
                    select="selectEmp" column="deptno"></collection>
    </resultMap>
    <select id="selectEmp" resultType="domain.Emp">
        select empno,ename,sal,deptno from emp where deptno=#{deptno}
    </select>

    <select id="selectOne" resultMap="selectDept">
        select deptno,dname,loc from dept where deptno=#{deptno}
    </select>
    <select id="selectAll" resultMap="selectDept">
        select deptno,dname,loc from dept
    </select>-->
    <!--方式二，联合查询-->
    <resultMap id="selectDept" type="domain.Dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <!--collection相当于一个对象，然后给对象进行赋值-->
        <collection property="empList" javaType="list" ofType="domain.Emp">
            <id property="empno" column="empno"></id>
            <result property="ename" column="ename"></result>
            <result property="sal" column="sal"></result>
        </collection>
    </resultMap>

    <select id="selectOne" resultMap="selectDept">
       select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from dept d left join emp e on e.deptno=d.deptno where d.deptno=#{deptno}
    </select>
    <select id="selectAll" resultMap="selectDept">
        select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from dept d left join emp e on e.deptno=d.deptno
    </select>
</mapper>

基于xxxMapper.xml文件的多对多查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.TeacherDao">
    <!--方式一 先查老师再查学生 但是第二次查询学生信息的时候需要两张表联合-->
    <!-- <resultMap id="selectTeacher" type="domain.Teacher">
         <id property="tid" column="tid"></id>
         <result property="tname" column="tname"></result>
         <result property="tsex" column="tsex"></result>
         <result property="tage" column="tage"></result>
         <collection property="studentList" javaType="list" ofType="domain.Student"
         select="selectTeacherForStudent" column="tid"></collection>
     </resultMap>
    <select id="selectTeacherForStudent" resultType="domain.Student">
        select s.* from tea_stu ts inner join student s on ts.sid=s.sid where ts.tid=#{tid}
    </select>
    <select id="selectOne" resultMap="selectTeacher">
        select tid,tname,tsex,tage from teacher where tid=#{tid}
    </select>-->


    <!--方式二 一次三表联合查询 将老师和学生信息都获取到了 可以直接赋值，不需要再查询-->
    <resultMap id="selectTeacher" type="domain.Teacher">
             <id property="tid" column="tid"></id>
             <result property="tname" column="tname"></result>
             <result property="tsex" column="tsex"></result>
             <result property="tage" column="tage"></result
             <collection property="studentList" javaType="list" ofType="domain.Student">
                 <id property="sid" column="sid"></id>
                 <result property="sname" column="sname"></result>
                 <result property="ssex" column="ssex"></result>
                 <result property="sage" column="sage"></result>
             </collection>
    </resultMap>
    <select id="selectOne" resultMap="selectTeacher">
        select t.*,s.* from teacher t inner join  tea_stu ts on t.tid=ts.tid
            inner join student s on ts.sid=s.sid where t.tid=#{tid}
    </select>
</mapper>