8、多对一的处理
多对一
- 例:多个学生对应一个老师
8.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 `mybatis`.`student`(`id`, `name`, `tid`) VALUES (1, '小明', 1);
INSERT INTO `mybatis`.`student`(`id`, `name`, `tid`) VALUES (2, '小红', 1);
INSERT INTO `mybatis`.`student`(`id`, `name`, `tid`) VALUES (3, '小张', 1);
INSERT INTO `mybatis`.`student`(`id`, `name`, `tid`) VALUES (4, '小李', 1);
INSERT INTO `mybatis`.`student`(`id`, `name`, `tid`) VALUES (5, '小王', 1);
CREATE TABLE `teacher` (
`id` int(10) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `mybatis`.`teacher`(`id`, `name`) VALUES (1, '秦老师');
8.2 导包
<!--导入依赖-->
<dependencies>
<!--mybaits-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!--mysql连接池-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
<!--单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!--log4j-->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.18.0</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!--分页-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
8.3 编写核心配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--核心配置文件-->
<configuration>
<!--环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.zhangjiangbo.cn.mapper"></package>
</mappers>
</configuration>
8.4示例
创建学生类
package com.zhangjiangbo.cn.bean;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
创建老师类(多个学生对应一个老师)
package com.zhangjiangbo.cn.bean;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
编写mapper接口
package com.zhangjiangbo.cn.mapper;
import com.zhangjiangbo.cn.bean.Student;
import java.util.List;
public interface StudentMapper {
List<Student> queryAll();
}
编写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.zhangjiangbo.cn.mapper.StudentMapper">
<resultMap id="baseResultMap" type="com.zhangjiangbo.cn.bean.Student">
<result property="id" jdbcType="INTEGER" column="id"/>
<result property="name" jdbcType="VARCHAR" column="name"/>
<association property="teacher" javaType="com.zhangjiangbo.cn.bean.Teacher" column="tid" select="getTeacher"/>
</resultMap>
<select id="queryAll" resultMap="baseResultMap">
select * from student
</select>
<select id="getTeacher" resultType="com.zhangjiangbo.cn.bean.Teacher">
select * from teacher where id = #{tid}
</select>
</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.zhangjiangbo.cn.mapper.StudentMapper">
<resultMap id="baseResultMap" type="com.zhangjiangbo.cn.bean.Student">
<result property="id" jdbcType="INTEGER" column="sid"/>
<result property="name" jdbcType="VARCHAR" column="sname"/>
<association property="teacher" javaType="com.zhangjiangbo.cn.bean.Teacher">
<result property="id" jdbcType="INTEGER" column="tid"/>
<result property="name" jdbcType="VARCHAR" column="tname"/>
</association>
</resultMap>
<select id="queryAll" resultMap="baseResultMap">
select
s.id as sid,
s.name as sname,
t.id as tid,
t.name as tname
from
student s,teacher t
where
s.tid = t.id
</select>
</mapper>
测试类
package com.zhangjiangbo.cn;
import com.zhangjiangbo.cn.bean.Student;
import com.zhangjiangbo.cn.mapper.StudentMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Test {
@org.junit.Test
public void test01() throws IOException {
String resource = "mybaits.xml";
InputStream resourceAsStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.queryAll();
students.forEach(action->{
System.out.println(action);
});
sqlSession.close();
}
}
运行结果
个人理解:
- 第一种方式:查询到每一条数据,将关联的字段当做参数传到下一个SQL中执行,便于理解但是不好维护,处理复杂表关系比较鸡肋。
- 第二种方式:比较直观,sql使用多表关联查询,便于维护。
- 个人推荐使用第二种
重点:association标签
9、多对一处理
一对多
- 一个老师对应多个学生
环境搭建同上
修改实体类
package com.zhangjiangbo.cn.bean;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
// private Teacher teacher;
}
package com.zhangjiangbo.cn.bean;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
创建mapper接口
package com.zhangjiangbo.cn.mapper;
import com.zhangjiangbo.cn.bean.Teacher;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface TeacherMapper {
List<Teacher> queryTeacher(@Param("id") int id);
}
创建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.zhangjiangbo.cn.mapper.TeacherMapper">
<resultMap id="baseResultMap" type="com.zhangjiangbo.cn.bean.Teacher">
<result column="id" jdbcType="INTEGER" property="id"></result>
<result column="name" jdbcType="VARCHAR" property="name"></result>
<collection property="students" ofType="com.zhangjiangbo.cn.bean.Student" javaType="ArrayList" column="id" select="getStudent"></collection>
</resultMap>
<select id="queryTeacher" resultMap="baseResultMap">
select * from teacher where id = #{id}
</select>
<select id="getStudent" resultType="com.zhangjiangbo.cn.bean.Student">
select * from student where tid = #{id}
</select>
</mapper>
第二种方式 结果嵌套
<resultMap id="baseResultMap" type="com.zhangjiangbo.cn.bean.Teacher">
<result column="id" jdbcType="INTEGER" property="id"></result>
<result column="name" jdbcType="VARCHAR" property="name"></result>
<collection property="students" ofType="com.zhangjiangbo.cn.bean.Student">
<result property="id" jdbcType="INTEGER" column="sid"></result>
<result property="name" jdbcType="INTEGER" column="sname"></result>
<result property="tid" jdbcType="INTEGER" column="tid"></result>
</collection>
</resultMap>
<select id="queryTeacher" resultMap="baseResultMap">
select
s.id as sid,
s.name as sname,
s.tid as tid,
t.name,
t.id
from
student s,
teacher t
where
s.tid = t.id
and t.id = #{id}
</select>
测试类
org.junit.Test
public void test02() throws IOException {
String resource = "mybaits.xml";
InputStream resourceAsStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teachers = mapper.queryTeacher(1);
teachers.forEach(action->{
System.out.println(action);
});
sqlSession.close();
}
运行结果
个人理解
- 第一种方式:查询到每一条数据,将关联的字段当做参数传到下一个SQL中执行,使用ofType属性来指定实体类,要通过javaType告诉mybatis实体类中定义的字段类型。
- 第二种方式:比较直观,sql使用多表关联查询,同样也要使用ofType属性来指定实体类
注意点 collection标签、ofType属性
小结
- 关联-association
- 集合-colletion
- 多对一使用association、一对多使用colletion
- javaType和ofType都是用来指定对象类型的
- javaType是用来指定pojo中属性的类型
- ofType指定的是映射到list集合属性中pojo的类型
注意
1、保证sql的可读性
2、尽量编写性能更高的sql语句
3、注意属性名和字段名不一致问题
4、提高自己查看日志排查错误的能力