实验四：MyBatis 的关联映射

目录：

一 、实验目的：

熟练掌握实体之间的各种映射关系。

二 、预习要求：

预习数据库原理中所讲过的一对一、一对多和多对多关系

三、实验内容：

1. 查询所有订单信息，关联查询下单用户信息(注意：因为一个订单信息只会是一个人 下的订单，所以从查询订单信息出发关联查询用户信息为一对一查询。

如果从用户信息 出发查询用户下的订单信息则为一对多查询，因为一个用户可以下多个订单。)

2. 查询所有用户信息及用户关联的订单信息(用户信息和订单信息为一对多关系)。

四、实验方法和步骤：

实验前准备：

创建数据库，user表，order表

CREATE DATABASE experience03;
USE experience03;DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (`id` int(0) NOT NULL AUTO_INCREMENT,`username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`birthday` date NULL DEFAULT NULL,`sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`address` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ;INSERT INTO `user` VALUES (1, '关羽', '2024-09-29', '男', '蜀国');
INSERT INTO `user` VALUES (2, '梁王孙', '2024-09-11', '男', '北京市');
INSERT INTO `user` VALUES (3, '陈长生', '2024-05-14', '女', '京都西庙');
INSERT INTO `user` VALUES (4, '王婆', '2024-09-05', '女', '快远');
INSERT INTO `user` VALUES (5, '黄忠', '2016-07-24', '1', '三国');
INSERT INTO `user` VALUES (6, '张飞', '2024-10-16', '女', '三国');
INSERT INTO `user` VALUES (7, 'lucky', '2021-10-08', '男', '三国');
INSERT INTO `user` VALUES (14, '关羽', '2024-12-02', '男', '蜀国');

创建User：

package com.hasut.pojo;import java.util.Date;public class User {private int id;private String username;private Date birthday;private String sex;private String address;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", birthday=" + birthday +", sex='" + sex + '\'' +", address='" + address + '\'' +'}';}
}

创建Order：

package com.hasut.pojo;import java.util.Date;public class Order {private int id;private String number;private Date createtime;private String note;private User user;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getNumber() {return number;}public void setNumber(String number) {this.number = number;}public Date getCreatetime() {return createtime;}public void setCreatetime(Date createtime) {this.createtime = createtime;}public String getNote() {return note;}public void setNote(String note) {this.note = note;}public User getUser() {return user;}public void setUser(User user) {this.user = user;}@Overridepublic String toString() {return "Order{" +"id=" + id +", number='" + number + '\'' +", createtime=" + createtime +", note='" + note + '\'' +", user=" + user +'}';}
}

1. 查询所有订单信息（从订单出发一对一）

关联查询下单用户信息(注意：因为一个订单信息只会是一个人 下的订单，所以从查询订单信息出发关联查询用户信息为一对一查询。

如果从用户信息 出发查询用户下的订单信息则为一对多查询，因为一个用户可以下多个订单。)

sql 语句：

SELECT o.id, o.user_id userId, o.number, o.createtime, o.note, u.username, u.address FROM `order` o LEFT JOIN `user` u ON o.user_id = u.id

或者：

SELECT o.id, o.number,o.createtime,o.note,o.userId,u.username,u.birthday,u.sex,u.address
FROM `user` u, `order` o
WHERE u.id = o.userId

使用 resultMap，使用 resultMap，定义专门的 resultMap 用于映射一对一查询结 果。

①改造 pojo 类 在 Order 类中加入 User 属性，user 属性中用于存储关联查询的用户信息，因为订单关 联查询用户是一对一关系，所以这里使用单个 User 对象存储关联查询的用户信息。改造

Order 如下图：

package com.hasut.pojo;import java.util.Date;public class Order {private int id;private String number;private Date createtime;private String note;private User user;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getNumber() {return number;}public void setNumber(String number) {this.number = number;}public Date getCreatetime() {return createtime;}public void setCreatetime(Date createtime) {this.createtime = createtime;}public String getNote() {return note;}public void setNote(String note) {this.note = note;}public User getUser() {return user;}public void setUser(User user) {this.user = user;}@Overridepublic String toString() {return "Order{" +"id=" + id +", number='" + number + '\'' +", createtime=" + createtime +", note='" + note + '\'' +", user=" + user +'}';}
}

②Mapper.xml 这里 resultMap 指定 orderUserResultMap，如下：

<resultMap id="findOrderWithUserResultMap" type="com.haust.pojo.Orders"><id property="id" column="id"/><result property="number" column="number"/><result property="createtime" column="createtime"/><result property="note" column="note"/><association property="user" javaType="User"><id property="id" column="id"/><result property="username" column="username"/><result property="birthday" column="birthday"/><result property="sex" column="sex"/><result property="address" column="address"/></association></resultMap><select id="findOrderAndUserById" parameterType="int" resultMap="findOrderWithUserResultMap">SELECT o.id, o.number,o.createtime,o.note,o.userId,u.username,u.birthday,u.sex,u.addressFROM `user` u, `order` oWHERE u.id = o.userId</select>

③Mapper 接口

编写 UserMapper 如下图：

List<Orders> findOrderAndUserById(int id);

④测试方法

@Testpublic void test09() throws IOException {String resources = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resources);SqlSessionFactory sqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = sqlSessionFactory.openSession();OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);List<Orders> list = ordersMapper.findOrderAndUserById(1);for (Orders o : list){System.out.println(list);}sqlSession.commit();sqlSession.close();}

⑤实验效果

测试效果如下图：

2. 查询所有用户信息及用户关联的订单信息(从用户出发，为一对多关系)。

用户信息和订单信息为一对多关系

sql 语句：

SELECT u.id, u.username, u.birthday, u.sex, u.address, o.id oid, o.number, o.createtime, o.note FROM `user` u LEFT JOIN `order` o ON u.id = o.user_id

SELECT u.id,u.username,u.birthday,u.sex,u.address,o.id oid,o.number,o.createtime,o.note
FROM `user` u, `order` o
WHERE u.id = o.userId

①修改 pojo 类

在 User 类中加入 List orders 属性,如下图：

package com.haust.pojo;import java.util.Date;
import java.util.List;public class User {private int id;private String username;private Date birthday;private String sex;private String address;private List<Orders> ordersList;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public List<Orders> getOrdersList() {return ordersList;}public void setOrdersList(List<Orders> ordersList) {this.ordersList = ordersList;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", birthday=" + birthday +", sex='" + sex + '\'' +", address='" + address + '\'' +", ordersList=" + ordersList +'}';}
}

②UserMapper.xml

在 UserMapper.xml 添加 sql，如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.haust.mapper.UserMapper"><resultMap id="findOrderWithUserResultMap2" type="com.haust.pojo.User"><id property="id" column="id"/><result property="username" column="username"/><result property="birthday" column="birthday"/><result property="sex" column="sex"/><result property="address" column="address"/><collection property="ordersList" ofType="com.haust.pojo.Orders"><id property="id" column="id"/><result property="number" column="number"/><result property="createtime" column="createtime"/><result property="note" column="note"/></collection></resultMap><select id="findOrderAndUserById2" parameterType="int" resultMap="findOrderWithUserResultMap2">SELECT u.id,u.username,u.birthday,u.sex,u.address,o.id oid,o.number,o.createtime,o.noteFROM `user` u, `order` oWHERE u.id = o.userIdand u.id = #{id}</select>
</mapper>

③Mapper 接口

编写 UserMapper 接口，如下图：

public interface UserMapper {List<User> findOrderAndUserById2(int id);
}

④测试方法

在 UserMapperTest 增加测试方法，如下

@Testpublic void test10() throws IOException {String resources = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resources);SqlSessionFactory sqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);List<User> list = userMapper.findOrderAndUserById2(2);for (User o : list){System.out.println(o);}sqlSession.commit();sqlSession.close();}

⑤效果

测试效果如下图：