Mybatis 多表查询

一对一

一个人一张身份证
card

public class Card {
    private Integer id;
    private String number;
    private Person person;

    public Card() {
    }

    public Card(Integer id, String number, Person person) {
        this.id = id;
        this.number = number;
        this.person = person;
    }

    @Override
    public String toString() {
        return "Card{" +
                "id=" + id +
                ", number='" + number + '\'' +
                ", person=" + person +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }
}

person

public class Person {
    private Integer id;
    private String name;
    private Integer age;

    public Person() {
    }

    public Person(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

mapper文件

public interface OneToOne {
    public List<Card> selectAll();
}

mapperXml文件

<!--全类名-->
<mapper namespace="com.多表代理.OneToOne"> 

    <resultMap id="oneToOne" type="com.一对一.Card">
        <id column="cid" property="id"/>
        <result column="number" property="number"/>
        <association property="person" javaType="com.一对一.Person">
            <id column="pid" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
        </association>
    </resultMap>
    <select id="selectAll" resultMap="oneToOne">
        SELECT card.id cid,number,person.id pid,name,age FROM card,person WHERE card.pid=person.id
    </select>

</mapper>

一对多

一个学生多个班
student

public class student {
    private Integer id;
    private String name;
    private Integer age;
//    private classes classes;
    private List<classes> classes;

    public student() {
    }

classes

public class classes {
    private Integer id;
    private String name;

    public classes() {
    }

oneToMany

public interface OneToMany {
    public List<student> selectAll();
}

oneToMany.xml

<mapper namespace="com.多表代理.OneToMany">

    <resultMap id="OneToMany" type="com.一对多.student">
        <id column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="age" property="age"/>
        <collection property="classes" ofType="com.一对多.classes">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="OneToMany">
        SELECT s.id sid,s.name sname,age,c.id cid,c.name cname FROM classes c,student2 s where c.id=s.cid
    </select>

</mapper>

多对多

一个学生对应多个课程，一个课程对应多个学生
student

public class student {
    private Integer id;
    private String name;
    private Integer age;
    private List<course> courses;

course

public class course {
    private Integer id;
    private String name;

    public course() {
    }

ManyToMany

public interface ManyToMany {
    public List<student> selectAll();
}

ManyToMany.xml

<mapper namespace="com.多表代理.ManyToMany">

    <resultMap id="ManyToMany" type="com.多对多.student">
        <id column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="age" property="age"/>
        <collection property="courses" ofType="com.多对多.course">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="ManyToMany">
        select c.id cid,c.name cname,s2.id sid,s2.age,s2.name sname from course c,stu_cr s,student2 s2 WHERE c.id=s.cid and s.sid=s2.id
    </select>

</mapper>

注解开发

一对一

CardMapper

public interface CardMapper {
    @Select("select * from card")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "number",property = "number"),
            @Result(
                    property = "person",  //被包含对象的变量名
                    javaType = com.一对一.Person.class, //被包含实际数据类型
                    column = "pid",   //根据查询出card表对person id查询
                    /*
                        one, @One 一对一固定写法
                        select属性,指定调用那个接口的哪个方法
                     */
                    one=@One(select = "com.注解开发.一对一.personMapper.selectById")
            )
    })
    public abstract List<Card> selectAll();
}

personMapper

public interface personMapper {
    @Select("select * from person where id=#{id}")
    public abstract List<Person> selectById(Integer id);
}

一对多

studentMapper

public interface studentMapper {
    @Select("select * from student2")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "age",property = "age"),
            @Result(
                    property = "classes",  //被包含对象的变量名
                    javaType = List.class, //被包含实际数据类型
                    column = "cid",   //根据查询出card表对person id查询
                    /*
                        one, @One 一对一固定写法
                        select属性,指定调用那个接口的哪个方法
                     */
                    many=@Many(select = "com.注解开发.一对多.ClassesMapper.selectById")
            )
    })
    public abstract List<student> selectAll();
}

ClassesMapper

public interface ClassesMapper {

    @Select("select * from classes where id=#{id}")
    public abstract List<classes> selectById();
}

多对多

StudentMapper

public interface StudentMapper {
    @Select("select distinct s.id,s.name,s.age from student2 s,stu_cr sc where sc.sid=s.id")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "age",property = "age"),
            @Result(
                    property = "courses",  //被包含对象的变量名
                    javaType = List.class, //被包含实际数据类型
                    column = "id",   //根据查询出card表对person id查询
                    /*
                        many, @Many 一对多固定写法
                        select属性,指定调用那个接口的哪个方法
                     */
                    many=@Many(select = "com.注解开发.多对多.courseMapper.selectById")
            )
    })
    public abstract List<student> selectAll();

}

courseMapper

public interface courseMapper {
    @Select("select c.id,c.name from stu_cr sc,course c where sc.cid=c.id and sc.sid=#{id}")
    public abstract List<course> selectById(Integer id);
}