Mybatis-05-多对一｜一对多

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');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('6', '小明', '1');

测试环境搭建

Maven新建一个基础项目或模块

导入依赖【mysql驱动，Mybatis，junit，lombok】

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
    <scope>provided</scope>
</dependency>

建立实体类，Teacher，Student

student

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

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

Teacher

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

建立接口 StudentMapper，TeacherMapper
建立StudentMapper.xml，TeacherMapper.xml文件
在核心配置中绑定注册Mapper接口或文件
定义一个简单方法，测试查询是否能成功

2、按照查询嵌套处理

思路：

先查询所有的学生信息
根据查询出来的学生的 tid，寻找对应的老师！子查询

实现代码：

StudentMapper 接口中定义查询方法（查询所有的学生信息，以及对应的老师信息
```
```java
public interface StudentMapper {

    public List<Student> getStudent();
}
```
```

编写 StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qn.dao.StudentMapper">
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <!-- 复杂的属性需要单独处理
        对象：association
        集合：collection
        -->
        <association column="tid" property="teacher" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    
	<select id="getTeacher" resultType="Teacher">
        select * from teacher
        where id = #{id}
    </select>

</mapper>

复杂的属性需要单独处理：

对象：association
集合：collection

测试代码：

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

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

    sqlSession.close();
}

结果：

3、按照结果嵌套处理

实现代码：

StudentMapper 接口中定义查询方法

public interface StudentMapper {

    public List<Student> studentTeacher();
}

编写 StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qn.dao.StudentMapper">

    <select id="studentTeacher" resultMap="StudentMap">
        select s.id sid, s.name sname, t.name tname
        from student s, teacher t
        where tid = t.id
    </select>
    
    <resultMap id="StudentMap" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>

        <association property="teacher" column="tid" javaType="Teacher">
            <result column="tname" property="name"/>
        </association>
    </resultMap>
    
</mapper>

测试代码：

@Test
public void studentTeacher(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    List<Student> students = mapper.studentTeacher();
    for (Student student : students) {
        System.out.println(student);
    }
    sqlSession.close();
}

结果：

mysql 中多对一查询方式：

子查询 ---->> 查询嵌套
联表查询 --->> 结果嵌套

2、一对多的处理

1、测试环境

与上方一致

2、按照查询嵌套处理

代码实现：

TeacherMapper 接口中定义查询方法

public interface TeacherMapper {

    // 获取指定老师下的所有学生
    List<Teacher> getTeacherById(@Param("tid") int id);
}

编写 TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qn.dao.TeacherMapper">

    <select id="getTeacherById" resultMap="TeacherMap">
        select s.id sid, s.name sname, t.name tname, t.id tid
        from student s, teacher t
        where tid = t.id and t.id = #{tid}
    </select>
    
    <resultMap id="TeacherMap" 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"/>
        </collection>
    </resultMap>
    
</mapper>

测试代码：

@Test
public void getTeacherById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

    List<Teacher> teachers = mapper.getTeacherById(1);
    for (Teacher teacher : teachers) {
        System.out.println(teacher);
    }

    sqlSession.close();
}

结果：

/*
* Teacher{
*   id=0, 
*   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}, 
*     Student{id=6, name='小明', tid=1}
*   ]
* }
 */

3、按照结果嵌套处理

代码实现：

TeacherMapper 接口中定义查询方法

public interface TeacherMapper {
    // 获取指定老师下的所有学生
    List<Teacher> getTeacherByIdTwo(@Param("tid") int id);
}

编写 TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qn.dao.TeacherMapper">

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

</mapper>

测试代码：

@Test
public void getTeacherById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

    List<Teacher> teachers = mapper.getTeacherByIdTwo(1);
    for (Teacher teacher : teachers) {
        System.out.println(teacher);
    }

    sqlSession.close();
}

结果：

/*
* Teacher{
*   id=0,
*   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},
*     Student{id=6, name='小明', tid=1}
*   ]
* }
 */

3、总结

关联： association （多对于一（对象
集合： collection（一对多（集合

javaType="" 指定 实体类 属性的类型

ofType="" 用来指定映射 集合中的泛型

注意点：

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

posted @ 2021-05-25 23:39 遇见星光阅读(45) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

遇见星光

Mybatis-05-多对一｜一对多

1、多对一的处理

1、测试环境

测试环境搭建

2、按照查询嵌套处理

3、按照结果嵌套处理

2、一对多的处理

1、测试环境

2、按照查询嵌套处理

3、按照结果嵌套处理

3、总结

公告