springboot mybatis 多对多级联查询

---

mybatis 多对多级联查询

 

 

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

示例

 

user：id、name、age

role：id、role

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

 

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

pojo 层

 

UserMap

public class UserMap {

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

    private List<Role> roleList;

    。。。。

}

 

RoleMap

public class RoleMap {

    private Integer id;
    private String role;

    private List<User> userList;

    。。。。

}

 

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

dao 层

 

UserMapper

public interface UserMapper extends Mapper<User> {

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

    List<UserMap> getAllUserMap();
}

 

RoleMapper

public interface RoleMapper extends Mapper<Role> {

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

    List<RoleMap> getAllRoleMap();
}

 

UserRoleRelationMapper

package com.example.demo.dao;

import com.example.demo.pojo.UserRoleRelation;
import tk.mybatis.mapper.common.Mapper;

public interface UserRoleRelationMapper extends Mapper<UserRoleRelation> {
}

 

 

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

xml文件

 

UserMapper.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.example.demo.dao.UserMapper">
  <resultMap id="BaseResultMap" type="com.example.demo.pojo.User">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="age" jdbcType="INTEGER" property="age" />
  </resultMap>

  <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>
</mapper>

 

RoleMapper.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.example.demo.dao.RoleMapper">
  <resultMap id="BaseResultMap" type="com.example.demo.pojo.Role">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="role" jdbcType="VARCHAR" property="role" />
  </resultMap>

  <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>
</mapper>

 

UserRoleRelationMapper.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.example.demo.dao.UserRoleRelationMapper">
  <resultMap id="BaseResultMap" type="com.example.demo.pojo.UserRoleRelation">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="user_id" jdbcType="INTEGER" property="userId" />
    <result column="role_id" jdbcType="INTEGER" property="roleId" />
  </resultMap>
</mapper>

 

 

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

controller 层

 

UserController

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

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

 

RoleController

@RestController
public class RoleController {

    @Autowired
    private RoleMapper roleMapper;

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

 

 

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

使用测试

                                       

localhost:8080/get

                                       

 

localhost:8080/get2