MyBatis教程:XML方式的基本用法之多表查询

2019-07-1806:27:10后端程序开发Comments2,106 views字数 5542阅读模式

1. 多表查询

上篇博客中,我们示例的2个查询都是单表查询,但实际的业务场景肯定是需要多表查询的,比如现在有个需求:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

查询某个用户拥有的所有角色。这个需求要涉及到sys_user,sys_user_role,sys_role三张表,如何实现呢?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

首先,在SysUserMapper接口中定义如下方法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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

然后打开对应的SysUserMapper.xml文件,添加如下select语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

<select id="selectRolesByUserId" resultType="com.zwwhnly.mybatisaction.model.SysRole">
    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}
</select>

细心的读者可能会发现,我们虽然使用到了多表查询,但是resultType设置的仍然是单表,即只包含角色表的信息。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

如果我希望这个查询语句同时返回SysUser表的user_name字段呢,该如何设置resultType?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

方法1:直接在SysRole实体类中添加userName字段。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

private String userName;

public String getUserName() {
    return userName;
}

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

此时resultType不用修改。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

方法2:新建扩展类,在扩展类中添加userName字段。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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;
    }
}

此时需要将resultType修改为:com.zwwhnly.mybatisaction.model.SysRoleExtend。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

这种方式比较适合需要少量额外字段的场景。如果需要其他表的大量字段,可以使用方式3或者方式4,个人推荐使用方式4。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

方法3:在SysRole实体类中添加SysUser类型的字段。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

private SysUser sysUser;

public SysUser getSysUser() {
   return sysUser;
}

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

此时resultType不用修改。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

方法4(推荐使用):新建扩展类,在扩展类中添加SysUser类型的字段。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

书中推荐的是方式3,方式4是我个人认为更好的方式,因为实体类一般由工具自动生成,增加了字段后,后续容易忘记导致被覆盖掉。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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;
    }
}

此时需要将resultType修改为:com.zwwhnly.mybatisaction.model.SysRoleExtend。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

此时xml中的查询语句如下。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

<select id="selectRolesByUserId" resultType="com.zwwhnly.mybatisaction.model.SysRoleExtend">
    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}
</select>

在SysUserMapperTest中添加测试代码如下。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

@Test
public void testSelectRolesByUserId() {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        List<SysRole> sysRoleList = sysUserMapper.selectRolesByUserId(1L);

        Assert.assertNotNull(sysRoleList);
        Assert.assertTrue(sysRoleList.size() > 0);
    } finally {
        sqlSession.close();
    }
}

运行该测试方法,输入日志如下。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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 = ?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

DEBUG [main] - ==> Parameters: 1(Long)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

TRACE [main] - <== Columns: id, roleName, enabled, createBy, createTime, sysUser.userName, sysUser.userEmail文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

TRACE [main] - <== Row: 1, 管理员, 1, 1, 2019-06-27 18:21:12.0, admin, admin@mybatis.tk文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

TRACE [main] - <== Row: 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0, admin, admin@mybatis.tk文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

DEBUG [main] - <== Total: 2文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

2. 多个接口参数的用法

2.1 参数类型是基本类型

截止目前,我们定义的方法都只有1个参数,要么是只有1个基本类型的参数,比如selectById(Long id);。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

要么是只有1个对象作为参数,即将多个参数合并成了1个对象。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

但有些场景下,比如只有2个参数,没有必要为这2个参数再新建一个对象,比如我们现在需要根据用户的id和角色的状态来获取用户的所有角色,那么该如何使用呢?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

首先,在接口SysUserMapper中添加如下方法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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

然后,打开对应的SysUserMapper.xml文件,添加如下代码。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

<select id="selectRolesByUserIdAndRoleEnabled" resultType="com.zwwhnly.mybatisaction.model.SysRole">
    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}
</select>

在SysUserMapperTest测试类中,添加如下测试方法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

@Test
public void testselectRolesByUserIdAndRoleEnabled() {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
        List<SysRole> sysRoleList = sysUserMapper.selectRolesByUserIdAndRoleEnabled(1L, 1);

        Assert.assertNotNull(sysRoleList);
        Assert.assertTrue(sysRoleList.size() > 0);
    } finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}

运行该测试方法,发现报如下错误。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

MyBatis教程:XML方式的基本用法之多表查询文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

报错信息中说未找到参数userId,可用的参数是[0,1,param1,param2],也就是说我们将代码修改为:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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

或者修改为:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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

这么使用是可以测试通过的,不过这样使用,代码阅读起来不够友好,因此并不推荐这么使用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

推荐在接口方法的参数前添加@Param注解,如下所示:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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

运行刚刚添加的测试方法,测试通过,输出日志如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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 = ? AND r.enabled = ?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

DEBUG [main] - ==> Parameters: 1(Long), 1(Integer)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

TRACE [main] - <== Columns: id, roleName, enabled, createBy, createTime文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

TRACE [main] - <== Row: 1, 管理员, 1, 1, 2019-06-27 18:21:12.0文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

TRACE [main] - <== Row: 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

DEBUG [main] - <== Total: 2文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

2.2 参数类型是对象

为了演示参数类型是对象的使用方法,我们在接口SysUserMapper中添加如下方法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

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

此时对应的xml中的语句为:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html

<select id="selectRolesByUserAndRole" resultType="com.zwwhnly.mybatisaction.model.SysRole">
    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}
</select>
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14072.html
  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/bc/14072.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定