本文介绍了如何使用MyBatis处理数据库中的多对一和一对多关系。通过创建Teacher和Student实体类，建立数据库表并插入数据，然后在MyBatis中编写Mapper接口和XML配置文件，实现按查询嵌套和结果嵌套的查询方式，展示了获取学生列表及其对应老师信息以及获取指定老师的所有学生的方法。测试结果显示查询成功，展示了MyBatis在处理关联查询的灵活性。
9 一对多和多对一处理
多对一处理
多个学生对应一个老师
数据库设计
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');
搭建测试环境
1、编写实体类
Student.java
package com.zzb.pojo;
public class Student {
private int id;
private String name;
private Teacher teacher;
public Student() {
}
public Student(int id, String name, Teacher teacher) {
this.id = id;
this.name = name;
this.teacher = teacher;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
'}';
}
}
Teacher.java
package com.zzb.pojo;
public class Teacher {
private int id;
private String name;
public Teacher() {
}
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
2、编写实体类对应的Mapper接口
package com.zzb.dao;
public interface StudentMapper {
}
package com.zzb.dao;
public interface TeacherMapper {
}
3、编写接口对应的mapper.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.zzb.dao.StudentMapper">
</mapper>
<?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.zzb.dao.TeacherMapper">
</mapper>
按查询嵌套处理
1、给 StudentMapper 接口增加方法
// 获取学生列表及对应老师的信息
public List<Student> getStudents();
2、编写对应的Mapper配置文件
<?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.zzb.dao.StudentMapper">
<!--按查询嵌套-->
<select id="getStudents" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名-->
<association property="teacher" column="tid" javaType="Teacher" select = "getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{tid}
</select>
</mapper>
3、MyBatis配置文件中注册Mapper
<!--绑定接口-->
<mappers>
<mapper class="com.zzb.dao.StudentMapper"/>
</mappers>
注意：
<resultMap id="StudentTeacher" type="Student">
<!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名-->
<association property="teacher" column="{id = tid, name = name}" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--
这里传递过来的id，只有一个属性的时候，下面可以写任何值
association中column多参数配置：
column="{key=value,key=value}"
其实就是键值对的形式，key是传给下个sql的取值名称，value是sql查询的字段名。
-->
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{id} and name = #{name}
</select>
4、测试
@Test
public void getStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.getStudents();
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、StudentMapper接口方法编写
public List<Student> getStudents2();
2、编写对应的StudentMapper.xml配置文件
<!--按结果嵌套-->
<select id="getStudents2" resultMap="StudentTeacher2">
SELECT S.id AS sid, s.name AS sname, t.id AS tid, t.name AS tname
FROM student AS s, teacher AS 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>
3、MyBatis配置文件中注册Mapper
<mapper class="com.zzb.dao.StudentMapper"/>
4、测试
@Test
public void getStudent2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.getStudents2();
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='曾老师'}}
总结：
按照查询进行嵌套处理就像SQL中的子查询
按照结果进行嵌套处理就像SQL中的联表查询
一对多处理
module：mybatis-06
一个老师拥有多个学生
1、实体类编写
Student.java
package com.zzb.pojo;
// pom.xml中引入lombok包，简化实体类的编写
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Teacher.java
package com.zzb.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
和上述多对一一样，搭建测试环境！
按结果嵌套查询
1、TeacherMapper接口编写方法
package com.zzb.dao;
import com.zzb.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
public interface TeacherMapper {
// 获取指定老师下的所有学生
Teacher getTeacher2(@Param("tid") int id);
}
2、编写接口对应的Mapper配置文件
<?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.zzb.dao.TeacherMapper">
<!--按结果嵌套-->
<select id="getTeacher2" resultMap="TeacherStudent2">
SELECT S.id AS sid, s.name AS sname, t.id AS tid, t.name AS tname
FROM student AS s, teacher AS t
WHERE s.tid = t.id;
</select>
<resultMap id="TeacherStudent2" 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>
</mapper>
3、mybatis配置文件中注册mapper
<!--绑定接口-->
<mappers>
<mapper class="com.zzb.dao.TeacherMapper"/>
</mappers>
4、测试
@Test
public void getTeacher2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(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)])
按查询嵌套
1、TeacherMapper接口编写方法
// 获取指定老师下的所有学生
Teacher getTeacher(@Param("tid") int id);
2、编写接口对应的Mapper配置文件
<!--按查询嵌套-->
<select id="getTeacher" resultMap="TeacherStudent">
select * from teacher
</select>
<resultMap id="TeacherStudent" type="teacher">
<result property="id" column="id"/>
<result property="name" column="name"/>
<collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudent"/>
</resultMap>
<select id="getStudent" resultType="Student">
select * from student where tid = #{id}
</select>
3、mybatis配置文件中注册mapper
<!--绑定接口-->
<mappers>
<mapper class="com.zzb.dao.TeacherMapper"/>
</mappers>
4、测试
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.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)])
总结：
1、关联-association
2、集合-collection
3、所以association是用于一对一和多对一，而collection是用于一对多的关系
4、JavaType和ofType都是用来指定对象类型的
- JavaType是用来指定pojo中属性的类型
- ofType指定的是映射到List集合属性中pojo的类型。
注意说明：
1、保证SQL的可读性，尽量通俗易懂
2、根据实际要求，尽量编写性能更高的SQL语句
3、注意属性名和字段不一致的问题
4、注意一对多和多对一 中：字段和属性对应的问题
5、尽量使用Log4j，通过日志来查看自己的错误
扫一扫
422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
