多对一，一对多处理，按照结果嵌套，按照查询嵌套处理简单理解

多对一，一对多处理，按照结果嵌套，按照查询嵌套处理

多对一处理

1.按照查询嵌套处理

1.创建新的Modlue

2.加入新的Sql语句

在之前的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');

3.编写实体类 Student Teacher

4.编写StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://my batis.org/dtd/mybatis-3-mapper.dtd">

<mapper  namespace="com.ws.Dao.StudentMapper">
    
</mapper>

5.编写TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://my batis.org/dtd/mybatis-3-mapper.dtd">

<mapper  namespace="com.ws.Dao.TeacherMapper">

</mapper>

6.写接口 Student Teacher接口

package com.ws.Dao;

public interface TeacherMapper {
}

package com.ws.Dao;

public interface StudentMapper {
    List<Student> getStudentList();
}

7.给实体类起别名

别忘记在核心配置文件中起别名

<typeAliases>
    <typeAlias type="com.ws.pojo.Student" alias="Student"/>
    <typeAlias type="com.ws.pojo.Teacher" alias="Teacher"/>
</typeAliases>

8.编写StudentMapper.xml（加条件）

• 这里用的是对象查询的
• 查询所有学生的信息
• 根据查询出来的学生tid 然后再次关联老师的查询
• 这三个查询语句是关联起来的 并没有调用TeacherMapper的getTeacherList方法
• association里面关联的select是自己起的名字 目的就是关联起来
• 还需要注意的是ResultMap的 id和 select对应的ResultMap是一致的 目的也是关联起来 也是自己起的名字
• association是对象查询
• collection是集合查询

<select id="getStudentList" resultMap="StudentTeacher">
    select *from student
</select>

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

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

9.测试

  //获得Session对象
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //getMapper  获得接口   对应的就是Dao里面的UserMapper的sql语句  直接执行
    StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);


    List<Student> studentList = mapper.getStudentList();
    for (Student student :studentList){
        System.out.println(studentList);
    }

    sqlSession.close();
}

2.按照查询嵌套处理

1.创建一个新的接口

List<Student> getStudentList2();

2.StudentMapper.xml 配置

• 查询出来学生的 然后对应的老师进行映射 直接查询老师的名字 并查询没有id 只是tid对用的老师的id!

<mapper  namespace="com.ws.Dao.StudentMapper">

    <select id="getStudentList2" 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 column="id" property="sid"/>
        <result column="name" property="sname"/>
        <association property="teacher" javaType="Teacher">
             <!--    column对应的就是数据库的字段    property 对用就是实体类  !-->
            <!--     查询出来学生的 然后对应的老师进行映射  直接查询老师的名字  并查询没有id   只是tid对用的老师的id!-->
            <result column="tname" property="name"/>
        </association>

    </resultMap>
    

3.测试

public  void getStudentList2() {

    //获得Session对象
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //getMapper  获得接口   对应的就是Dao里面的UserMapper的sql语句  直接执行
    StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);


    List<Student> studentList = mapper.getStudentList2();
    for (Student student :studentList){
        System.out.println(studentList);
    }

    sqlSession.close();
}

一对多处理

1.按照查询嵌套处理

• 新建一个Module 重新配置一下

1.编写实体类 换新的方式了实体类也要修改

• Teacher

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

• Student

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private int tid;
}

2.写接口

import com.ws.pojo.Teacher;
import org.apache.ibatis.annotations.Param;

public interface TeacherMapper {

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

}

3.写对应的TeacherMapper.xml

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

4.测试

@Test
public  void getUserList2() {

    //获得Session对象
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //getMapper  获得接口   对应的就是Dao里面的UserMapper的sql语句  直接执行
    TeacherMapper mapper=sqlSession.getMapper(TeacherMapper.class);


    Teacher teacherList = mapper.getTeacherList2(1);

    System.out.println(teacherList);


    sqlSession.close();
}

2.按照结果嵌套处理

1.接口

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

2.TeacherMapper.xml

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

3.测试

@Test
public  void getUserList() {

    //获得Session对象
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    //getMapper  获得接口   对应的就是Dao里面的UserMapper的sql语句  直接执行
    TeacherMapper mapper=sqlSession.getMapper(TeacherMapper.class);


    Teacher teacherList = mapper.getTeacherList(1);

    System.out.println(teacherList);


    sqlSession.close();
}

小结

1.关联-association 【多对一】

2.集合 -collection【一对多】

3.javaType &ofType

• javaType用来指定实体类中属性的类型
• ofType用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

注意点：

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

面试高频

• Mysql引擎
• InnoDb底层原理
• 索引
• 索引优化