MyBatis的多对多映射(十)下-阿里云开发者社区

MyBatis的多对多映射(十)下

2021-12-27 144

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： MyBatis的多对多映射(十)

六. 员工去查询角色

UserMapper.java 的接口:

public User getUserRoleById(int id);

UserMapper.xml sql语句:

<resultMap type="user" id="userResultMapWithUserRole">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="sex" column="sex"/>
    <result property="age" column="age"/>
    <result property="description" column="description"/>
    <!--引入集合为userRole的集合-->
    <collection property="userRole" javaType="ArrayList" ofType="userRole"
    column="id" select="com.yjl.mapper.UserRoleMapper.findByUserId">
    </collection>
  </resultMap>
  <select id="getUserRoleById" parameterType="int" resultMap="userResultMapWithUserRole">
    select * from user where id=#{id}
  </select>

UserRoleMapper.java 的接口为:

package com.yjl.mapper;
import org.apache.ibatis.annotations.Param;
import com.yjl.pojo.UserRole;
/**
@atuhor:yuejl
@Description: 类描述
*/
public interface UserRoleMapper {
  public UserRole findByUserId(@Param(value="userId") int userId);
}

UserRoleMapper.xml 文件为:

<?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="com.yjl.mapper.UserRoleMapper">
  <resultMap type="userRole" id="userRoleMap">
    <result property="created_by" column="created_by" javaType="string"/>
    <result property="created_date" column="created_date" javaType="date"/>
    <!-- 关联一下，相应的角色信息,为一对一的形式 -->
    <association property="role" javaType="role" column="roleId"
    select="com.yjl.mapper.RoleMapper.findById"></association>
  </resultMap>
  <select id="findByUserId" parameterType="int" resultMap="userRoleMap">
    select t.* from user_role t where t.userId=#{userId}
  </select>
</mapper>

RoleMapper.java 的接口为:

public Role findById(int id);

RoleMapper.xml 的sql语句为:

<resultMap type="role" id="roleResultMap">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
</resultMap>
<select id="findById" parameterType="int" resultMap="roleResultMap">
    select * from role t where t.id=#{id}
  </select>

测试方法:

@Test
  public void getUserRoleByIdTest(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    //查询该员工所具有的角色
    User user=userMapper.getUserRoleById(1);
    List<UserRole> roleList=user.getUserRole();
    System.out.println(user);
    for (UserRole userRole : roleList) {
      System.out.println("输出创建人:"+userRole.getCreated_by());
      System.out.println("输出创建日期:"+userRole.getCreated_date());
      Role role=userRole.getRole();
      System.out.println(role);
    }
  }

这是员工找角色的，也可以角色去找员工。与上面的思路差不多，就不讲解了。当然，也有对应的result 的形式。

UserMapper.java 接口:

public User getUserRoleByIdWithResult(int id);

UserMapper.xml sql语句:

<resultMap type="user" id="userResultMapWithUserRoleResult">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="sex" column="sex"/>
    <result property="age" column="age"/>
    <result property="description" column="description"/>
    <collection property="userRole" ofType="userRole" javaType="arrayList">
      <result property="created_by" column="created_by" javaType="string"/>
      <result property="created_date" column="created_date" javaType="date"/>
      <!-- 对角色的一对一 -->
      <association property="role" javaType="role">
        <id property="id" column="rId" javaType="int"/>
        <result property="name" column="rName" javaType="string"/>
      </association>
    </collection>
  </resultMap>
<select id="getUserRoleByIdWithResult" parameterType="int" resultMap="userResultMapWithUserRoleResult">
    select u.id as id,u.name as name,u.sex as sex,u.age as age,u.description as description,
    ur.created_by as created_by,ur.created_date as created_date,
    r.id as rId,r.name as rName,
    ur.userId as userId,ur.roleId as roleId from user u,role r,user_role ur where u.id=ur.userId and r.id=ur.roleId
    and u.id=#{id}
  </select>

测试方法:

@Test
  public void getUserRoleByIdWithResultTest(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    //查询该员工所具有的角色
    User user=userMapper.getUserRoleByIdWithResult(1);
    List<UserRole> roleList=user.getUserRole();
    System.out.println(user);
    System.out.println("长度是:"+roleList.size());
    for (UserRole userRole : roleList) {
      System.out.println("输出创建人:"+userRole.getCreated_by());
      System.out.println("输出创建日期:"+userRole.getCreated_date());
      Role role=userRole.getRole();
      System.out.println(role);
    }
  }