springboot -- mybatis 之多对多级联查询

---

mybatis 多对多级联查询

 

示例：

user：id、name、age

role：id、role

一个user可以有多个role，一个role可以对应多个user

 

---

pojo

 

public class UserMap {

    private Integer id;
    private String name;
    private Integer age;

    private List<Role> roleList;

    。。。。

}



public class RoleMap {

    private Integer id;
    private String role;

    private List<User> userList;

    。。。。

}

 

***********************************************

dao层接口

 

public interface UserMapper extends Mapper<User> {

    User getUserByRoleId(@Param("id") Integer id);

    List<UserMap> getAllUserMap();
}



public interface RoleMapper extends Mapper<Role> {

    Role getRoleByUserId(@Param("id") Integer id);

    List<RoleMap> getAllRoleMap();
}

 

*************************************************

xml文件

 

userMapper.xml

  <resultMap id="userMap" type="userMap">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <association property="roleList" column="id" select="com.example.demo.dao.RoleMapper.getRoleByUserId"/>
  </resultMap>

  <select id="getUserByRoleId" resultMap="BaseResultMap">
    select * from user u,user_role_relation ur where u.id=ur.user_id and ur.role_id = #{id};
  </select>

  <select id="getAllUserMap" resultMap="userMap">
    select * from user;
  </select>

***********************************

roleMapper.xml

  <resultMap id="roleMap" type="roleMap">
    <id column="id" property="id"/>
    <result column="role" property="role"/>
    <collection property="userList" column="id" select="com.example.demo.dao.UserMapper.getUserByRoleId"/>
  </resultMap>

  <select id="getRoleByUserId" resultMap="BaseResultMap">
    select * from role r,user_role_relation ur where r.id=ur.role_id and ur.user_id = #{id};
  </select>

  <select id="getAllRoleMap" resultMap="roleMap">
    select * from role;
  </select>

 

*******************************************

controller层

 

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @RequestMapping("/get2")
    public List<UserMap> get2(){
        return userMapper.getAllUserMap();
    }
}


**********************************

@RestController
public class RoleController {

    @Autowired
    private RoleMapper roleMapper;

    @RequestMapping("/get")
    public List<RoleMap> get(){
        return roleMapper.getAllRoleMap();
    }
}

 

---

输出测试