系列八、Mybatis一对多查询，只查询出了一条记录

YYAugenstern

已于 2024-01-08 21:57:17 修改

阅读量1.9k

点赞数 8

分类专栏： MyBatis系列文章标签： MyBatis

于 2023-11-12 18:57:20 首次发布

本文链接：https://blog.csdn.net/HelloWorld20161112/article/details/134363798

版权

MyBatis系列专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一、Mybatis一对多查询，只查询出了一条记录

1.1、问题说明

典型的权限管理框架的数据库表中，一般会存在这样3种角色的表，即用户表、角色表、用户角色关联表，表设计好之后，往这三张表中初始化了一些测试数据，现在有一个需求通过用户名查询用户的id、username、name、nameZh，在Navicat等工具中查询能够正常查询出来，但是使用mapper去查却只返回一条数据。

二、sql初始化

drop database if exists 20231101_mybatis;
create database 20231101_mybatis;
use 20231101_mybatis;

DROP TABLE IF EXISTS `mybatis_user`;
CREATE TABLE `mybatis_user`  (
                                 `id` int NOT NULL AUTO_INCREMENT COMMENT '用户编号',
                                 `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名',
                                 `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '密码',
                                 `account_non_expired` int NOT NULL DEFAULT 1 COMMENT '账户是否没有过期（1：没有过期、0：已过期）',
                                 `account_non_locked` int NOT NULL DEFAULT 1 COMMENT '账户是否没有被锁定（1：没有锁定、0：已锁定）',
                                 `credentials_non_expired` int NOT NULL DEFAULT 1 COMMENT '密码是否没有过期（1：没有过期、0：已过期）',
                                 `enabled` int NOT NULL DEFAULT 1 COMMENT '账户是否可用（1：可用、0：不可用）',
                                 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = DYNAMIC;

INSERT INTO `mybatis_user` VALUES (1, 'root', 'root123',1,1,1,1);
INSERT INTO `mybatis_user` VALUES (2, 'admin', 'admin123',1,1,1,1);
INSERT INTO `mybatis_user` VALUES (3, 'user', 'user123',1,1,1,1);

DROP TABLE IF EXISTS `mybatis_role`;
CREATE TABLE `mybatis_role`  (
                                 `id` int NOT NULL AUTO_INCREMENT COMMENT '角色编号',
                                 `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色名称（英文）',
                                 `name_zh` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色名称（中文）',
                                 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '角色表' ROW_FORMAT = DYNAMIC;

INSERT INTO `mybatis_role` VALUES (1, 'dba', '数据库管理员');
INSERT INTO `mybatis_role` VALUES (2, 'admin', '超级管理员');
INSERT INTO `mybatis_role` VALUES (3, 'user', '用户');

DROP TABLE IF EXISTS `mybatis_user_role`;
CREATE TABLE `mybatis_user_role`  (
                                      `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
                                      `u_id` int NULL DEFAULT NULL COMMENT '用户ID',
                                      `r_id` int NULL DEFAULT NULL COMMENT '角色ID',
                                      PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户角色关联表' ROW_FORMAT = DYNAMIC;

INSERT INTO `mybatis_user_role` VALUES (1, 1, 1);
INSERT INTO `mybatis_user_role` VALUES (2, 1, 2);
INSERT INTO `mybatis_user_role` VALUES (3, 2, 2);
INSERT INTO `mybatis_user_role` VALUES (4, 3, 3);

三、表结构

3.1、mybatis_user

3.2、mybatis_role

3.3、mybatis_user_role

四、代码

4.1、pom

<dependencies>
	<!-- spring-boot -->
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-devtools</artifactId>
		<scope>runtime</scope>
		<optional>true</optional>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-test</artifactId>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-security</artifactId>
	</dependency>

	<!-- 数据源 -->
	<dependency>
		<groupId>com.baomidou</groupId>
		<artifactId>mybatis-plus-boot-starter</artifactId>
		<version>3.4.1</version>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.26</version>
	</dependency>
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid-spring-boot-starter</artifactId>
		<version>1.1.21</version>
	</dependency>

	<!-- 工具 -->
	<dependency>
		<groupId>org.projectlombok</groupId>
		<artifactId>lombok</artifactId>
		<optional>true</optional>
	</dependency>
	<dependency>
		<groupId>junit</groupId>
		<artifactId>junit</artifactId>
		<version>4.13.2</version>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>fastjson</artifactId>
		<version>1.2.76</version>
	</dependency>
	<dependency>
		<groupId>org.apache.commons</groupId>
		<artifactId>commons-collections4</artifactId>
		<version>4.3</version>
	</dependency>
	<dependency>
		<groupId>org.apache.commons</groupId>
		<artifactId>commons-lang3</artifactId>
		<version>3.11</version>
	</dependency>
	<dependency>
		<groupId>cn.hutool</groupId>
		<artifactId>hutool-all</artifactId>
		<version>5.7.22</version>
	</dependency>
	<dependency>
		<groupId>com.fasterxml.jackson.core</groupId>
		<artifactId>jackson-databind</artifactId>
		<version>2.12.1</version>
	</dependency>

</dependencies>

4.2、UserDO

@TableName("mybatis_user")
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
@ToString
public class UserDO implements UserDetails {
    /**
     * 主键
     */
    private Long id;

    /**
     * 用户名
     */
    private String username;

    /**
     * 密码
     */
    private String password;

    /**
     * 账户是否没有过期
     */
    private Boolean accountNonExpired;

    /**
     * 账户是否没有被锁定
     */
    private Boolean accountNonLocked;

    /**
     * 密码是否没有过期
     */
    private Boolean credentialsNonExpired;

    /**
     * 账户是否可用
     */
    private Boolean enabled;

    /**
     * 角色
     */
    @TableField(exist = false)
    private List<RoleDO> roles;

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {

        List<SimpleGrantedAuthority> authorities = new ArrayList<>();
        for (RoleDO role : getRoles()) {
            authorities.add(new SimpleGrantedAuthority(role.getName()));
        }
        return authorities;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }
    @Override
    public String getUsername() {
        return username;
    }

    public void setPassword(String password) {
        this.password = password;
    }
    @Override
    public String getPassword() {
        return password;
    }

    public void setAccountNonExpired(Boolean accountNonExpired) {
        this.accountNonExpired = accountNonExpired;
    }
    @Override
    public boolean isAccountNonExpired() {
        return accountNonExpired;
    }

    public void setAccountNonLocked(Boolean accountNonLocked) {
        this.accountNonLocked = accountNonLocked;
    }
    @Override
    public boolean isAccountNonLocked() {
        return accountNonLocked;
    }

    public void setCredentialsNonExpired(Boolean credentialsNonExpired) {
        this.credentialsNonExpired = credentialsNonExpired;
    }
    @Override
    public boolean isCredentialsNonExpired() {
        return credentialsNonExpired;
    }

    public void setEnabled(Boolean enabled) {
        this.enabled = enabled;
    }
    @Override
    public boolean isEnabled() {
        return enabled;
    }

    public List<RoleDO> getRoles() {
        return roles;
    }

    public void setRoles(List<RoleDO> roles) {
        this.roles = roles;
    }
}

4.3、RoleDO

@TableName("mybatis_role")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class RoleDO implements Serializable {
    /**
     * 角色id
     */
    @TableField(value = "rId")
    private Long id;

    /**
     * 角色名称（英文名称）
     */
    private String name;

    /**
     * 角色名称（中文名称）
     */
    private String nameZh;

}

4.3、UserRoleDO

@TableName("mybatis_user_role")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class UserRoleDO implements Serializable {

    /**
     * 主键
     */
    private Long id;

    /**
     * 用户id
     */
    private Long uId;

    /**
     * 角色id
     */
    private Long rId;

}

4.4、UserMapper

public interface UserMapper extends BaseMapper<UserDO> {

    /**
     * 根据用户名查询用户
     * @param username
     * @return
     */
    UserDO loadUserByUsername(String username);
}

4.5、UserMapper.xml

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.star.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="userDO">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="accountNonExpired" column="account_non_expired"/>
        <result property="accountNonLocked" column="account_non_locked"/>
        <result property="credentialsNonExpired" column="credentials_non_expired"/>
        <result property="enabled" column="enabled"/>
        <collection property="roles" ofType="roleDO">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="nameZh" column="name_zh"/>
        </collection>
    </resultMap>

    <select id="loadUserByUsername" resultMap="BaseResultMap">
        select mu.id, mu.username, mr.`name`, mr.name_zh,mr.id
        from mybatis_user mu
                 left join mybatis_user_role mur on mu.id = mur.u_id
                 left join mybatis_role mr on mr.id = mur.r_id
        where mu.username = #{username}
    </select>

</mapper>

五、测试

5.1、Navicat测试（正常）

SELECT
	mu.id,
	mu.username,
	mr.`name`,
	mr.name_zh 
FROM
	mybatis_user mu
	LEFT JOIN mybatis_user_role mur ON mu.id = mur.u_id
	LEFT JOIN mybatis_role mr ON mr.id = mur.r_id 
WHERE
	mu.username = 'root'

5.2、代码测试（异常）

同样的查询条件，Navicat中查询正常，代码中查询不正常，原因是什么呢？多条的数据只显示一条，是因为字段名相同，两张表的主键都是id，就只会显示一条数据，只需要把多条数据的表id的column修改成别名就可以了。如下所示：

5.3、问题解决

5.3.1、修改UserMapper.xml的映射信息和查询信息

5.3.2、再次查询（正常）

六、参考

https://www.cnblogs.com/damugua/p/17046724.html

YYAugenstern

关注

8
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
系列八、Mybatis一对多查询，只查询出了一条记录

典型的权限管理框架的数据库表中，一般会存在这样3种角色的表，即用户表、角色表、用户角色关联表，表设计好之后，往这三张表中初始化了一些测试数据，现在有一个需求通过用户名查询用户的id、username、name、nameZh，在Navicat等工具中查询能够正常查询出来，但是使用mapper去查却只返回一条数据。
复制链接

扫一扫

专栏目录