6Mybatis一对多喝多对一处理

一对多和多对一处理

1多对一处理

1.1理解

多个学生对应一个老师
如果对于学生这边，就是一个多对一的现象，即从学生这边关联一个老师

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

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

    //学生需要关联一个老师
    private Teacher teacher;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;

}

public interface StudentMapper {
   //查询所有的学生信息，以及对应的老师信息
   List<Student> getStudent();
   List<Student> getStudent2();
}

public interface TeacherMapper {
    Teacher getTeacher(int id);
}

1.2回顾Mysql多对一查询方式

子查询
联表查询

1.3方式一：按嵌套处理

查询所有学生信息
根据查询出来的学生tid寻找对应的老师
这样学生的结果集中应该包含老师，该如何处理呢？
做一个结果集映射：StudentTeacher
StudentTeacher结果集的类型为 Student
学生中老师的属性为teacher，对应数据库中为tid
association – 一个复杂类型的关联；使用它来处理关联查询

<!--  StudentMapper.xml中  -->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student;
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <!--复杂的属性我们需要单独处理  对象：association 集合：collection-->
        <!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
    </resultMap>

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

  @Test
    public void test2(){
        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();
    }

//结果
Student(id=1, name=小明, teacher=Teacher(id=1, name=秦老师))
Student(id=2, name=小红, teacher=Teacher(id=1, name=秦老师))
Student(id=3, name=小张, teacher=Teacher(id=1, name=秦老师))
Student(id=4, name=小李, teacher=Teacher(id=1, name=秦老师))
Student(id=5, name=小王, teacher=Teacher(id=1, name=秦老师))

1.4方式二：按照结果嵌套处理（个人更喜欢）

<!--  StudentMapper.xml中  -->
   <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid, s.name sname,t.id tid,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="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

 @Test
    public void test3(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();
        for (Student student:studentList){
            System.out.println(student);
        }
        sqlSession.close();
    }
//结果和上面一样

2一对多处理

2.1理解

一个老师拥有多个学生
如果对于老师这边，就是一个一对多的现象，即从一个老师下面拥有一群学生（集合）！

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;

    //一个老师拥有多个学生
    private List<Student> students;
}

public interface TeacherMapper {
    //获取老师
    List<Teacher> getTeacher();
    
    //获取指定老师下的所有学生及老师的信息
    Teacher getTeacher(int id);
}

2.2按结果嵌套查询

<!--  TeacherMapper.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=#{id}
    </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>

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

        sqlSession.close();
    }

//结果
Teacher(id=1, name=秦老师, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小红, tid=1), Student(id=3, name=小张, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])

3小结

关联：association(多对一)
集合：collection(一对多)
javaType：用来指定实体类中属性的类型
ofType：用来指定映射到List或集合中的pojo类型，即泛型中的约束类型

4注意点

保证SQL的可读性，尽量通俗易懂
根据实际要求，尽量编写性能更高的SQL语句
注意属性名和字段不一致的问题
注意一对多和多对一中：字段和属性对应的问题
尽量使用Log4j，通过日志来查看自己的错误

posted @ 2022-05-21 12:24 fao99 阅读(31) 评论(0) 收藏举报

刷新页面返回顶部

fao99

6Mybatis一对多喝多对一处理

一对多和多对一处理

公告