【Mybatis】Mybatis处理一对多、多对多关系映射-四

唠嗑部分

上篇文章我们说了Mybatis中ORM映射的几种方式，相关文章：
【Mybatis】简单入门及工具类封装-一
【Mybatis】如何实现ORM映射-二
【Mybatis】Mybatis的动态SQL、缓存机制-三

这篇文章我们来说说Mybatis如何处理一对一、一对多、多对多的关系映射

首先创建环境，表结构

create database `mybatis-wx-demo` character set 'utf8mb4';
use `mybatis-wx-demo`;
create table user
(id int primary key auto_increment comment '主键id',user_name varchar(255) comment '用户名',age int comment '年龄',create_time datetime comment '创建时间'
) comment '用户表';insert into user values (1, '全栈小白', 23, CURRENT_TIMESTAMP()),(2, '南宫飞雪', 25, CURRENT_TIMESTAMP()),(3, '盒马鲜生', 30, CURRENT_TIMESTAMP());create table user_account(acc_id int primary key auto_increment comment '账户id',acc_balance int default 0 comment '账户余额',user_id int comment '用户id'
) comment '用户账户表';
insert into user_account values (1, 100, 1);create table role(role_id int primary key auto_increment comment '角色id',role_name varchar(50) comment '角色名'
) comment '角色表';insert into role values (1, '超级管理员'), (2, '管理员'), (3, '用户');create table user_role(id int primary key auto_increment comment '主键id',u_id int not null comment '用户id',r_id int not null comment '角色id'
) comment '用户角色关联表';insert into user_role values (1, 1, 1), (2, 1, 2);create table user_photo(id int primary key auto_increment comment '主键',user_id int not null comment '用户id',url varchar(255) not null comment '用户照片'
) comment '用户照片表';
insert into user_photo values (1, 1, 'https://img2.baidu.com/it/u=3202947311,1179654885&fm=253&fmt=auto&app=138&f=JPEG?w=800&h=500'),(2, 1, 'https://lmg.jj20.com/up/allimg/1114/040221103339/210402103339-8-1200.jpg');

说说表结构，user与user_account是一对一的关系，用户只能有一个账户，

user与user_photo是一对多的关系，用户可以有多个生活照片

user与role是多对多的关系，用户可以有多个角色

言归正传

实体类关系就不演示了，着重说数据封装

需求一：查询用户id为1的用户信息及用户账户信息(一对一)

1、创建UserAccountInfoBO业务封装对象

/*** @Project: mybatis-wx-demo* @Author: cxs2014501@163.com* @Create: 2023/3/10 11:01* @Description:**/
@Data
public class UserAccountInfoBO {private User userInfo;private UserAccount userAccountInfo;
}

2、编写接口

/*** @Project: mybatis-wx-demo* @Author: cxs2014501@163.com* @Create: 2023/2/28 10:41* @Description:**/
public interface UserMapper {UserAccountInfoBO selectUserAndAccountByUserId(@Param("id") Integer id);
}

3、编写xml配置文件

使用association标签处理复杂的javaBean

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace = 所需实现的接口全限定名-->
<mapper namespace="com.cxs.mapper.UserMapper"><resultMap id="selectUserAndAccountByUserIdMap" type="com.cxs.bo.UserAccountInfoBO"><association property="userInfo" javaType="com.cxs.model.User"><id property="id" column="id" jdbcType="INTEGER"/><result property="userName" column="user_name" jdbcType="VARCHAR"/><result property="age" column="age" jdbcType="INTEGER"/><result property="createTime" column="create_time" jdbcType="TIMESTAMP"/></association><association property="userAccountInfo" javaType="com.cxs.model.UserAccount"><id property="accId" column="acc_id" jdbcType="INTEGER"/><result property="accBalance" column="acc_balance" jdbcType="INTEGER"/><result property="userId" column="user_id" jdbcType="INTEGER"/></association></resultMap><select id="selectUserAndAccountByUserId" resultMap="selectUserAndAccountByUserIdMap" parameterType="java.lang.Integer">select *from user uinner join user_account uaon u.id = ua.user_idwhere u.id = #{id}</select>
</mapper>

4、测试

需求二：查询用户id为1的用户信息及用户的照片信息(一对多)

1、创建UserPhotoInfoBO业务封装对象

/*** @Project: mybatis-wx-demo* @Author: cxs2014501@163.com* @Create: 2023/3/10 11:40* @Description:**/
@Data
public class UserPhotoInfoBO {private Integer id;private String userName;private Integer age;private LocalDateTime createTime;private List<UserPhoto> userPhotoList;
}

2、编写接口

public interface UserMapper {UserAccountInfoBO selectUserAndAccountByUserId(@Param("id") Integer id);UserPhotoInfoBO selectUserAndPhotoByUserId(@Param("id") Integer id);
}

3、编写xml配置文件

使用collection标签封装Bean集合，照片是多个

<resultMap id="selectUserAndPhotoByUserIdMap" type="com.cxs.bo.UserPhotoInfoBO"><id property="id" column="id" jdbcType="INTEGER"/><result property="userName" column="user_name" jdbcType="VARCHAR"/><result property="age" column="age" jdbcType="INTEGER"/><result property="createTime" column="create_time" jdbcType="TIMESTAMP"/><collection property="userPhotoList" javaType="java.util.List" ofType="com.cxs.model.UserPhoto"><id property="id" column="photo_id" jdbcType="INTEGER"/><result property="url" column="url" jdbcType="VARCHAR"/><result property="userId" column="user_id" jdbcType="INTEGER"/></collection></resultMap><select id="selectUserAndPhotoByUserId" resultMap="selectUserAndPhotoByUserIdMap" parameterType="java.lang.Integer">select u.*,up.id as photo_id,up.user_id,up.urlfrom user uinner join user_photo upon u.id = up.user_idwhere u.id = #{id}</select>

4、测试

需求三：查询用户id为1的用户信息及用户的角色信息(多对多)

1、创建UserRoleInfoBO业务封装对象

/*** @Project: mybatis-wx-demo* @Author: cxs2014501@163.com* @Create: 2023/3/10 13:19* @Description:**/
@Data
public class UserRoleInfoBO {private Integer id;private String userName;private Integer age;private LocalDateTime createTime;private List<Role> roleList;
}

2、编写接口

/*** @Project: mybatis-wx-demo* @Author: cxs2014501@163.com* @Create: 2023/2/28 10:41* @Description:**/
public interface UserMapper {UserAccountInfoBO selectUserAndAccountByUserId(@Param("id") Integer id);UserPhotoInfoBO selectUserAndPhotoByUserId(@Param("id") Integer id);UserRoleInfoBO selectUserAndRoleListByUserId(@Param("id") Integer id);
}

3、编写xml配置文件

<resultMap id="selectUserAndRoleListByUserIdMap" type="com.cxs.bo.UserRoleInfoBO"><id property="id" column="id" jdbcType="INTEGER"/><result property="userName" column="user_name" jdbcType="VARCHAR"/><result property="age" column="age" jdbcType="INTEGER"/><result property="createTime" column="create_time" jdbcType="TIMESTAMP"/><collection property="roleList" javaType="java.util.List" ofType="com.cxs.model.Role"><id property="roleId" column="role_id" jdbcType="INTEGER"/><result property="roleName" column="role_name" jdbcType="VARCHAR"/></collection></resultMap><select id="selectUserAndRoleListByUserId" resultMap="selectUserAndRoleListByUserIdMap" parameterType="java.lang.Integer">select u.*, r.*from user uleft join user_role ur on u.id = ur.u_idinner join role r on ur.r_id = r.role_idwhere u.id = #{id}</select>