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 一对多查询需求

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

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

2. 用户实体类

package zw.mybatis.bean.domain;

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

/**
 * @ClassName UserDO
 * @Description 用户实体类
 * @Author 周威
 * @Date 2020-08-14 - 22:02
 */
@Data
public class UserDO implements Serializable
{
    /* 用户编号 */
    private Integer id;
    /* 用户姓名 */
    private String userName;
    /* 用户生日 */
    private Date birthday;
    /* 用户性别 */
    private String sex;
    /* 用户住址 */
    private String address;
}

3. 角色实体类

package zw.mybatis.bean.domain;

import lombok.Data;
import java.io.Serializable;

/**
 * @ClassName RoleDO
 * @Description 角色实体类
 * @Author 周威
 * @Date 2020-08-14 - 22:04
 */
@Data
public class RoleDO implements Serializable
{
    /* 角色编号 */
    private Integer id;
    /* 角色名称 */
    private String roleName;
    /* 角色描述 */
    private String roleDesc;
}

4. 用户展示实体类

package zw.mybatis.bean.vo;

import lombok.Data;
import zw.mybatis.bean.domain.RoleDO;
import zw.mybatis.bean.domain.UserDO;
import java.io.Serializable;
import java.util.List;

/**
 * @ClassName UserVO
 * @Description 用户展示实体类
 * @Author 周威
 * @Date 2020-08-14 - 22:06
 */
@Data
public class UserVO extends UserDO implements Serializable
{
    /* 角色信息 */
    private List<RoleDO> rolesDO;
}

5. 角色持久层接口

package zw.mybatis.mapper;

import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import zw.mybatis.bean.domain.RoleDO;
import java.util.List;

/**
 * @ClassName RoleMapper
 * @Description 角色持久层接口
 * @Author 周威
 * @Date 2020-08-14 - 22:08
 */
public interface RoleMapper
{
    /**
     * 查询角色信息，根据用户编号查询指定角色信息列表
     * @param userId
     * @return 角色信息列表
     */
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "roleName", property = "roleName"),
            @Result(column = "roleDesc", property = "roleDesc")
    })
    @Select("select t1.id id, t1.role_name roleName, t1.role_desc roleDesc from role t1 inner join user_role t2 on t1.id = t2.role_id where t2.user_id = #{userId}")
    List<RoleDO> listRoleInformationByUserId(Integer userId);
}

6. 用户持久层接口

package zw.mybatis.mapper;

import org.apache.ibatis.annotations.*;
import zw.mybatis.bean.vo.UserVO;

/**
 * @ClassName UserMapper
 * @Description 用户持久层接口
 * @Author 周威
 * @Date 2020-08-14 - 22:15
 */
public interface UserMapper
{
    /**
     * 查询用户信息，根据用户编号查询指定用户信息及其角色信息
     * @param userId
     * @return 用户信息
     */
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "username", property = "userName"),
            @Result(column = "birthday", property = "birthday"),
            @Result(column = "sex", property = "sex"),
            @Result(column = "address", property = "address"),
            @Result(column = "id", property = "rolesDO", many = @Many(select = "zw.mybatis.mapper.RoleMapper.listRoleInformationByUserId"))
    })
    @Select("select * from user where id = #{userId}")
    UserVO findUserInformationAndRoleByUserId(Integer userId);
}

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.domain.RoleDO;
import zw.mybatis.bean.vo.UserVO;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @ClassName UserMapperTest
 * @Description 用户持久层测试类
 * @Author 周威
 * @Date 2020-08-14 - 22:20
 */
public class UserMapperTest
{
    @Test
    public void findUserInformationAndRoleByUserIdTest()
    {
        InputStream is = null;
        SqlSession sqlSession = null;
        try
        {
            // 读取mybatis配置文件
            is = Resources.getResourceAsStream("mybatis/mybatis.xml");
            // 创建SqlSessionFactoryBuilder对象
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            // 获取SQLSessionFactory对象
            SqlSessionFactory factory = builder.build(is);
            // 获取SqlSession对象
            sqlSession = factory.openSession();
            // 获取UserMapper代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 查询数据记录
            UserVO userVO = userMapper.findUserInformationAndRoleByUserId(1);
            // 打印用户信息
            System.out.println("用户编号：" + userVO.getId());
            System.out.println("用户姓名：" + userVO.getUserName());
            System.out.println("用户生日：" + userVO.getBirthday());
            System.out.println("用户性别：" + userVO.getSex());
            System.out.println("用户住址：" + userVO.getAddress());
            // 打印用户角色信息
            List<RoleDO> rolesDO = userVO.getRolesDO();
            for (RoleDO roleDO : rolesDO)
            {
                System.out.println("角色编号：" + roleDO.getId());
                System.out.println("角色名称：" + roleDO.getRoleName());
                System.out.println("角色描述：" + roleDO.getRoleDesc());
            }
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                // 关闭资源
                if (sqlSession != null)
                    sqlSession.close();
                if (is != null)
                    is.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }
    }
}

8. 运行结果