MyBatis多表关联查询

野性酷女 2022-04-24 02:42 382阅读 0赞

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

*  `sys_user`表：  
    ![20190411174803836.png][]
 *  `sys_role`表：  
    ![201904111749041.png][]
 *  `sys_user_role`表：  
    ![20190411174956603.png][]

1.  通过查询三个表：
    
        <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

1.  通过`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

1.  如果想同时展示`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

[20190411174803836.png]: /images/20220221/c7bc88b49bd342e5a9690669a2fab8e8.png
[201904111749041.png]: /images/20220221/9340c82dbef54fdf84f4ede111c7bf08.png
[20190411174956603.png]: /images/20220221/d46f36e4a4924115afbafd35df14191b.png