MyBatis中的关联关系以及多表连接

目录

一对一

一对多

多对多

关联关系总结

---

一对一

一张表的一条记录对应查询到另一张表的一条记录，重要的是实体中的一个对象也要包含另一个对象

实例，一个乘客查询到一本护照

实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Passenger {
    private Integer id;
    private String name;
    private String sex;
    private Date birthday;
    private Passport passport;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Passport {
    private Integer id;
    private String nationality;
    private Date expire;
    private Integer passenger_id;
}

dao层

 public Passenger selectByPassengerId(Integer id);

mapper语句

<resultMap id="rm" type="passenger">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <association property="passport" javaType="passport">
            <id property="id" column="pid"></id>
            <result property="expire" column="expire"></result>
        </association>
    </resultMap>
​
    <select id="selectByPassengerId" resultMap="rm">
        select pg.*,pp.id pid,pp.expire from t_passports pp INNER JOIN
        t_passengers pg on pg.id=pp.passenger_id and pg.id=#{id};
    </select>

测试层

 @Test
    public void PassengerText(){
        PassengerDao passengerDao = MyBatisUtils.getMapper(PassengerDao.class);
        Passenger passenger = passengerDao.selectByPassengerId(1001);
        System.out.println(passenger);
    }

一对多

mapper语句

<resultMap id="rm" type="dept">
        <id property="id" column="did"></id>
        <result property="name" column="dname"></result>
        <result property="location" column="dlocation"></result>
        <collection property="emps" ofType="emp">
            <id property="id" column="id"></id>
            <result property="name" column="name"></result>
            <result property="salary" column="salary"></result>
            <result property="dept_id" column="dept_id"></result>
        </collection>
    </resultMap>
    <select id="selectByEmpId" resultMap="rm">
        select e.*,d.id did,d.name dname,d.location dlocation from t_departments d INNER JOIN
        t_employees e on e.id=d.id where e.id=#{id};
    </select>

实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private Integer id;
    private String name;
    private double salary;
    private Integer dept_id;
    
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Dept {
    private Integer id;
    private String name;
    private String location;
    private List<Emp> emps;
}

dao层

public Dept selectByEmpId(Integer id);

测试层

@Test
    public void selectEmpText(){
        DeptDao deptDao = MyBatisUtils.getMapper(DeptDao.class);
        Dept dept = deptDao.selectByEmpId(1);
        System.out.println(dept);
    }

多对多

dao层

public List<Student> selectBySbId(Integer id);

测试层

@Test
    public void SubjectText(){
        SubjectDao subjectDao = MyBatisUtils.getMapper(SubjectDao.class);
        List<Student> students = subjectDao.selectBySbId(10);
        System.out.println(students);
    }

mapper

<resultMap id="rm" type="subject">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="grade" column="grade"></result>
        <collection property="students" ofType="student">
            <id property="id" column="tid"></id>
            <result property="name" column="tname"></result>
            <result property="sex" column="tsex"></result>
        </collection>
    </resultMap>
    <select id="selectBySbId" resultMap="rm">
        select sb.*,st.id tid,st.name tname,st.sex tsex from student st INNER JOIN
        stu_sub ss on ss.student_id=st.id INNER JOIN subject sb
        on sb.id=ss.subject_id and sb.id=10;
    </select>

实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private Integer id;
    private String name;
    private String sex;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Subject {
    private Integer id;
    private String name;
    private Integer grade;
    private List<Student> students;
}

关联关系总结

双方建立关系属性后，对应的mapper文件中要使用<ResultMap>完成多表之间的映射

持有对象关系属性（一对一）：< association property="" javaType="" >

持有集合关系属性（一对多或多对一）：