目录
- 1. 什么是一对多和多对一
- 2. 一对多的实现
- 创建实体类
- 按照结果嵌套处理
- 按照查询嵌套处理
- 3. 多对一的实现
- 创建实体类
- 按照结果嵌套处理
- 按照查询嵌套处理
- 注意
1. 什么是一对多和多对一
拿老师和学生举例:
一个老师可以有多个学生, 这就是一对多
多个学生只能有一个老师, 这就是多对一
图中的学生表中的tid对应老师表中的id
2. 一对多的实现
创建实体类
@Data
public class Student {
private int id;
private String name;
private int tid;
} //学生实体类
/*==================================================================*/
@Data
public class Teacher {
private int id;
private String name;
//一个老师对应多个学生
private List<Student> students;
} //老师实体类
按照结果嵌套处理
在TeacherMapper.xml
中:
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.id tid,t.name tname
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"/>
<!--collection: 一对多-->
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
创建Test进行测试
@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();
}
运行结果
Opening JDBC Connection
Created connection 832279283.
==> Preparing: select s.id sid,s.name sname,t.id tid,t.name tname from student s,teacher t where s.tid = t.id and t.id=?
==> Parameters: 1(Integer)
<== Columns: sid, sname, tid, tname
<== Row: 1, 小明, 1, 秦老师
<== Row: 2, 小红, 1, 秦老师
<== Row: 3, 小张, 1, 秦老师
<== Row: 4, 小李, 1, 秦老师
<== Row: 5, 小王, 1, 秦老师
<== Total: 5
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)])
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@319b92f3]
Returned connection 832279283 to pool.
按照查询嵌套处理
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from teacher where id=#{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<!--如果没有下面这行语句,由于在进行student表查询时使用Teacher类中的id已经被collection占用了,
查询出来的结果老师的id会显示为0,所以要提前做一个映射-->
<result property="id" column="id"/>
<!--collection: 一对多-->
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTid" column="id"/>
</resultMap>
<select id="getStudentByTid" resultType="Student">
select * from student where tid=#{tid}
</select>
创建Test进行测试
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
运行结果
Opening JDBC Connection
Created connection 832279283.
==> Preparing: select * from teacher where id=?
==> Parameters: 1(Integer)
<== Columns: id, name
<== Row: 1, 秦老师
====> Preparing: select * from student where tid=?
====> Parameters: 1(Integer)
<==== Columns: id, name, tid
<==== Row: 1, 小明, 1
<==== Row: 2, 小红, 1
<==== Row: 3, 小张, 1
<==== Row: 4, 小李, 1
<==== Row: 5, 小王, 1
<==== Total: 5
<== Total: 1
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)])
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@319b92f3]
Returned connection 832279283 to pool.
3. 多对一的实现
创建实体类
@Data
public class Student {
private int id;
private String name;
//学生关联老师
private Teacher teacher;
} //学生实体类
/*==================================================================*/
@Data
public class Teacher {
private int id;
private String name;
} //老师实体类
按照结果嵌套处理
在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: 多对一-->
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
<result property="id" column="tid"/>
</association>
</resultMap>
创建Test进行测试
@Test
public void testStudent2(){
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();
}
运行结果
Opening JDBC Connection
Created connection 1608230649.
==> Preparing: select s.id sid,s.name sname,t.id tid,t.name tname from student s,teacher t where s.tid=t.id
==> Parameters:
<== Columns: sid, sname, tid, tname
<== Row: 1, 小明, 1, 秦老师
<== Row: 2, 小红, 1, 秦老师
<== Row: 3, 小张, 1, 秦老师
<== Row: 4, 小李, 1, 秦老师
<== Row: 5, 小王, 1, 秦老师
<== Total: 5
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=秦老师))
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5fdba6f9]
Returned connection 1608230649 to pool.
按照查询嵌套处理
<select id="getStudent" resultMap="StudentTeacher">
select * from student;
</select>
<!--查询所有学生信息,根据查询出来的tid,再去查询对应的老师(子查询)-->
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--association: 多对一-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{tid};
</select>
创建Test进行测试
@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();
}
运行结果
Opening JDBC Connection
Created connection 1608230649.
==> Preparing: select * from student;
==> Parameters:
<== Columns: id, name, tid
<== Row: 1, 小明, 1
====> Preparing: select * from teacher where id=?;
====> Parameters: 1(Integer)
<==== Columns: id, name
<==== Row: 1, 秦老师
<==== Total: 1
<== Row: 2, 小红, 1
<== Row: 3, 小张, 1
<== Row: 4, 小李, 1
<== Row: 5, 小王, 1
<== Total: 5
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=秦老师))
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5fdba6f9]
Returned connection 1608230649 to pool.
注意
- 两种查询实现的方式是不一样的, 从日志中就可以看出
- 结果嵌套是一条sql语句直接查出结果, 嵌套是在sql内部完成的
- 查询嵌套是分别运行两条sql语句, 查询后对各自的结果再次进行嵌套
- 集合 ->
collection
【一对多】 - 关联 ->
association
【多对一】 javaType
和ofType
-
JavaType
用来指定实体类中属性的类型 -
ofType
用来指定映射到List或者集合中的 pojo类型,泛型中的约束类型!