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