MyBatis多表关联查询

最新推荐文章于 2024-08-15 17:49:57 发布

ronybo

最新推荐文章于 2024-08-15 17:49:57 发布

阅读量303

点赞数

分类专栏： MyBatis 文章标签： MyBatis SQL JOIN 多表查询

本文链接：https://blog.csdn.net/weixin_43254907/article/details/89218420

版权

MyBatis 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

根据用户id获取用户拥有的所有角色，，返回的结果为角色集合，结果只有角色的信息，不包含额外的其他字段信息。
这个方法涉及sys_user，sys_role和sys_user_role这3个表：

sys_user表：
sys_role表：
sys_user_role表：

通过查询三个表：

<select id="selectRoleByUserId" resultType="sysrole">
	select r.id,
		   r.role_name,
		   r.enabled,
		   r.create_by,
		   r.create_time
	from sys_user u,
		 sys_role r,
		 sys_user_role ur
	where u.id = ur.user_id 
	and   r.id = ur.role_id
	and   u.id = #{userId}
</select>

DEBUG [main] - ==>  Preparing: select r.id,
								      r.role_name,
									  r.enabled,
									  r.create_by,
									  r.create_time
							   from sys_user u,
							   		sys_role r,
							   		sys_user_role ur 
							   where u.id = ur.user_id and r.id = ur.role_id and u.id = ?; 
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <==    Columns: id, role_name, enabled, create_by, create_time
TRACE [main] - <==        Row: 1, 管理员, 1, 1, 2016-04-01 17:02:14.0
TRACE [main] - <==        Row: 2, 普通用户, 1, 1, 2016-04-01 17:02:34.0
DEBUG [main] - <==      Total: 2
SysRole{id=1, roleName='管理员', enabled=1, createBy=1, createTime=Fri Apr 01 17:02:14 CST 2016}
SysRole{id=2, roleName='普通用户', enabled=1, createBy=1, createTime=Fri Apr 01 17:02:34 CST 2016}

Process finished with exit code 0

通过JOIN
2.1 INNER JOIN：就是JOIN。
2.2 LEFT JOIN：会从左表返回所有行，即时右表没有对应的数据（会空出来）。
2.3 RIGHT JOIN：和LEFT JOIN相反。
2.4 FULL JOIN：全连接。返回左表对应的行和右表对应的行。没有查询到对应的列返回空。

<select id="selectRoleByUserId" resultType="sysrole">
	select r.id,
		   r.role_name,
		   r.enabled,
		   r.create_by,
		   r.create_time
	from sys_user u
	inner join sys_user_role ur on u.id = ur.user_id
	inner join sys_role r on r.id = ur.role_id
	where u.id = #{userId}
</select>

DEBUG [main] - ==>  Preparing: select r.id, 
									  r.role_name, 
									  r.enabled, 
									  r.create_by, 
									  r.create_time 
							   from sys_user u 
							   inner join sys_user_role ur on ur.user_id = u.id 
							   inner join sys_role r on ur.role_id = r.id 
							   where u.id = ? 
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <==    Columns: id, role_name, enabled, create_by, create_time
TRACE [main] - <==        Row: 1, 管理员, 1, 1, 2016-04-01 17:02:14.0
TRACE [main] - <==        Row: 2, 普通用户, 1, 1, 2016-04-01 17:02:34.0
DEBUG [main] - <==      Total: 2
SysRole{id=1, roleName='管理员', enabled=1, createBy=1, createTime=Fri Apr 01 17:02:14 CST 2016}
SysRole{id=2, roleName='普通用户', enabled=1, createBy=1, createTime=Fri Apr 01 17:02:34 CST 2016}

Process finished with exit code 0

如果想同时展示sys_role的信息和sys_user的部分信息
3.1 新建一个类继承SysRole类，resultType写成SysRoleExtend：

public class SysRoleExtend extends SysRole {
	private String userName;
	getter,setter...
}

3.2 直接在SysRole类中增加SysUser对象

public class SysRole {
	// 其他原有字段
	/**
	 * 用户信息
	 * /
	private SysUser user;
}

<select id="selectRolesByUserId" resultType="sysrole">
   select r.id,
          r.role_name,
          r.enabled,
          r.create_by,
          r.create_time,
          u.user_name as "user.userName",
          u.user_email as "user.userEmail"
   from sys_user u
   inner join sys_user_role ur on u.id = ur.user_id
   inner join sys_role r on r.id = ur.role_id
   where u.id = #{userId}
</select>

测试：

    @Test
    public void testSelectRolesByUserId() {
        SqlSession session = getSqlSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<SysRole> sysRoles = userMapper.selectRolesByUserId(1L);
        Assert.assertNotNull(sysRoles);
    }

结果：

DEBUG [main] - ==>  Preparing: select r.id, r.role_name, r.enabled, r.create_by, r.create_time, u.user_name as "user.userName", u.user_email as "user.userEmail" from sys_user u inner join sys_user_role ur on u.id = ur.user_id inner join sys_role r on r.id = ur.role_id where u.id = ? 
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <==    Columns: id, role_name, enabled, create_by, create_time, user.userName, user.userEmail
TRACE [main] - <==        Row: 1, 管理员, 1, 1, 2016-04-01 17:02:14.0, admin, admin@mybatis.tk
TRACE [main] - <==        Row: 2, 普通用户, 1, 1, 2016-04-01 17:02:34.0, admin, admin@mybatis.tk
DEBUG [main] - <==      Total: 2

Process finished with exit code 0