此文章接上一篇博文https://blog.csdn.net/qq_45929019/article/details/131627291
一、多对一处理
1、多对一理解
- 多个学生对应一个老师
- 从学生的角度看,就是一个多对一的现象,即多个学生关联一个老师
2、数据库设计
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');
3、搭建测试环境
(1)IDEA安装lombok插件
在:file–>settings–>plugs中搜索安装即可
(2)IDEA中引入lombok依赖
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
</dependency>
(3)编写实体类,并使用lombok为实体类加上注释
Student实体类:
@Data //加上这个lombok的注解,为该类实现了无参构造,get,set,toString()
public class Student {
private int id;
private String name;
// 学生需要关联一个老师
private Teacher teacher;
}
Teacher实体类:
@Data
public class Teacher {
private int id;
private String name;
}
(4)编写实体类对应的Mapper接口
public interface StudentMapper {
// 查询所有学生的信息以及老师的信息
public List<Student> getStudent();
public List<Student> getStudent2();
}
public interface TeacherMapper {
@Select("select * from teacher where id=#{tid};")
Teacher getTeacher(@Param("tid") int id);
}
(5)编写Mapper接口对应的mapper.xml配置文件
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xian.dao.StudentMapper">
<!--
方式一:
思路:
1.查询所有的学生信息
2.根据查询出来的学生的tid,寻找对应的老师! 子查询
-->
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!-- 复杂的属性我们需要单独处理 对象:association 集合:collection -->
<!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getStudent" resultMap="StudentTeacher">
select * from student;
</select>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{tid};
</select>
<!-- =================================================
方式二:
思路: 按照结果嵌套处理
-->
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id as sid,s.name as sname,t.name as tname
from student s, teacher t
where s.tid=t.id;
</select>
</mapper>
TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xian.dao.TeacherMapper">
</mapper>
(6)编写数据库配置文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username=root
password=123456
(7)补充MyBatis核心配置文件,注册Mapper!
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration:核心配置文件-->
<configuration>
<!-- 引入外部配置文件-->
<properties resource="db.properties" />
<settings>
<!-- 标准的日志工厂实现-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!-- 可以给实体类取别名,减少完整限定名的书写-->
<typeAliases>
<!-- 可以给实体类取别名 为该类名的首字母小写-->
<package name="com.xian.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- mapper.xml 和 mapper 要在同一个包下面-->
<mappers>
<mapper class="com.xian.dao.TeacherMapper"/>
<mapper class="com.xian.dao.StudentMapper"/>
</mappers>
</configuration>
(8)注意点:
<resultMap id="StudentTeacher" type="Student">
<!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名-->
<association property="teacher" column="{id=tid,name=tname}" 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>
(9)测试
@Test
public void getStudent(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudent();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
@Test
public void getStudent2(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudent2();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
(10)小结
- 按照查询进行嵌套处理就像SQL中的子查询
- 按照结果进行嵌套处理就像SQL中的联表查询
二、一对多处理
1、一对多的理解
- 一个老师拥有多个学生
- 如果对于老师这边,就是一个一对多的现象,即从一个老师下面拥有一群学生(集合)!
2、数据库设计
同上,不用修改
3、搭建测试环境
仅补充与第一部分(多对一处理)不同的部分
(1)实体类编写,并使用lombok为实体类加上注释
@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;
}
(2)编写实体类对应的Mapper接口
public interface StudentMapper {
}
public interface TeacherMapper {
// 获取老师
List<Teacher> getTeacher();
// 获取指定老师下的所有学生及老师的信息
Teacher getTeacher2(@Param("tid") int id);
// 获取指定老师下的所有学生及老师的信息
Teacher getTeacher3(@Param("tid") int id);
}
(3)编写Mapper接口对应的mapper.xml配置文件
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xian.dao.StudentMapper">
</mapper>
TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xian.dao.TeacherMapper">
<!--普通测试-->
<select id="getTeacher" resultMap="TeacherStudent">
select s.id as sid, s.name as sname,t.id as tid,t.name as tname
from teacher t, student s
where s.tid=t.id;
</select>
<!-- ===================按结果嵌套查询=========================-->
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!-- students 为集合
javaType为指定的属性类型
集合中的泛型信息,我们使用ofType获取
-->
<collection property="students" javaType="ArrayList" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<select id="getTeacher2" resultMap="TeacherStudent" parameterType="int">
select s.id as sid, s.name as sname,t.id as tid,t.name as tname
from teacher t, student s
where s.tid=t.id and t.id=#{tid};
</select>
<!-- =================嵌套子查询的方式=================-->
<resultMap id="TeacherStudent2" 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="getTeacher3" resultMap="TeacherStudent2" parameterType="int">
select * from teacher where id=#{tid};
</select>
<select id="getStudent" resultType="Student">
select * from student where tid=#{id};
</select>
</mapper>
(4)补充MyBatis核心配置文件,注册Mapper!
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration:核心配置文件-->
<configuration>
<!-- 引入外部配置文件-->
<properties resource="db.properties" />
<settings>
<!-- 标准的日志工厂实现-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!-- 可以给实体类取别名,减少完整限定名的书写-->
<typeAliases>
<!-- 可以给实体类取别名 为该类名的首字母小写-->
<package name="com.xian.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- mapper.xml 和 mapper 要在同一个包下面-->
<mappers>
<mapper class="com.xian.dao.TeacherMapper"/>
<mapper class="com.xian.dao.StudentMapper"/>
</mappers>
</configuration>
(5)测试
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teacherList = mapper.getTeacher();
for (Teacher teacher : teacherList) {
System.out.println(teacher);
}
sqlSession.close();
}
// 结果集嵌套
@Test
public void getTeacher2(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
// 嵌套子查询
@Test
public void getTeacher3(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher3(1);
System.out.println(teacher);
sqlSession.close();
}
三、小结
1、关联-association
2、集合-collection
3、所以association是用于一对一和多对一,而collection是用于一对多的关系
4、JavaType和ofType都是用来指定对象类型的
- JavaType是用来指定pojo中属性的类型
- ofType指定的是映射到list集合属性中pojo的类型。
5、注意说明:
- (1)保证SQL的可读性,尽量通俗易懂
- (2)根据实际要求,尽量编写性能更高的SQL语句
- (3)注意属性名和字段不一致的问题
- (4)注意一对多和多对一 中:字段和属性对应的问题
- (5)尽量使用Log4j,通过日志来查看自己的错误
附官方参考文档
Mybatis官方文档 :http://www.mybatis.org/mybatis-3/zh/index.html
GitHub :https://github.com/mybatis/mybatis-3