【Mybatis从入门到实战教程】第五章 Mybatis 关联查询详解
五、Mybatis 关联查询
5.1 数据模型分析
5.1.1 表功能介绍
5.1.2 表之间的业务关系
用户表和订单表:
-
用户表 ---> 订单表: 一个用户可以创建多个订单,一对多关系;
-
订单表 ---> 用户表: 一个订单只由一个用户创建,一对一关系;
订单表和订单详情表:
-
订单表 ---> 订单详情表: 一个订单可以包含多个订单详情,因为一个订单可以购买多个商品,每个商品的购买信息在订单详情表中记录,一对多关系;
-
订单详情表 ---> 订单表: 一个订单详情只能包括在一个订单中,一对一关系;
订单详情表和商品表:
-
订单详情表 ---> 商品表: 一个订单详情只对应一个商品信息,一对一关系;
-
商品表 ---> 订单详情表: 一个商品可以包括在多个订单详情,一对多关系;
订单表和商品表:
-
订单表 商品表: 一个订单中包含多个商品,一个商品可以添加在多个订单中,两者是通过订单详情表建立关系,多对多关系;
注意:
-
如果两张表有主外键关联关系,那么他们的业务关系是一对一/一对多,或者是双向一对一(比如用户表和用户详情表)。
-
如果两张表是双向一对多关系,那么他们是多对多关系,并且必然存在一张关系描述表作为中间表。
5.1.3 表结构
用户表:
CREATE TABLE `users` ( `id` int(11) PRIMARY KEY AUTO_INCREMENT, `username` varchar(20), `password` varchar(50), `realname` varchar(20));INSERT INTO `users` VALUES (1, 'admin', '123456', '管理员');INSERT INTO `users` VALUES (2, 'tom', '123', '汤姆');INSERT INTO `users` VALUES (3, 'jerry', '456', '杰瑞');INSERT INTO `users` VALUES (4, 'zhangsan', '111', '张三');INSERT INTO `users` VALUES (5, 'lisi', '222', '李四');
订单表:
CREATE TABLE `orders` ( `id` int(11) PRIMARY KEY AUTO_INCREMENT, `order_number` varchar(30), `total_price` double, `status` varchar(5), `user_id` int(11));INSERT INTO `orders` VALUES (1, '201812290838001', 2535, '已评价', 2);INSERT INTO `orders` VALUES (2, '201812290838002', 4704.6, '已签收', 2);INSERT INTO `orders` VALUES (3, '201812290838003', 3620, '已支付', 2);INSERT INTO `orders` VALUES (4, '201812290840001', 600, '已发货', 3);INSERT INTO `orders` VALUES (5, '201812290840002', 280, '未支付', 3);
订单详情表:
CREATE TABLE `orders_detail` ( `id` int(11) PRIMARY KEY AUTO_INCREMENT, `amount` int(11), `goods_id` int(11), `orders_id` int(11));INSERT INTO `orders_detail` VALUES (1, 1, 1, 1);INSERT INTO `orders_detail` VALUES (2, 3, 8, 1);INSERT INTO `orders_detail` VALUES (3, 1, 2, 2);INSERT INTO `orders_detail` VALUES (4, 2, 7, 2);INSERT INTO `orders_detail` VALUES (5, 1, 3, 3);INSERT INTO `orders_detail` VALUES (6, 6, 6, 3);INSERT INTO `orders_detail` VALUES (7, 2, 4, 4);INSERT INTO `orders_detail` VALUES (8, 1, 5, 5);
商品表:
CREATE TABLE `goods` ( `id` int(11) PRIMARY KEY AUTO_INCREMENT, `goods_name` varchar(50), `description` varchar(500), `price` double);INSERT INTO `goods` VALUES (1, '手机', '手机', 2499);INSERT INTO `goods` VALUES (2, '笔记本电脑', '笔记本电脑', 4699);INSERT INTO `goods` VALUES (3, 'IPAD', 'IPAD', 3599);INSERT INTO `goods` VALUES (4, '运动鞋', '运动鞋', 300);INSERT INTO `goods` VALUES (5, '外套', '外套', 280);INSERT INTO `goods` VALUES (6, '可乐', '可乐', 3.5);INSERT INTO `goods` VALUES (7, '辣条', '辣条', 2.8);INSERT INTO `goods` VALUES (8, '水杯', '水杯', 12);
5.2 一对一查询
5.2.1 需求
查询订单信息。关联如下:
1、关联查询其相关用户信息。
5.2.2 通过resultType方式实现
实体类:
实体类Orders类不能映射全部字段,需要新创建的实体类,创建一个包括查询字段较多的实体类。OrdersQuery中包含了Orders以及Users需要查询的属性。
package com.newcapec.vo;/** * OrdersQuery值对象,不是entity/po,因为它和数据库中表的字段不是对应关系 */public class OrdersQuery { //订单属性 private Integer id; private String orderNumber; private Double totalPrice; private String status; private Integer userId; //用户属性 private String username; private String password; private String realname; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getOrderNumber() { return orderNumber; } public void setOrderNumber(String orderNumber) { this.orderNumber = orderNumber; } public Double getTotalPrice() { return totalPrice; } public void setTotalPrice(Double totalPrice) { this.totalPrice = totalPrice; } public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getRealname() { return realname; } public void setRealname(String realname) { this.realname = realname; } @Override public String toString() { return "OrdersQuery{" + "id=" + id + ", orderNumber='" + orderNumber + '\'' + ", totalPrice=" + totalPrice + ", status='" + status + '\'' + ", userId=" + userId + ", username='" + username + '\'' + ", password='" + password + '\'' + ", realname='" + realname + '\'' + '}'; }}
mapper接口:
package com.newcapec.mapper;import com.newcapec.vo.OrdersQuery;import java.util.List;public interface OrdersMapper { List selectUseResultType();}
mapper文件:
select a.id,a.order_number,a.total_price,a.status,a.user_id,b.username,b.password,b.realname from orders a, users b where a.user_id=b.id
测试:
public class QueryTest { @Test public void testOneToOneResultType() { SqlSession sqlSession = MybatisUtil.getSession(); OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class); List list = ordersMapper.selectUseResultType(); for (OrdersQuery ordersQuery : list) { System.out.println(ordersQuery); } sqlSession.close(); }}
5.2.3 通过resultMap方式实现
- 5.2.3.1 实体类
用户类:
package com.newcapec.entity;public class Users { private Integer id; private String username; private String password; private String realname; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getRealname() { return realname; } public void setRealname(String realname) { this.realname = realname; } @Override public String toString() { return "Users{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", realname='" + realname + '\'' + '}'; }}
订单类:
在Orders类中加入Users属性,Users属性用于存储关联查询的用户信息。
因为订单关联查询用户是一对一关系,所以这里使用单个Users对象存储关联查询的用户信息。
package com.newcapec.entity;public class Orders { private Integer id; private String orderNumber; private Double totalPrice; private String status; private Integer userId; /** * 一对一关系属性 */ private Users users; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getOrderNumber() { return orderNumber; } public void setOrderNumber(String orderNumber) { this.orderNumber = orderNumber; } public Double getTotalPrice() { return totalPrice; } public void setTotalPrice(Double totalPrice) { this.totalPrice = totalPrice; } public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public Users getUsers() { return users; } public void setUsers(Users users) { this.users = users; } @Override public String toString() { return "Orders{" + "id=" + id + ", orderNumber='" + orderNumber + '\'' + ", totalPrice=" + totalPrice + ", status='" + status + '\'' + ", userId=" + userId + ", users=" + users + '}'; }}
- 5.2.3.2 mapper接口
List selectUseResultMap();
- 5.2.3.3 mapper文件
association标签: 一对一关系映射描述。
property: 关系属性名称
javaType: 关系属性类型
select a.id,a.order_number,a.total_price,a.status,a.user_id,b.username,b.password,b.realname from orders a, users b where a.user_id=b.id
- 5.2.3.4 测试
@Testpublic void testOneToOneResultMap() { SqlSession sqlSession = MybatisUtil.getSession(); OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class); List list = ordersMapper.selectUseResultMap(); for (Orders orders : list) { System.out.println(orders); } sqlSession.close();}
5.2.4 resultType和resultMap实现一对一查询小结
-
resultType:使用resultType实现较为简单,如果实体类中没有包括查询出来的列名,需要增加列名对应的属性,即可完成映射。如果查询结果没有特殊要求,建议使用resultType。
-
resultMap:需要单独定义resultMap,实现有点麻烦,如果对查询结果有特殊的要求,使用resultMap可以完成将关联查询映射到实体类的属性中。
-
resultMap可以实现延迟加载,resultType无法实现延迟加载。
5.3 一对多查询
5.3.1 需求
查询订单信息。关联如下:
1、关联查询其相关用户信息
2、关联查询其相关订单详情信息。
5.3.2 实体类
订单详情类:
public class OrdersDetail { private Integer id; private Integer amount; private Integer ordersId; private Integer goodsId; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getAmount() { return amount; } public void setAmount(Integer amount) { this.amount = amount; } public Integer getOrdersId() { return ordersId; } public void setOrdersId(Integer ordersId) { this.ordersId = ordersId; } public Integer getGoodsId() { return goodsId; } public void setGoodsId(Integer goodsId) { this.goodsId = goodsId; } @Override public String toString() { return "OrdersDetail{" + "id=" + id + ", amount=" + amount + ", ordersId=" + ordersId + ", goodsId=" + goodsId + '}'; }}
订单类:
在Order类中加入List detailList属性,details属性用于存储关联查询的订单详情。
因为订单关联查询订单详情是一对多关系,所以这里使用集合对象存储关联查询的订单详情信息。
public class Orders { private Integer id; private String orderNumber; private Double totalPrice; private String status; private Integer userId; /** * 一对一关系属性 */ private Users users; /** * 一对多关系属性 */ private List detailList; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getOrderNumber() { return orderNumber; } public void setOrderNumber(String orderNumber) { this.orderNumber = orderNumber; } public Double getTotalPrice() { return totalPrice; } public void setTotalPrice(Double totalPrice) { this.totalPrice = totalPrice; } public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public Users getUsers() { return users; } public void setUsers(Users users) { this.users = users; } public List getDetailList() { return detailList; } public void setDetailList(List detailList) { this.detailList = detailList; } @Override public String toString() { return "Orders{" + "id=" + id + ", orderNumber='" + orderNumber + '\'' + ", totalPrice=" + totalPrice + ", status='" + status + '\'' + ", userId=" + userId + ", users=" + users + ", detailList=" + detailList + '}'; }}
5.3.3 mapper接口
List selectOrdersAndDetail();
5.3.4 mapper文件
collection标签: 一对多关系映射描述。
property: 关系属性名称
ofType: 关系属性是一个List集合,集合中存放的元素类型
select a.id,a.order_number,a.total_price,a.status,a.user_id,b.username,b.password,b.realname, c.id detail_id,c.amount,c.goods_id from orders a join users b on a.user_id=b.id join orders_detail c on a.id=c.orders_id
5.3.5 测试
@Testpublic void testOneToMany() { SqlSession sqlSession = MybatisUtil.getSession(); OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class); List list = ordersMapper.selectOrdersAndDetail(); for (Orders orders : list) { System.out.println(orders); } sqlSession.close();}
5.4 多对多查询
5.4.1 订单与商品
- 5.4.1.1 需求
查询订单信息。关联如下:
1、关联查询其相关用户信息
2、关联查询其相关订单详情信息
3、关联查询订单详情中的商品信息
- 5.4.1.2 实体类
将OrderDetail类中Integer类型的goods_id属性修改为Goods类型属性,goods属性用于存储关联查询的商品信息。
订单与订单详情是一对多关系,订单详情与商品是一对一关系,反之商品与订单详情是一对多关系,订单详情与订单是一对一关系,所以订单与商品之前为多对多关系。
商品类:
public class Goods { private Integer id; private String goodsName; private String description; private Double price; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getGoodsName() { return goodsName; } public void setGoodsName(String goodsName) { this.goodsName = goodsName; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public Double getPrice() { return price; } public void setPrice(Double price) { this.price = price; } @Override public String toString() { return "Goods{" + "id=" + id + ", goodsName='" + goodsName + '\'' + ", description='" + description + '\'' + ", price=" + price + '}'; }}
订单详情类:
public class OrdersDetail { private Integer id; private Integer amount; private Integer ordersId; private Integer goodsId; /** * 一对一关系 */ private Goods goods; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getAmount() { return amount; } public void setAmount(Integer amount) { this.amount = amount; } public Integer getOrdersId() { return ordersId; } public void setOrdersId(Integer ordersId) { this.ordersId = ordersId; } public Integer getGoodsId() { return goodsId; } public void setGoodsId(Integer goodsId) { this.goodsId = goodsId; } public Goods getGoods() { return goods; } public void setGoods(Goods goods) { this.goods = goods; } @Override public String toString() { return "OrdersDetail{" + "id=" + id + ", amount=" + amount + ", ordersId=" + ordersId + ", goodsId=" + goodsId + ", goods=" + goods + '}'; }}
- 5.4.1.3 mapper接口
List selectOrdersAndGoods();
- 5.4.1.4 mapper文件
select a.id,a.order_number,a.total_price,a.status,a.user_id,b.username,b.password,b.realname, c.id detail_id,c.amount,c.goods_id,d.goods_name,d.description,d.price from orders a join users b on a.user_id=b.id join orders_detail c on a.id=c.orders_id join goods d on c.goods_id=d.id
- 5.4.1.5 测试
@Testpublic void testManyToMany1() { SqlSession sqlSession = MybatisUtil.getSession(); OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class); List list = ordersMapper.selectOrdersAndGoods(); for (Orders orders : list) { System.out.println(orders); } sqlSession.close();}
5.4.2 学生与课程之间的多对多关系
- 5.4.2.1 需求
查询学生信息,并关联查询学生相应的课程信息。
- 5.4.2.2 表结构
CREATE TABLE `course` ( `id` int(11) PRIMARY KEY AUTO_INCREMENT, `cname` varchar(20));INSERT INTO `course` VALUES (1, '大学语文');INSERT INTO `course` VALUES (2, '大学英语');INSERT INTO `course` VALUES (3, '高等数学');INSERT INTO `course` VALUES (4, 'JAVA语言');INSERT INTO `course` VALUES (5, '网络维护');INSERT INTO `course` VALUES (6, '通信原理');CREATE TABLE `student` ( `id` int(11) PRIMARY KEY AUTO_INCREMENT, `name` varchar(20), `gender` varchar(20), `major` varchar(20));INSERT INTO `student` VALUES (1, '小明', '男', '软件工程');INSERT INTO `student` VALUES (2, '小红', '女', '网络工程');INSERT INTO `student` VALUES (3, '小丽', '女', '物联网');CREATE TABLE `student_course` ( `id` int(11) PRIMARY KEY AUTO_INCREMENT, `student_id` int(11), `course_id` int(11));INSERT INTO `student_course` VALUES (1, 1, 1);INSERT INTO `student_course` VALUES (2, 1, 3);INSERT INTO `student_course` VALUES (3, 1, 4);INSERT INTO `student_course` VALUES (4, 2, 1);INSERT INTO `student_course` VALUES (5, 2, 2);INSERT INTO `student_course` VALUES (6, 2, 5);INSERT INTO `student_course` VALUES (7, 3, 2);INSERT INTO `student_course` VALUES (8, 3, 3);INSERT INTO `student_course` VALUES (9, 3, 6);
- 5.4.2.3 实体类
课程类:
public class Course { private Integer id; private String cname; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } @Override public String toString() { return "Course{" + "id=" + id + ", cname='" + cname + '\'' + '}'; }}
学生类:
public class Student { private Integer id; private String name; private String gender; private String major; /** * 一对多 */ private List courseList; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public String getMajor() { return major; } public void setMajor(String major) { this.major = major; } public List getCourseList() { return courseList; } public void setCourseList(List courseList) { this.courseList = courseList; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", gender='" + gender + '\'' + ", major='" + major + '\'' + ", courseList=" + courseList + '}'; }}
- 5.4.2.4 mapper接口
public interface StudentMapper { List select();}
- 5.4.2.5 mapper文件
select a.id,a.name,a.gender,a.major,b.course_id,c.cname from student a join student_course b on a.id=b.student_id join course c ON b.course_id=c.id
- 5.4.2.6 测试
@Testpublic void testManyToMany2() { SqlSession sqlSession = MybatisUtil.getSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); List list = studentMapper.select(); for (Student student : list) { System.out.println(student); } sqlSession.close();}
5.5 关联查询总结
5.5.1 resultType
作用:将查询结果按照SQL列名与实体类属性名一致性映射到实体类对象中。
场合:常见一些明细记录的展示,比如用户购买商品明细,将关联查询信息全部展示在页面时,此时可直接使用resultType将每一条记录映射到实体类中,在前端页面遍历list(list中是实体类)即可。
5.5.2 resultMap
使用association和collection完成一对一和一对多高级映射(对结果有特殊的映射要求)。
association
作用:将关联查询信息映射到一个实体类对象中。
场合:为了方便查询关联信息可以使用association将关联信息映射为当前对象的一个属性,比如:查询订单以及关联用户信息。
collection
作用:将关联查询信息映射到一个list集合中。
场合:为了方便查询遍历关联信息可以使用collection将关联信息映射到list集合中,比如: 查询用户权限范围模块及模块下的菜单,可使用collection将模块映射到模块list中,将菜单列表映射到模块对象的菜单list属性中,这样的作的目的也是方便对查询结果集进行遍历查询。如果使用resultType无法将查询结果映射到list集合中。
resultMap的继承
resultMap标签可以通过extends属性来继承一个已有的或公共的resultMap,避免重复配置的出现,减少配置量。
例子如下: