Mybatis（八）——关联查询(association、collection)

1. 一对一查询

建立好两个表student和card，其中student表中主键为id，外键为cardid，指向card表中的主键id。



建立一个Card类与表card对应

package com.santiago.entity;

public class Card {
    private int id;
    private String info;

    public int getId() {
        return id;
    }

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

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }
}

1.1 使用扩展业务类来实现一对一查询

建立StudentBusiness类，使该类继承自Student，然后将Card类中的属性和方法加入到StudentBusiness中，这样，StudentBusiness类就会同时有Student和Card类的所有属性和方法。

package com.santiago.entity;

public class StudentBusiness extends Student {
    private int id;
    private String info;

    public int getId() {
        return id;
    }

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

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }

    @Override
    public String toString() {
        return super.toString()
                + "StudentBusiness{" +
                "id=" + id +
                ", info='" + info + '\'' +
                '}';
    }
}

SQL映射文件

    <select id="queryWith1vs1" parameterType="int" resultType="StudentBusiness">
        select s.*, c.* from student s inner join card c
        on s.cardid = c.id
        where s.stuno = #{stuno}
    </select>

测试类

    public static void queryWith1vs1(){
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        StudentBusiness studentBusiness= studentMapper.queryWith1vs1(2);
        session.commit();
        System.out.println(studentBusiness);
        session.close();
    }

运行结果

1.2 使用resultMap来实现一对一查询

使用业务扩展类时，需要新建一个类StudentBusiness，使其继承自Student，然后添加Card类所有的属性和方法，于是StudentBusiness类则同时具有Student和Card所有的属性。
而使用resultMap时，只需在Student类中添加一个Card属性，则Student类就具有了Card类中的属性和方法。

package com.santiago.entity;

public class Student {
    private int stuNo;
    private String stuName;
    private int stuAge;
    private Address address;
    private Card card;

    public Card getCard() {
        return card;
    }

    public void setCard(Card card) {
        this.card = card;
    }

    public Student() {
    }

    public int getStuNo() {
        return stuNo;
    }

    public void setStuNo(int stuNo) {
        this.stuNo = stuNo;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public int getStuAge() {
        return stuAge;
    }

    public void setStuAge(int stuAge) {
        this.stuAge = stuAge;
    }

    public Address getAddress() {
        return address;
    }

    public void setAddress(Address address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Student{" +
                "stuNo=" + stuNo +
                ", stuName='" + stuName + '\'' +
                ", cardId=" + this.card.getId() +
                ", cardInfo=" + this.card.getInfo() +
                '}';
    }
}

SQL映射文件

    <select id="queryWithMap1vs1" parameterType="int" resultMap="Student_card_map">
        select s.*, c.* from student s inner join card c
        on s.cardid = c.id
        where s.stuno = #{stuno}
    </select>
    <resultMap id="Student_card_map" type="Student">
        <!--学生信息-->
        <id property="stuNo" column="stuNo"/>
        <result property="stuName" column="stuName"/>
        <result property="stuAge" column="stuAge"/>

        <!--学生卡信息-->
        <!--一对一映射用association，一对多映射用collection-->
        <association property="card" javaType="Card">
            <id property="id" column="id"/>
            <result property="info" column="info"/>
        </association>
    </resultMap>

注意：主键使用< id>标签，非主键使用< result>标签，对于类属性，一对一映射用association，一对多映射用collection

测试类

    public static void queryWithMap1vs1(){
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        Student student= studentMapper.queryWithMap1vs1(2);
        session.commit();
        System.out.println(student);
        session.close();
    }

运行结果

嵌套查询和分步查询

一对一嵌套查询和分步查询：https://blog.csdn.net/qq_36544760/article/details/79502206

2. 一对多查询

一对多以一个班级对应多个学生为例。
首先，新建一个班级表，添加classid字段和classname字段，在student表中添加一个classid字段，在class表中添加外键与student表进行关联。


新建一个StuClass类，在类中添加List< Student> 属性使其与Student类进行关联

public class StuClass {
    private int classId;
    private String className;
    private List<Student> students;

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    public int getClassId() {
        return classId;
    }

    public void setClassId(int classId) {
        this.classId = classId;
    }

    public String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }

    @Override
    public String toString() {
        return "Class{" +
                "classId=" + classId +
                ", className='" + className + '\'' +
                ", students=" + students +
                '}';
    }
}

SQL映射文件

    <select id="queryWith1vsN" parameterType="int" resultMap="class_student_map">
        select s.*, c.* from student s
        inner join class c
        on c.classid = s.classid
        where c.classid = #{classId}
    </select>
    <resultMap id="class_student_map" type="StuClass">
        <!--因为Class是主类，所以先配置Class的属性-->
        <id property="classId" column="classid"/>
        <result property="className" column="classname"/>
        <!--配置成员属性学生-->
        <collection property="students" ofType="Student">
            <id property="stuNo" column="stuno"/>
            <result property="stuName" column="stuname"/>
            <result property="stuAge" column="stuage"/>
        </collection>
    </resultMap>

注意：<collection property="students" ofType="Student">中使用到了oftype，JavaType和ofType都是用来指定对象类型的，但是JavaType是用来指定pojo中属性的类型，而ofType指定的是映射到list集合属性中pojo的类型。

测试类
首先在接口中添加方法StuClass queryWith1vsN(int id);
然后在测试类中添加具体的测试方法

    public static void queryWith1vsN(){
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        StuClass clazz = studentMapper.queryWith1vsN(1);
        session.commit();
        System.out.println(clazz);
        session.close();
    }

测试结果