学习博客：【MyBatis】多对一处理（关联）

创建数据库

CREATE DATABASE `mybatis`;

USE mybatis

CREATE TABLE `teacher`(
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `teacher`(`id`,`name`) VALUES(1,'赵老师');

CREATE TABLE `student`(
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    `tid` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fktid` (`tid`),
    CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `student`(`id`,`name`,`tid`) VALUES (1,'王晓','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES (2,'惠集','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES (3,'杨但','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES (4,'李里','1');
INSERT INTO `student`(`id`,`name`,`tid`) VALUES (5,'赵中','1');

多对一查询方式

• 子查询
• 联表查询

1. 搭建环境

1. 导入 lombok

   <!--lombok-->
   <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.24</version>
       <scope>provided</scope>
   </dependency>
   

2. 实体类 Teacher Student

   import lombok.Data;
   
   @Data
   public class Teacher {
       private int id;
       private String name;
   }
   

   import lombok.Data;
   
   @Data
   public class Student {
       private int id;
       private String name;
   
       //学生关联一个老师
       private Teacher teacher;
   
   }
   

3. Mapper接口

   import com.yl.pojo.Teacher;
   import org.apache.ibatis.annotations.Param;
   import org.apache.ibatis.annotations.Select;
   
   public interface TeacherMapper {
   
       @Select("select * from teacher where id = #{tid}")
       Teacher getTeacher(@Param("tid") int id);
   }
   

   import com.yl.pojo.Student;
   
   import java.util.List;
   
   public interface StudentMapper {
   
       //查询所有学生信息，及对应的老师信息（按查询嵌套处理）
       public List<Student> getStudent();
   
       //按结果嵌套处理
       public List<Student> getStudent2();
   
   }
   

4. Mapper.xml

   <?xml version="1.0" encoding="GBK" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   
   <!--核心配置文件-->
   <mapper namespace="com.yl.dao.TeacherMapper">
   
   </mapper>
   

   <?xml version="1.0" encoding="GBK" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   
   <!--核心配置文件-->
   <mapper namespace="com.yl.dao.StudentMapper">
   
   </mapper>
   

5. 在核心配置文件中绑定注册 Mapper 接口或文件

   <mappers>
       <mapper class="com.yl.dao.TeacherMapper"/>
       <mapper class="com.yl.dao.StudentMapper"/>
   </mappers>
   

6. 测试

   @Test
   public void getTeacher(){
       SqlSession sqlSession = MyBatisUntils.getSqlSession();
   
       TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
   
       Teacher teacher = mapper.getTeacher(1);
       System.out.println(teacher);
   
       sqlSession.close();
   
   }
   

2. 按照查询嵌套处理

<!--
    1.查询所有学生信息
    2.根据查询出来的学生的tid寻找对应的老师
-->
<select id="getStudent" resultMap="ST">
    select * from student
</select>

<resultMap id="ST" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--association 对象      collection 集合-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

@Test
public void getStudent(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    List<Student> studentList = mapper.getStudent();

    for (Student student : studentList) {
        System.out.println(student);
    }

    sqlSession.close();

}

3. 按照结果嵌套处理

<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="ST2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id
</select>

<resultMap id="ST2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

@Test
public void getStudent2(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    List<Student> studentList = mapper.getStudent2();

    for (Student student : studentList) {
        System.out.println(student);
    }

    sqlSession.close();

}