mybatis多表查询

一对一查询

1.创建表user和card

2.创建实体类

public class Card {
    private int id;
    private String num;
    private User user;
}

3.配置文件

<resultMap id="cards" type="com.xxxx.model.Card">
    <id column="id" property="id"></id>
    <result column="num" property="num"></result>
    <association property="user" javaType="com.xxxx.model.User">
        <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="age" property="age"></result>
        <result column="phone" property="phone"></result>
    </association>
</resultMap>

<select id="findAllCard" resultMap="cards">
    select c.id id,num,uid,username,password,age,phone from card c,user u where c.uid=u.id
</select>

4.测试

@Test
public void test05(){
    List<Card> allCard = cardMapper.findAllCard();
    System.out.println(allCard);
}

一对多查询

创建表和实体类  classes  student

public class Student {

    private int id;
    private String name;
    private int age;

}

public class Classes {

    private int id;
    private String name;
    private List<Student> students;
}

配置文件

<resultMap id="class" type="com.xxxx.model.Classes">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
    <collection property="students" ofType="com.xxxx.model.Student">
        <id column="sid" property="id"></id>
        <result column="sage" property="age"></result>
        <result column="sname" property="name"></result>
    </collection>
</resultMap>
<select id="findAll" resultMap="class">
    select c.id id,c.name name,s.id sid,s.name sname,s.age sage from classes c,student s where s.cid=c.id
</select>

多对多

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Student {
    private int id;
    private String name;
    private int age;
    private List<Teacher> teachers;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Teacher {
    private int id;
    private String name;
    private String sex;
    private List<Student> students;
}

编写Mapper文件

public interface StudentMapper {

    List<Student> findAllStudent();
}

public interface StudentMapper {

    List<Student> findAllStudent();
}

mapper配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxxx.dao.StudentMapper">

    <resultMap id="student" type="com.xxxx.model.Student">
        <id property="id" column="sid" />
        <result property="name" column="sname"/>
        <result property="age" column="sage"/>
        <collection property="teachers" ofType="com.xxxx.model.Teacher">
            <id property="id" column="tid" />
            <result property="name" column="tname"/>
            <result property="sex" column="tsex"/>
        </collection>
    </resultMap>

    <select id="findAllStudent" resultMap="student">
        select
            s.id sid,s.name sname,s.age sage,
            t.id tid,t.name tname,t.sex tsex
        from
            teacher t,student s,stu_teach st
        where
            t.id=st.tid and s.id=st.sid
    </select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxxx.dao.TeachertMapper">

    <resultMap id="teacher" type="com.xxxx.model.Teacher">
        <id property="id" column="tid" />
        <result property="name" column="tname"/>
        <result property="sex" column="tsex"/>
        <collection property="students" ofType="com.xxxx.model.Student">
            <id property="id" column="sid" />
            <result property="name" column="sname"/>
            <result property="age" column="sage"/>
        </collection>
    </resultMap>

    <select id="findAllTeacher" resultMap="teacher">
        select
            t.id tid,t.name tname,t.sex tsex,
            s.id sid,s.name sname,s.age sage
        from
            teacher t,student s,stu_teach st
        where
            t.id=st.tid and s.id=st.sid
    </select>
</mapper>

测试

@Autowired
private StudentMapper studentMapper;

@Autowired
private TeachertMapper teachertMapper;

@Test
public void test06(){
    List<Student> allStudent = studentMapper.findAllStudent();
    for (Student student : allStudent) {
        String name = student.getName();
        System.out.println(name);
        for (Teacher teacher : student.getTeachers()) {
            System.out.print("\t"+teacher.getName()+"-"+teacher.getSex()+"\t");
        }
        System.out.println();
    }
}