（五）Mybatis-多对一和一对多
（五）Mybatis-多对一和一对多
一、多对一查询
1.1、搭建环境
1.1.1 多对一处理
-
多个学生，对应一个老师
-
对于学生而言，关联。多个学生，关联一个老师【多对一】
-
对于老师而言，集合。一个老师，有很多学生【一对多】
1.1.2 搭建数据库环境
创建teacher表
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,'高老师')
创建student表
create table `student` (
`id` int(10) not null auto_increment,
`name` varchar(10) 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
插入5个学生
INSERT INTO `student` VALUES (NULL,'小明','1')，
(NULL,'小高','1')
(NULL,'小张','1')
(NULL,'小王','1')
(NULL,'小李','1')
1.1.3 创建pojo类
导入lombok
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
<scope>provided</scope>
</dependency>
创建Teacher类
package com.happy.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
private int id;
private String name;
}
创建Student类
package com.happy.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private int id;
private String name;
// 学生需要关联一个老师
// private int tid;
private Teacher teacher;
}
1.1.4 编写mapper接口
一个表对应一个pojo类，对应一个mapper接口
package com.happy.dao;
import com.happy.pojo.Teacher;
import java.util.List;
public interface TeacherMapper {
List<Teacher> getTeacherList();
}
待扩展，可以方法先不写
package com.happy.dao;
public interface StudentMapper {
}
1.1.5 建立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.happy.dao.TeacherMapper">
<select id="getTeacherList" resultType="teacher">
select *
from teacher
</select>
</mapper>
1.1.6 在核心配置文件中注册mapper文件或mapper接口
相当于绑定接口和接口实现mapper文件
1.1.7 测试使用
package com.happy.dao;
import com.happy.pojo.Teacher;
import com.happy.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Test;
import java.util.List;
public class TeacherMapperTest {
private static Logger logger = Logger.getLogger(TeacherMapperTest.class);
@Test
public void testGetTeacherList() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teacherList = mapper.getTeacherList();
for (Teacher teacher : teacherList) {
System.out.println(teacher);
}
}
}
}
1.2 按照查询嵌套处理
1.2.1 原理：类似于子查询
SELECT * FROM student s WHERE s.tid IN (SELECT t.id FROM teacher t)
1.2.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.happy.dao.StudentMapper">
<!-- <select id="getStudentsList" resultType="student" >-->
<!-- SELECT *-->
<!-- FROM student s-->
<!-- LEFT JOIN teacher t ON s.`tid` = t.`id`-->
<!-- </select>-->
<!--=========================方式一：按查询嵌套查询=>sub query===========================-->
<resultMap id="studentMap4SubQuery" type="student">
<result column="id" property="id"></result>
<result column="name" property="name"></result>
<!-- <collection column="tid" property="teacher"></collection>-->
<!-- 如果属性是一个对象，则使用association-->
<association column="tid" property="teacher" javaType="teacher" select="getTeacherList"></association>
</resultMap>
<select id="getStudentsList4SubQuery" resultMap="studentMap4SubQuery">
SELECT *
FROM student
</select>
<select id="getTeacherList" resultType="teacher">
select *
from teacher
where id = #{tid}
</select>
<!--============================方式二：按接口嵌套查询=》union===================================-->
<resultMap id="studentMap4Union" type="student">
<result column="sid" property="id"></result>
<result column="sname" property="name"></result>
<association property="teacher" javaType="teacher">
<result column="tname" property="name" ></result>
<result column="tid" property="id"></result>
</association>
</resultMap>
<select id="getStudentsList4Union" resultMap="studentMap4Union">
select s.id sid, s.name sname, t.name tname,t.id tid
from student s
left join teacher t on s.tid = t.id;
</select>
</mapper>
1.2.3 测试使用
//方式一
//按照查询嵌套查询，子查询
@Test
public void testGetStudentsList4SubQuery() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudentsList4SubQuery();
for (Student student : studentList) {
System.out.println(student);
}
}
}
1.3 按照结果嵌套处理
1.3.1 原理：类似于联表查询
SELECT * FROM student s LEFT JOIN teacher t ON s.tid=t.id
1.3.2 mapper文件实现
要注意结果集映射resultMap的时候：
- 简单类型（8大类+String）用result处理映射
- 复杂类型（pojo类，map，list等）使用association和collection处理
- 单一对象使用association
- 集合类型使用collection
<!--============================方式二：按接口嵌套查询=》union===================================-->
<resultMap id="studentMap4Union" type="student">
<result column="sid" property="id"></result>
<result column="sname" property="name"></result>
<association property="teacher" javaType="teacher">
<result column="tname" property="name" ></result>
<result column="tid" property="id"></result>
</association>
</resultMap>
<select id="getStudentsList4Union" resultMap="studentMap4Union">
select s.id sid, s.name sname, t.name tname,t.id tid
from student s
left join teacher t on s.tid = t.id;
</select>
1.3.3 测试使用
//方式二
//按照结果嵌套查询，联合查询
@Test
public void testGetStudentsList4Union() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudentsList4Union();
for (Student student : studentList) {
System.out.println(student);
}
}
}
二、一对多处理
2.1 搭建环境
2.1.1 一对多处理
关系同上：
比如：一个老师拥有很多学生！
对老师而言，就是一对多的关系
2.1.2 搭建数据库环境
完全复用上面多对一数据库
2.1.3 创建pojo类
导入lombok
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
<scope>provided</scope>
</dependency>
创建Teacher类
注意这里Teacher里装了很多学生，用集合！
package com.happy.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
private int id;
private String name;
private List<Student> studentList;
}
创建Student类
package com.happy.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
}
2.1.4 编写mapper接口
编写TeacherMapper
package com.happy.dao;
import com.happy.pojo.Teacher;
import java.util.List;
public interface TeacherMapper {
List<Teacher> getTeacherList();
}
编写StudentMapper
package com.happy.dao;
import com.happy.pojo.Student;
import java.util.List;
public interface StudentMapper {
}
2.1.5 建立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.happy.dao.TeacherMapper">
<select id="getTeacherList" resultType="teacher">
select *
from teacher
</select>
</mapper>
2.1.6 在核心配置文件中注册mapper文件或mapper接口
相当于绑定接口和接口实现mapper文件
2.1.7 测试使用
package com.happy.dao;
//import com.happy.dao.UserDaoImpl;
import com.happy.pojo.Student;
import com.happy.pojo.Teacher;
import com.happy.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Test;
import java.util.List;
public class TeacherMapperTest {
private static Logger logger = Logger.getLogger(TeacherMapperTest.class);
@Test
public void testGetTeacherList() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teacherList = mapper.getTeacherList();
for (Teacher teacher : teacherList) {
System.out.println(teacher);
}
}
}
}
可以发现，teacher里的student对象又为空了。现在下面开始解决获取老师即其所有学生。
2.2 按照查询嵌套处理
2.2.1 mapper接口编写
List<Teacher> getTeacherListBySubQuery();
2.2.2 mapper文件实现
要注意结果集映射resultMap的时候：
- 简单类型（8大类+String）用result处理映射
- 复杂类型（pojo类，map，list等）使用association和collection处理
- 单一对象使用association
- 集合类型使用collection
<!--===============================按查询嵌套查询=>子查询==========================================-->
<resultMap id="TeacherMapBySubQuery" type="teacher">
<result column="name" property="name"></result>
<result column="id" property="id"></result>
<collection property="studentList" ofType="student" select="getStudentList" column="id"></collection>
</resultMap>
<select id="getTeacherListBySubQuery" resultMap="TeacherMapBySubQuery">
select * from teacher;
</select>
<select id="getStudentList" resultType="student">
select * from student where tid=#{tid};
</select>
</mapper>
2.2.3 测试使用
@Test
public void testGetTeacherListBySubQuery() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teacherList = mapper.getTeacherListBySubQuery();
for (Teacher teacher : teacherList) {
System.out.println(teacher);
}
}
}
2.3 按照结果嵌套处理
2.3.1 mapper接口编写
package com.happy.dao;
import com.happy.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface TeacherMapper {
List<Teacher> getTeacherList();
List<Teacher> getTeacherListByUnionQuery();
Teacher getTeacherById(@Param("tid") int id);
}
2.3.2 mapper文件实现
要注意结果集映射resultMap的时候：
简单类型（8大类+String）用result处理映射复杂类型（pojo类，map，list等）使用association和collection处理单一对象使用association集合类型使用collection
<?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.happy.dao.TeacherMapper">
<select id="getTeacherList" resultType="teacher">
select *
from teacher
</select>
<resultMap id="TeacherMapByUnionQuery" type="teacher">
<result column="tid" property="id"></result>
<result column="tname" property="name"></result>
<collection property="studentList" ofType="student">
<result column="sname" property="name"></result>
<result column="sid" property="id"></result>
</collection>
</resultMap>
<select id="getTeacherListByUnionQuery" resultMap="TeacherMapByUnionQuery">
select t.name tname, t.id tid, s.id sid, s.name sname
from teacher t
left join student s on t.id = s.tid;
</select>
<select id="getTeacherById" parameterType="int" resultMap="TeacherMapByUnionQuery">
select t.name tname, t.id tid, s.id sid, s.name sname
from teacher t
left join student s on t.id = s.tid
where t.id = #{tid};
</select>
</mapper>
2.3.3 测试使用
@Test
public void testGetTeacherListByUnionQuery() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teacherList = mapper.getTeacherListByUnionQuery();
for (Teacher teacher : teacherList) {
System.out.println(teacher);
}
}
}
@Test
public void testGetTeacherById() {
try (SqlSession sqlSession = MybatisUtils.getSqlSession()) {
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacherById(1);
System.out.println(teacher);
}
}
三、小结
3.1 多对一
-
关联-association【多对一】
-
association里面使用javaType，指定单一pojo类型
3.2 一对多
teacher包含多个student
-
集合-collection【一对多】
-
collection里面使用offType，指定集合中类型即泛型类型。
3.3 注意点
- 保证sql的可读性，尽量保证通俗易懂
- 注意一对多和多对一种，属性名和和字段的问题。
- 如果问题不好排查，可以考虑使用log4j作为日志记录。
浙公网安备 33010602011771号