Mybatis

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

10、多对一处理

多对一：

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

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. 测试查询是否能够成功！

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文件

<?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">
<!--configuration核心配置文件-->
<mapper namespace="com.github.subei.dao.StudentMapper">

    <select id="getStudent" resultType="Student">
        select * from student
    </select>

</mapper>

• 测试类

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();
    }
}

• 修改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">
<!--configuration核心配置文件-->
<mapper namespace="com.github.subei.dao.StudentMapper">

    <!-- 思路: 1. 查询所有的学生信息 2. 根据查询出来的学生的tid，寻找对应的老师！ 子查询 -->

    <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 集合： collection -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

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

</mapper>

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>

回顾Mysql 多对一查询方式：

• 子查询
• 联表查询

11、一对多处理

• 比如：一个老师拥有多个学生！

• 对于老师而言，就是一对多的关系!

11.1环境搭建

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

实体类

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">
<!--configuration核心配置文件-->
<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();
    }
}

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"/>
        <!--复杂的属性，需要单独处理 对象： association 集合： collection javaType="" 指定属性的类型！ 集合中的泛型信息，我们使用ofType获取 -->
        <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();
    }
}

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();
    }

小结

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

注意点：

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

面试高频：

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