Mybatis学习笔记——day05

我不是女神ヾ 2022-12-30 15:58 223阅读 0赞

### 文章目录 ###

*  Mybatis
 *   *  10、多对一处理
     *   *  10.1测试环境搭建
         *  10.2按照查询嵌套处理
         *  10.3按照结果嵌套处理
     *  11、一对多处理
     *   *  11.1环境搭建
         *  11.2按照结果嵌套处理
         *  11.3按照查询嵌套处理
         *  小结

# Mybatis #

> 参考视频：[B站狂神][B]，写这个只是方便个人复习，怎么写是我自己的事，我能看懂就行，没要求非要让你看！白嫖还挑刺，是很没有风度的事情。希望做个有风度的“五好青年”！

--------------------

## 10、多对一处理 ##

> 多对一：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center]

*  多个学生，对应一个老师
 *  对于学生这边而言， **关联** … 多个学生，关联一个老师 【多对一】
 *  对于老师而言， **集合** ， 一个老师，有很多学生 【一对多】

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 1]

> SQL：

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' );

### 10.1测试环境搭建 ###

1.  导入lombok
2.  新建实体类 Teacher，Student
3.  建立Mapper接口
4.  建立Mapper.XML文件
5.  在核心配置文件中绑定注册我们的Mapper接口或者文件！【方式很多，随心选】
6.  测试查询是否能够成功！

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 2]

### 10.2按照查询嵌套处理 ###

*  StudentMapper接口

package com.github.subei.dao;
    
    import com.github.subei.pojo.Student;
    
    import java.util.List;
    
    public interface StudentMapper { 
        // 查询所有的学生的信息，以及对应老师的信息
        public List<Student> getStudent();
    
    }

*  StudentMapper.xml文件

*  测试类

import com.github.subei.dao.StudentMapper;
    import com.github.subei.dao.TeacherMapper;
    import com.github.subei.pojo.Student;
    import com.github.subei.pojo.Teacher;
    import com.github.subei.utils.MybatisUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import java.util.List;
    
    public class MyTest { 
    
        @Test
        public void testStudent(){ 
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            List<Student> studentList = mapper.getStudent();
            for(Student student : studentList){ 
                System.out.println(student);
            }
            sqlSession.close();
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 3]

*  修改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="com.github.subei.dao.StudentMapper">
    
        
    
        <select id="getStudent" resultMap="StudentTeacher">
            select * from student;
        </select>
    
        <resultMap id="StudentTeacher" type="Student">
            <result property="id" column="id"/>
            <result property="name" column="name"/>
            
            <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
        </resultMap>
    
        <select id="getTeacher" resultType="Teacher">
            select * from teacher where id = #{id};
        </select>
    
    </mapper>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 4]

### 10.3按照结果嵌套处理 ###

<select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s,teacher t
        where s.tid = t.id;
    </select>
    
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 5]

> 回顾Mysql 多对一查询方式：

*  子查询
 *  联表查询

## 11、一对多处理 ##

*  比如：一个老师拥有多个学生！
 *  对于老师而言，就是一对多的关系!

### 11.1环境搭建 ###

1.  环境搭建，和刚才一样。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 6]

**实体类**

package com.github.subei.pojo;
    
    import lombok.Data;
    
    @Data
    public class Student { 
        private int id;
        private String name;
        private int tid;
    }

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

*  测试一下，编写TeacherMapper接口

package com.github.subei.dao;
    
    import com.github.subei.pojo.Teacher;
    
    import java.util.List;
    
    public interface TeacherMapper { 
    
        // 获取老师
        List<Teacher> getTeacher();
    }

*  编写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="com.github.subei.dao.TeacherMapper">
    
        <select id="getTeacher" resultType="Teacher">
            select * from mybatis.teacher;
        </select>
    </mapper>

*  编写测试文档

import com.github.subei.dao.TeacherMapper;
    import com.github.subei.pojo.Teacher;
    import com.github.subei.utils.MybatisUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import java.util.List;
    
    public class MyTest { 
        @Test
        public void FTest(){ 
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
            List<Teacher> teacherList = mapper.getTeacher();
            for (Teacher teacher : teacherList){ 
                System.out.println(teacher);
            }
            sqlSession.close();
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 7]

### 11.2按照结果嵌套处理 ###

*  修改接口文档

package com.github.subei.dao;
    
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import com.github.subei.pojo.Teacher;
    
    import java.util.List;
    
    public interface TeacherMapper { 
    
        // 获取老师
       // List<Teacher> getTeacher();
    
        // 获取指定老师下的所有的学生及老师信息
        Teacher getTeacher(@Param("tid") int id);
    }

*  修改xml文档

<select id="getTeacher" resultMap="TeacherStudent">
            select s.id sid, s.name sname, t.name tname,t.id tid
            from student s,teacher t
            where s.tid = t.id and t.id = #{tid};
        </select>
    
        <resultMap id="TeacherStudent" type="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
            
            <collection property="students" ofType="Student">
                <result property="id" column="sid"/>
                <result property="name" column="sname"/>
                <result property="tid" column="tid"/>
            </collection>
        </resultMap>

*  测试文档

import com.github.subei.dao.TeacherMapper;
    import com.github.subei.pojo.Teacher;
    import com.github.subei.utils.MybatisUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import java.util.List;
    
    public class MyTest { 
        @Test
        public void FTest(){ 
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
            Teacher teacher = mapper.getTeacher(1);
            System.out.println(teacher);
            sqlSession.close();
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 8]

### 11.3按照查询嵌套处理 ###

Teacher getTeacher2(@Param("tid") int id);

<select id="getTeacher2" resultMap="TeacherStudent2">
        select * from mybatis.teacher where id = #{tid}
    </select>
    
    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>
    
    <select id="getStudentByTeacherId" resultType="Student">
        select * from mybatis.student where tid = #{tid}
    </select>

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 9]

### 小结 ###

1.  关联 - association 【多对一】
2.  集合 - collection 【一对多】
3.  javaType & ofType
    
    1.  JavaType 用来指定实体类中属性的类型
    2.  ofType 用来指定映射到List或者集合中的 pojo类型，泛型中的约束类型！

--------------------

> 注意点：

*  保证SQL的可读性，尽量保证通俗易懂！
 *  注意一对多和多对一中，属性名和字段的问题！
 *  如果问题不好排查错误，可以使用日志 ， 建议使用 Log4j！

> 面试高频：

*  Mysql引擎
 *  InnoDB底层原理
 *  索引
 *  索引优化！

[B]: https://www.bilibili.com/video/BV1NE411Q7Nx
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/a172a18daf42436e980401071eb38d5c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/abc1873a8b154fe6a4f9b72ade23325d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/3bb1b64e0d6c4c51899cdae8fc87ce47.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/69195501432a419a8bc338e999123a48.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/bc539c044153445aaf21d4d83b0fa57d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221120/314be4daf1a44fc4b487a6e4c06e98a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221120/cc44cc36bdcd4d44a521068bab478525.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221120/5bcf2085b8854da6ae5e4204ba52b356.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221120/43b94a39c59543d1ad446d6fd4e28a48.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221120/5781af4e0c824e4daa50c583dffb124c.png