Mybatis实现多表查询（一对多 、多对一）_mybatis 一对多查询

多对一和一对多的关系说明：以多个学生对一位老师为例

1. 对学生这方面说 ： 使用关键"关联" association 就是多个学生关联一个老师 -----------多对一

2. 对老师来说： 使用关键字“集合” collection， 就是 一个老师有很多学生 （集合）---------一对多

一、创建数据库

 
create table  teacher(
    id  int  not null  primary key ,
    name varchar(50) not null
)ENGINE =INNODB DEFAULT  CHARSET=utf8;
 
insert into teacher(id,name) values (1001,"刘老师"),(1002,"叶老师")，(1003,"张老师");
 
 
create table  student(
   id  int  not null  primary key,
   name varchar(50) not null,
   age   int not null ,
   tid  int default null,
   constraint `fk` foreign key (`tid`) references `teacher`(`id`)
)ENGINE =INNODB DEFAULT  CHARSET=utf8 collate utf8_general_ci
 
insert into student(id,name,age,tid) values (1201,"王冬雪",20,1001),
                                            (1202,"好平平",19,1001),
                                            (1203,"刘美玲",18,1001),
                                            (1204,"王海华",32,1001),
                                            (1205,"张李",34,1001),
                                            (1206,"江风",45,1001),
                                            (1207,"任丽",67,1001);
 
insert into student(id,name,age,tid) values (1201,"王冬雪",20,1001),
                                            (1202,"好平平",19,1001),
                                            (1203,"刘美玲",18,1001),
                                            (1204,"王海华",32,1001),
                                            (1205,"张李",34,1001),
                                            (1206,"江风",45,1001),
                                            (1207,"任丽",67,1001);
																						
insert into student(id,name,age,tid) values (1301,"李小龙",20,1002),
                                            (1302,"王美玲",19,1002),
                                            (1303,"和小",18,1002),
                                            (1304,"王丹丹",32,1002),
                                            (1305,"厚天天",34,1002),
                                            (1306,"李大明",45,1002),
                                            (1307,"李铁",43,1002);
 
insert into student(id,name,age,tid) values (1401,"王小妹",20,1003),
                                            (1402,"李大牛",19,1003),
                                            (1403,"杨杨",18,1003),
                                            (1404,"王二小",32,1003),
                                            (1405,"李芬丽",34,1003),
                                            (1406,"牛小同",45,1003),
                                            (1407,"和小明",23,1003);

二、多对一和一对多

第一步：导包

 <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.16</version>
    </dependency>

第二步：创建实体类Teacher、Student

Teacher

package ljg.com.pojo;
 
import lombok.*;
 
import java.util.List;
 
@Data
@AllArgsConstructor
@NoArgsConstructor
@Setter
@Getter
public class Teacher {
    private int id;
    private String name;
 
    // 一个老师拥有多个学生
    private List<Student> students;
}

Student 

package ljg.com.pojo;
 
import lombok.*;
 
@Data//提高代码的简洁，可以省去实体类中大量的get()、set()、toString()等方法
@AllArgsConstructor//注解版全参
@NoArgsConstructor//注解版无参
@Setter
@Getter
public class Student {
    private int id;
    private String name;
    private  int    age;
//学生需要关联一个老师  多对一
    private Teacher teacher;
 
 
    //一对多，一个老师拥有多个学生
    //只有一个老师
    private int tid;
 
}

第三步：建立mapper接口

package ljg.com.mapper;
 
import ljg.com.pojo.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
 
import java.util.List;
 
public interface StudentMapper {
    //@Select mybatis注解 用于映射sql语句
    @Select("select * from student where id = #{sid}")
    // @Param的作用就是给参数命名，
        // 比如在mapper里面某方法A（int id），
        // 当添加注解后A（@Param("sid") int id），
        // 也就是说外部想要取出传入的id值，
        // 只需要取它的参数名sid就可以了。
        // 将参数值传如SQL语句中，通过#{sid}进行取值给SQL的参数赋值。
    Student  getStudent(@Param("sid") int id);
 
 
    List<Student> getStudents();
 
    List<Student> getStudents2();
}

package ljg.com.mapper;
 
import ljg.com.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
 
import java.util.List;
 
public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
    
    //测试
    List<Teacher> getTeacher3();
 
    
 
    // 获取一个老师   获取指定老师下的所有及老师的信息
    Teacher  getTeacher1(@Param("tid") int id);
    //  子查询
    Teacher  getTeacher2(@Param("tid") int id);
 
}

第四步：建立Mapper.xml文件

StudentMapper.xml（多对一）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="ljg.com.mapper.StudentMapper">
 
 
    <!--按照结果嵌套处理       联表查询-->
    <select id="getStudents2" resultMap="StudentTeacher2"  >
        select s.id as sid,s.name as sname,age as sage,t.name as tname
        from student s,teacher t
        where s.tid=t.id
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <!--学生的属性id对应 sid    一一对应-->
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="age" column="sage"/>
        <association property="teacher"  javaType="Teacher">
        <!--老师是个复杂类型  结果处理完了  再去映射老师里面对应的字段-->
        <result property="name" column="tname"/>
        </association>
    </resultMap>
 
 
 
 
 
 
 
 
    <!--/
    1.查询所有的学生信息
    2.根据查询出来的学生的tid，寻找对应的老师
    -->
    <!--  按照查询嵌套处理-->
    <select id="getStudents" resultMap="StudentTeacher">
        select * from student
    </select>
 
    <!--结果集映射将两个关联起来，解决属性名和字段名不一致的问题-->
    <resultMap id="StudentTeacher" type="Student">
        <!--主键，一一对应-->
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
<!--    //property参数类型名
        //column字段名
        //javaType实体类中属性的类型
        //select 嵌套语句-->
<!--复杂的属性需要单独处理，对象（老师）：association  javaType：用来指定实体类中属性的类型，select在进行嵌套查询-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <!--tid 下面id没必要一一对应，mybatis会自行去推断-->
    <select id="getTeacher" resultType="Teacher">
        select * from Teacher where id=#{tid}
    </select>
</mapper>

说明：上述代码详细说明了多对一的两种方法，分别是查询嵌套处理和结果嵌套处理。

TeacherMapper.xml (一对多）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="ljg.com.mapper.TeacherMapper">
    
    
    
    <select id="getTeacher3" resultType="Teacher">
        select * from xsgl2.teacher;
    </select>
 
 
 
    <!--按照结果嵌套-->
    <select id="getTeacher1"  resultMap="TeacherStudent">
       select  s.id sid, s.name sname,s.age sage,t.id tid,t.name tname
       from teacher t ,student s
       where  t.id=s.tid and t.id=#{tid}
   </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
 
        <!--javaType时属性的类型，集合中的泛型信息，我们使用ofType获取-->
        <!--不用写javaType因为直接取出来的每一个值，然后去一一对应-->
        <collection property="students"  ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="age" column="sage"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
 
 
 
 
 
    <!--查询嵌套处理-->
    <select id="getTeacher2"  resultMap="TeacherStuden2">
        select  * from xsgl2.teacher  where id =#{tid}
    </select>
 
    <resultMap id="TeacherStuden2" type="Teacher">
        <!--
             private List<Student> students;
               list 是Java类型     用  javaType="ArrayList"
              <Student> 是范型数据 用  ofType="Student
              select="getByTidOfStudents"  根据老师的id查询对应的学生
              tid=#{tid}    这个结果就是老师的ID
          -->
        <!--本来就是个集合  集合对象和泛型信息写出来-->
        <collection property="students" javaType="ArrayList" ofType="Student"
                    select="getByTidOfStudents" column="id"/>
    </resultMap>
    <!--两个语句查询再通过子查询去查一次getByTidOfStudents-->
    <select id="getByTidOfStudents" resultType="Student">
       select  * from xsgl2.student where  tid=#{tid}
     </select>
</mapper>

说明：上述代码详细说明了多对一的两种方法，分别是查询嵌套处理和结果嵌套处理。

第五步：测试类

package ljg.com.test;
 
import ljg.com.mapper.StudentMapper;
import ljg.com.mapper.TeacherMapper;
import ljg.com.pojo.Student;
import ljg.com.pojo.Teacher;
import ljg.com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
 
 
import java.util.List;
 
public class YouTest {
//搭配环境
@Test
    public void getteacher(){
    SqlSession salSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = salSession.getMapper(StudentMapper.class);
    Student student = mapper.getStudent(1201);
    System.out.println(student);
 
    TeacherMapper mapper1 = salSession.getMapper(TeacherMapper.class);
    Teacher teacher = mapper1.getTeacher(1001);
    System.out.println(teacher);
}
 
//多对一
//    第一种方法    查询嵌套处理
@Test
    public void getstudent(){
    SqlSession salSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = salSession.getMapper(StudentMapper.class);
    List<Student> students = mapper.getStudents();
    for (Student student : students) {
        System.out.println(student);
    }
}
 
 
//第二种方法  结果嵌套查询
@Test
    public void getstudent1(){
    SqlSession salSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = salSession.getMapper(StudentMapper.class);
    List<Student> students = mapper.getStudents2();
    for (Student student : students) {
        System.out.println(student);
    }
}
 
 
    @Test
    public  void  getTeacher1(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher1(1002);
        System.out.println(teacher);
        sqlSession.close();
    }
 
    @Test
    public  void  getTeacher2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1002);
        System.out.println(teacher);
        sqlSession.close();
    }
 
 
    @Test
    public void getTeacher3(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        for (Teacher teacher :sqlSession.getMapper(TeacherMapper.class).getTeacher3()){
            System.out.println(teacher);
        }
        sqlSession.close();
 
    }
 
 
 
}