MyBatis声明式一对多映射

1. 一对多映射

1.1 数据库的创建

# 创建用户表
CREATE TABLE IF NOT EXISTS `user`(
  `id` INT AUTO_INCREMENT,
  `username` VARCHAR(32) NOT NULL,
  `birthday` DATETIME NOT NULL,
  `sex` CHAR(1) NOT NULL,
  `address` VARCHAR(256),
  CONSTRAINT pk_user PRIMARY KEY(`id`)
);

# 添加用户数据
INSERT  INTO `user`(`id`,`username`,`birthday`,`sex`,`address`) VALUES 
(NULL,'周瑜','2020-02-27 17:47:08','男','吴国'),
(NULL,'大乔','2020-02-27 15:09:37','女','吴国'),
(NULL,'小乔','2020-02-27 11:34:34','女','吴国'),
(NULL,'陆逊','2020-02-27 12:04:06','男','吴国'),
(NULL,'孙策','2020-02-27 17:37:26','男','东吴'),
(NULL,'孙尚香','2020-02-27 11:44:00','女','吴国'),
(NULL,'曹操','2020-07-29 16:36:26','男','魏国');

# 创建角色表
CREATE TABLE IF NOT EXISTS `role`(
  `id` INT AUTO_INCREMENT,
  `role_name` VARCHAR(30) NOT NULL,
  `role_desc` VARCHAR(60) NOT NULL,
  CONSTRAINT pk_role PRIMARY KEY(`id`)
);

# 添加角色数据
INSERT  INTO `role`(`id`,`role_name`,`role_desc`) VALUES 
(1,'CEO','管理整个公司'),
(2,'总裁','管理整个集团'),
(3,'总监','管理整个分公司');

# 创建用户与角色关系表
CREATE TABLE IF NOT EXISTS `user_role`(
  `user_id` INT,
  `role_id` INT,
  CONSTRAINT pk_user_role PRIMARY KEY(`user_id`,`role_id`),
  CONSTRAINT fk_user_role_user FOREIGN KEY(`user_id`) REFERENCES `user`(`id`),
  CONSTRAINT fk_user_role_role FOREIGN KEY(`role_id`) REFERENCES `role`(`id`)
);

# 添加用户角色对应数据
INSERT  INTO `user_role`(`user_id`,`role_id`) VALUES 
(1,1),
(5,1),
(1,3);

1.2 表与表的对应关系

1.3 一对多查询需求

关系：一个用户有多个角色，一个角色有多个用户，他们通过用户角色表进行关联

需求：根据用户编号查询指定用户信息及其角色信息

SQL：

select
 t1.id id,
 t1.username userName,
 t1.birthday birthday,
 t1.sex sex,
 t1.address address,
 t3.id roleId,
 t3.role_name roleName,
 t3.role_desc roleDesc
from
 `user` t1
left outer join
 `user_role` t2
on
 t1.`id` = t2.`user_id`
left outer join
 `role` t3
on
 t2.`role_id` = t3.`id`
where
 t1.`id` = 1;

2. 用户实体类

package zw.mybatis.bean.domain;

import java.io.Serializable;
import java.util.Date;

/**
 * @className UserDO
 * @description 用户实体类
 * @author 周威
 * @date 2020-07-31 14:14
 **/
public class UserDO implements Serializable
{
    /* 用户编号 */
    private Integer id;
    /* 用户姓名 */
    private String userName;
    /* 用户生日 */
    private Date birthday;
    /* 用户性别 */
    private String sex;
    /* 用户地址 */
    private String address;

    /* Getter和Setter方法 */
    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 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;
    }

    /* toString()方法 */
    @Override
    public String toString()
    {
        return "UserDO{" + "id=" + id + ", userName='" + userName + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}';
    }
}

3. 角色实体类

package zw.mybatis.bean.domain;

/**
 * @className RoleDO
 * @description 角色实体类
 * @author 周威
 * @date 2020-08-04 8:58
 **/
public class RoleDO
{
    /* 角色编号 */
    private Integer id;
    /* 角色名称 */
    private String roleName;
    /* 角色描述 */
    private String roleDesc;

    /* Getter和Setter方法 */
    public Integer getId()
    {
        return id;
    }

    public void setId(Integer id)
    {
        this.id = id;
    }

    public String getRoleName()
    {
        return roleName;
    }

    public void setRoleName(String roleName)
    {
        this.roleName = roleName;
    }

    public String getRoleDesc()
    {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc)
    {
        this.roleDesc = roleDesc;
    }

    /* toString()方法 */
    @Override
    public String toString()
    {
        return "RoleDO{" + "id=" + id + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}';
    }
}

4. 用户展示实体类

package zw.mybatis.bean.vo;

import zw.mybatis.bean.domain.RoleDO;
import zw.mybatis.bean.domain.UserDO;
import java.util.List;

/**
 * @className UserVO
 * @description 用户展示实体类
 * @author 周威
 * @date 2020-08-03 18:31
 **/
public class UserVO extends UserDO
{
    /* 角色列表 */
    private List<RoleDO> rolesDO;

    /* Getter和Setter方法 */

    public List<RoleDO> getRolesDO()
    {
        return rolesDO;
    }

    public void setRolesDO(List<RoleDO> rolesDO)
    {
        this.rolesDO = rolesDO;
    }

    /* toString()方法 */
    @Override
    public String toString()
    {
        return "UserVO{UserDO=" + super.toString() + "rolesDO=" + rolesDO + '}';
    }
}

5. 用户持久层接口

package zw.mybatis.mapper;

import zw.mybatis.bean.vo.UserVO;

/**
 * @className UserMapper
 * @description 用户持久层接口
 * @author 周威
 * @date 2020-07-31 14:18
 **/
public interface UserMapper
{
    /**
     * 查询用户信息，根据用户编号查询指定用户信息及其角色信息
     * @param userId
     * @return 用户信息
     */
    UserVO findUserInformationAndRoleByUserId(Integer userId);

}

6. 用户映射文件

<?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="zw.mybatis.mapper.UserMapper">
    <!-- 输出映射 -->
    <resultMap id="userVOResultMap" type="zw.mybatis.bean.vo.UserVO">
        <!-- 用户信息映射 -->
        <id column="id" property="id" javaType="java.lang.Integer"/>
        <result column="userName" property="userName" javaType="java.lang.String"/>
        <result column="birthday" property="birthday" javaType="java.util.Date"/>
        <result column="sex" property="sex" javaType="java.lang.String"/>
        <result column="address" property="address" javaType="java.lang.String"/>
        <!-- 用户与角色一对多映射 -->
        <collection property="rolesDO" ofType="zw.mybatis.bean.domain.RoleDO">
            <id column="roleId" property="id" javaType="java.lang.Integer"/>
            <result column="roleName" property="roleName" javaType="java.lang.String"/>
            <result column="roleDesc" property="roleDesc" javaType="java.lang.String"/>
        </collection>
    </resultMap>

    <!-- 查询用户信息，根据用户编号查询指定用户信息及其角色信息 -->
    <select id="findUserInformationAndRoleByUserId" parameterType="java.lang.Integer" resultMap="userVOResultMap">
        select
         t1.id id,
         t1.username userName,
         t1.birthday birthday,
         t1.sex sex,
         t1.address address,
         t3.id roleId,
         t3.role_name roleName,
         t3.role_desc roleDesc
        from
         `user` t1
        left outer join
         `user_role` t2
        on
         t1.`id` = t2.`user_id`
        left outer join
         `role` t3
        on
         t2.`role_id` = t3.`id`
        where
         t1.`id` = #{userId}
    </select>

</mapper>

7. 用户持久层测试类

package zw.mybatis.mapper;

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 org.junit.Test;
import zw.mybatis.bean.vo.UserVO;
import java.io.IOException;
import java.io.InputStream;

/**
 * @className UserMapperTest
 * @description 用户持久层测试类
 * @author 周威
 * @date 2020-07-31 14:27
 **/
public class UserMapperTest
{
    @Test
    public void findUserInformationAndAccountByUserIdTest()
    {
        InputStream is = null;
        SqlSession sqlSession = null;
        try
        {
            // 读取配置文件
            is = Resources.getResourceAsStream("mybatis/mybatis.xml");
            // 创建SqlSessionFactoryBuilder对象
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            // 获取SqlSessionFactory对象
            SqlSessionFactory factory = builder.build(is);
            // 获取SqlSession对象
            sqlSession = factory.openSession();
            // 获取代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 查询数据记录
            UserVO userVO = userMapper.findUserInformationAndRoleByUserId(1);
            // 打印查询结果
            System.out.println("userVO = " + userVO);
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                // 关闭资源
                if(sqlSession != null)
                    sqlSession.close();
                if(is != null)
                    is.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }
    }
}

8. 运行结果