MyBatisXML方式的基本用法之多表查询功能的示例代码

/**
 * 根据用户id获取角色信息
 *
 * @param userId
 * @return
 */
List selectRolesByUserId(Long userId);

 SELECT r.id,
  r.role_name roleName,
  r.enabled,
  r.create_by createBy,
  r.create_time createTime
 FROM sys_user u
 INNER JOIN sys_user_role ur ON u.id = ur.user_id
 INNER JOIN sys_role r ON ur.role_id = r.id
 WHERE u.id = #{userId}

private String userName;
public String getUserName() {
 return userName;
}
public void setUserName(String userName) {
 this.userName = userName;
}

package com.zwwhnly.mybatisaction.model;
public class SysRoleExtend extends SysRole {
 private String userName;
 public String getUserName() {
 return userName;
 }
 public void setUserName(String userName) {
 this.userName = userName;
 }
}

private SysUser sysUser;
public SysUser getSysUser() {
 return sysUser;
}
public void setSysUser(SysUser sysUser) {
 this.sysUser = sysUser;
}

package com.zwwhnly.mybatisaction.model;
public class SysRoleExtend extends SysRole {
 private SysUser sysUser;
 public SysUser getSysUser() {
 return sysUser;
 }
 public void setSysUser(SysUser sysUser) {
 this.sysUser = sysUser;
 }
}

 SELECT r.id,
  r.role_name roleName,
  r.enabled,
  r.create_by createBy,
  r.create_time createTime,
  u.user_name "sysUser.userName",
  u.user_email "sysUser.userEmail"
 FROM sys_user u
 INNER JOIN sys_user_role ur ON u.id = ur.user_id
 INNER JOIN sys_role r ON ur.role_id = r.id
 WHERE u.id = #{userId}

@Test
public void testSelectRolesByUserId() {
 SqlSession sqlSession = getSqlSession();
 try {
 SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
 List sysRoleList = sysUserMapper.selectRolesByUserId(1L);
 Assert.assertNotNull(sysRoleList);
 Assert.assertTrue(sysRoleList.size() > 0);
 } finally {
 sqlSession.close();
 }
}

DEBUG [main] - ==> Preparing: SELECT r.id, r.role_name roleName, r.enabled, r.create_by createBy, r.create_time createTime, u.user_name "sysUser.userName", u.user_email "sysUser.userEmail" FROM sys_user u INNER JOIN sys_user_role ur ON u.id = ur.user_id INNER JOIN sys_role r ON ur.role_id = r.id WHERE u.id = &＃63;
DEBUG [main] - ==> Parameters: 1(Long)
TRACE [main] - <== Columns: id, roleName, enabled, createBy, createTime, sysUser.userName, sysUser.userEmail
TRACE [main] - <== Row: 1, 管理员, 1, 1, 2019-06-27 18:21:12.0, admin, admin@mybatis.tk
TRACE [main] - <== Row: 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0, admin, admin@mybatis.tk
DEBUG [main] - <== Total: 2

/**
 * 根据用户id和角色的enabled状态获取用户的角色
 *
 * @param userId
 * @param enabled
 * @return
 */
List selectRolesByUserIdAndRoleEnabled(Long userId,Integer enabled);

 SELECT r.id,
  r.role_name roleName,
  r.enabled,
  r.create_by createBy,
  r.create_time createTime
 FROM sys_user u
 INNER JOIN sys_user_role ur ON u.id = ur.user_id
 INNER JOIN sys_role r ON ur.role_id = r.id
 WHERE u.id = #{userId} AND r.enabled = #{enabled}

@Test
public void testselectRolesByUserIdAndRoleEnabled() {
 SqlSession sqlSession = getSqlSession();
 try {
 SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
 List sysRoleList = sysUserMapper.selectRolesByUserIdAndRoleEnabled(1L, 1);
 Assert.assertNotNull(sysRoleList);
 Assert.assertTrue(sysRoleList.size() > 0);
 } finally {
 sqlSession.rollback();
 sqlSession.close();
 }
}

WHERE u.id = #{0} AND r.enabled = #{1}

WHERE u.id = #{param1} AND r.enabled = #{param2}

/**
 * 根据用户id和角色的enabled状态获取用户的角色
 *
 * @param userId
 * @param enabled
 * @return
 */
List selectRolesByUserIdAndRoleEnabled(@Param("userId") Long userId, @Param("enabled") Integer enabled);

DEBUG [main] - ==> Preparing: SELECT r.id, r.role_name roleName, r.enabled, r.create_by createBy, r.create_time createTime FROM sys_user u INNER JOIN sys_user_role ur ON u.id = ur.user_id INNER JOIN sys_role r ON ur.role_id = r.id WHERE u.id = &＃63; AND r.enabled = &＃63;
DEBUG [main] - ==> Parameters: 1(Long), 1(Integer)
TRACE [main] - <== Columns: id, roleName, enabled, createBy, createTime
TRACE [main] - <== Row: 1, 管理员, 1, 1, 2019-06-27 18:21:12.0
TRACE [main] - <== Row: 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0
DEBUG [main] - <== Total: 2

/**
 * 根据用户id和角色的enabled状态获取用户的角色
 *
 * @param user
 * @param role
 * @return
 */
List selectRolesByUserAndRole(@Param("user") SysUser user, @Param("role") SysRole role);

 SELECT r.id,
 r.role_name roleName,
 r.enabled,
 r.create_by createBy,
 r.create_time createTime
 FROM sys_user u
 INNER JOIN sys_user_role ur ON u.id = ur.user_id
 INNER JOIN sys_role r ON ur.role_id = r.id
 WHERE u.id = #{user.id} AND r.enabled = #{role.enabled}