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

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

    1.1、问题说明

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

    二、sql初始化

    1. drop database if exists 20231101_mybatis;
    2. create database 20231101_mybatis;
    3. use 20231101_mybatis;
    4.  
    5. DROP TABLE IF EXISTS `mybatis_user`;
    6. CREATE TABLE `mybatis_user`  (
    7.                                  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户编号',
    8.                                  `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名',
    9.                                  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '密码',
    10.                                  `account_non_expired` int NOT NULL DEFAULT 1 COMMENT '账户是否没有过期（1：没有过期、0：已过期）',
    11.                                  `account_non_locked` int NOT NULL DEFAULT 1 COMMENT '账户是否没有被锁定（1：没有锁定、0：已锁定）',
    12.                                  `credentials_non_expired` int NOT NULL DEFAULT 1 COMMENT '密码是否没有过期（1：没有过期、0：已过期）',
    13.                                  `enabled` int NOT NULL DEFAULT 1 COMMENT '账户是否可用（1：可用、0：不可用）',
    14.                                  PRIMARY KEY (`id`) USING BTREE
    15. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = DYNAMIC;
    16.  
    17. INSERT INTO `mybatis_user` VALUES (1, 'root', 'root123',1,1,1,1);
    18. INSERT INTO `mybatis_user` VALUES (2, 'admin', 'admin123',1,1,1,1);
    19. INSERT INTO `mybatis_user` VALUES (3, 'user', 'user123',1,1,1,1);
    20.  
    21. DROP TABLE IF EXISTS `mybatis_role`;
    22. CREATE TABLE `mybatis_role`  (
    23.                                  `id` int NOT NULL AUTO_INCREMENT COMMENT '角色编号',
    24.                                  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色名称（英文）',
    25.                                  `name_zh` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色名称（中文）',
    26.                                  PRIMARY KEY (`id`) USING BTREE
    27. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '角色表' ROW_FORMAT = DYNAMIC;
    28.  
    29. INSERT INTO `mybatis_role` VALUES (1, 'dba', '数据库管理员');
    30. INSERT INTO `mybatis_role` VALUES (2, 'admin', '超级管理员');
    31. INSERT INTO `mybatis_role` VALUES (3, 'user', '用户');
    32.  
    33. DROP TABLE IF EXISTS `mybatis_user_role`;
    34. CREATE TABLE `mybatis_user_role`  (
    35.                                       `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
    36.                                       `u_id` int NULL DEFAULT NULL COMMENT '用户ID',
    37.                                       `r_id` int NULL DEFAULT NULL COMMENT '角色ID',
    38.                                       PRIMARY KEY (`id`) USING BTREE
    39. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户角色关联表' ROW_FORMAT = DYNAMIC;
    40.  
    41. INSERT INTO `mybatis_user_role` VALUES (1, 1, 1);
    42. INSERT INTO `mybatis_user_role` VALUES (2, 1, 2);
    43. INSERT INTO `mybatis_user_role` VALUES (3, 2, 2);
    44. 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

    1. <dependencies>
    2. 	
    3. 	<dependency>
    4. 		<groupId>org.springframework.bootgroupId>
    5. 		<artifactId>spring-boot-starter-webartifactId>
    6. 	dependency>
    7. 	<dependency>
    8. 		<groupId>org.springframework.bootgroupId>
    9. 		<artifactId>spring-boot-devtoolsartifactId>
    10. 		<scope>runtimescope>
    11. 		<optional>trueoptional>
    12. 	dependency>
    13. 	<dependency>
    14. 		<groupId>org.springframework.bootgroupId>
    15. 		<artifactId>spring-boot-starter-testartifactId>
    16. 		<scope>testscope>
    17. 	dependency>
    18. 	<dependency>
    19. 		<groupId>org.springframework.bootgroupId>
    20. 		<artifactId>spring-boot-starter-securityartifactId>
    21. 	dependency>
    22.  
    23. 	
    24. 	<dependency>
    25. 		<groupId>com.baomidougroupId>
    26. 		<artifactId>mybatis-plus-boot-starterartifactId>
    27. 		<version>3.4.1version>
    28. 	dependency>
    29. 	<dependency>
    30. 		<groupId>mysqlgroupId>
    31. 		<artifactId>mysql-connector-javaartifactId>
    32. 		<version>8.0.26version>
    33. 	dependency>
    34. 	<dependency>
    35. 		<groupId>com.alibabagroupId>
    36. 		<artifactId>druid-spring-boot-starterartifactId>
    37. 		<version>1.1.21version>
    38. 	dependency>
    39.  
    40. 	
    41. 	<dependency>
    42. 		<groupId>org.projectlombokgroupId>
    43. 		<artifactId>lombokartifactId>
    44. 		<optional>trueoptional>
    45. 	dependency>
    46. 	<dependency>
    47. 		<groupId>junitgroupId>
    48. 		<artifactId>junitartifactId>
    49. 		<version>4.13.2version>
    50. 		<scope>testscope>
    51. 	dependency>
    52. 	<dependency>
    53. 		<groupId>com.alibabagroupId>
    54. 		<artifactId>fastjsonartifactId>
    55. 		<version>1.2.76version>
    56. 	dependency>
    57. 	<dependency>
    58. 		<groupId>org.apache.commonsgroupId>
    59. 		<artifactId>commons-collections4artifactId>
    60. 		<version>4.3version>
    61. 	dependency>
    62. 	<dependency>
    63. 		<groupId>org.apache.commonsgroupId>
    64. 		<artifactId>commons-lang3artifactId>
    65. 		<version>3.11version>
    66. 	dependency>
    67. 	<dependency>
    68. 		<groupId>cn.hutoolgroupId>
    69. 		<artifactId>hutool-allartifactId>
    70. 		<version>5.7.22version>
    71. 	dependency>
    72. 	<dependency>
    73. 		<groupId>com.fasterxml.jackson.coregroupId>
    74. 		<artifactId>jackson-databindartifactId>
    75. 		<version>2.12.1version>
    76. 	dependency>
    77.  
    78. dependencies>

    4.2、UserDO

    1. @TableName("mybatis_user")
    2. @AllArgsConstructor
    3. @NoArgsConstructor
    4. @Accessors(chain = true)
    5. @ToString
    6. public class UserDO implements UserDetails {
    7.     /**
    8.      * 主键
    9.      */
    10.     private Long id;
    11.  
    12.     /**
    13.      * 用户名
    14.      */
    15.     private String username;
    16.  
    17.     /**
    18.      * 密码
    19.      */
    20.     private String password;
    21.  
    22.     /**
    23.      * 账户是否没有过期
    24.      */
    25.     private Boolean accountNonExpired;
    26.  
    27.     /**
    28.      * 账户是否没有被锁定
    29.      */
    30.     private Boolean accountNonLocked;
    31.  
    32.     /**
    33.      * 密码是否没有过期
    34.      */
    35.     private Boolean credentialsNonExpired;
    36.  
    37.     /**
    38.      * 账户是否可用
    39.      */
    40.     private Boolean enabled;
    41.  
    42.     /**
    43.      * 角色
    44.      */
    45.     @TableField(exist = false)
    46.     private List roles;
    47.  
    48.     @Override
    49.     public Collectionextends GrantedAuthority> getAuthorities() {
    50.  
    51.         List authorities = new ArrayList<>();
    52.         for (RoleDO role : getRoles()) {
    53.             authorities.add(new SimpleGrantedAuthority(role.getName()));
    54.         }
    55.         return authorities;
    56.     }
    57.  
    58.     public Long getId() {
    59.         return id;
    60.     }
    61.  
    62.     public void setId(Long id) {
    63.         this.id = id;
    64.     }
    65.  
    66.     public void setUsername(String username) {
    67.         this.username = username;
    68.     }
    69.     @Override
    70.     public String getUsername() {
    71.         return username;
    72.     }
    73.  
    74.     public void setPassword(String password) {
    75.         this.password = password;
    76.     }
    77.     @Override
    78.     public String getPassword() {
    79.         return password;
    80.     }
    81.  
    82.     public void setAccountNonExpired(Boolean accountNonExpired) {
    83.         this.accountNonExpired = accountNonExpired;
    84.     }
    85.     @Override
    86.     public boolean isAccountNonExpired() {
    87.         return accountNonExpired;
    88.     }
    89.  
    90.     public void setAccountNonLocked(Boolean accountNonLocked) {
    91.         this.accountNonLocked = accountNonLocked;
    92.     }
    93.     @Override
    94.     public boolean isAccountNonLocked() {
    95.         return accountNonLocked;
    96.     }
    97.  
    98.     public void setCredentialsNonExpired(Boolean credentialsNonExpired) {
    99.         this.credentialsNonExpired = credentialsNonExpired;
    100.     }
    101.     @Override
    102.     public boolean isCredentialsNonExpired() {
    103.         return credentialsNonExpired;
    104.     }
    105.  
    106.     public void setEnabled(Boolean enabled) {
    107.         this.enabled = enabled;
    108.     }
    109.     @Override
    110.     public boolean isEnabled() {
    111.         return enabled;
    112.     }
    113.  
    114.     public List getRoles() {
    115.         return roles;
    116.     }
    117.  
    118.     public void setRoles(List roles) {
    119.         this.roles = roles;
    120.     }
    121. }

    4.3、RoleDO

    1. @TableName("mybatis_role")
    2. @Data
    3. @AllArgsConstructor
    4. @NoArgsConstructor
    5. @Accessors(chain = true)
    6. public class RoleDO implements Serializable {
    7.     /**
    8.      * 角色id
    9.      */
    10.     @TableField(value = "rId")
    11.     private Long id;
    12.  
    13.     /**
    14.      * 角色名称（英文名称）
    15.      */
    16.     private String name;
    17.  
    18.     /**
    19.      * 角色名称（中文名称）
    20.      */
    21.     private String nameZh;
    22.  
    23. }

    4.3、UserRoleDO

    1. @TableName("mybatis_user_role")
    2. @Data
    3. @AllArgsConstructor
    4. @NoArgsConstructor
    5. @Accessors(chain = true)
    6. public class UserRoleDO implements Serializable {
    7.  
    8.     /**
    9.      * 主键
    10.      */
    11.     private Long id;
    12.  
    13.     /**
    14.      * 用户id
    15.      */
    16.     private Long uId;
    17.  
    18.     /**
    19.      * 角色id
    20.      */
    21.     private Long rId;
    22.  
    23. }

    4.4、UserMapper

    1. public interface UserMapper extends BaseMapper {
    2.  
    3.     /**
    4.      * 根据用户名查询用户
    5.      * @param username
    6.      * @return
    7.      */
    8.     UserDO loadUserByUsername(String username);
    9. }

    4.5、UserMapper.xml

    1. mapper
    2.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    3.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    4.  
    5. <mapper namespace="org.star.mapper.UserMapper">
    6.  
    7.     <resultMap id="BaseResultMap" type="userDO">
    8.         <id property="id" column="id"/>
    9.         <result property="username" column="username"/>
    10.         <result property="password" column="password"/>
    11.         <result property="accountNonExpired" column="account_non_expired"/>
    12.         <result property="accountNonLocked" column="account_non_locked"/>
    13.         <result property="credentialsNonExpired" column="credentials_non_expired"/>
    14.         <result property="enabled" column="enabled"/>
    15.         <collection property="roles" ofType="roleDO">
    16.             <id property="id" column="id"/>
    17.             <result property="name" column="name"/>
    18.             <result property="nameZh" column="name_zh"/>
    19.         collection>
    20.     resultMap>
    21.  
    22.     <select id="loadUserByUsername" resultMap="BaseResultMap">
    23.         select mu.id, mu.username, mr.`name`, mr.name_zh,mr.id
    24.         from mybatis_user mu
    25.                  left join mybatis_user_role mur on mu.id = mur.u_id
    26.                  left join mybatis_role mr on mr.id = mur.r_id
    27.         where mu.username = #{username}
    28.     select>
    29.  
    30. mapper>

    五、测试

    5.1、Navicat测试（正常）

    1. SELECT
    2. 	mu.id,
    3. 	mu.username,
    4. 	mr.`name`,
    5. 	mr.name_zh 
    6. FROM
    7. 	mybatis_user mu
    8. 	LEFT JOIN mybatis_user_role mur ON mu.id = mur.u_id
    9. 	LEFT JOIN mybatis_role mr ON mr.id = mur.r_id 
    10. WHERE
    11. 	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

  • 相关阅读:
    http和https的区别、什么是对称密钥，什么是非对称密钥
    数据填报系统究竟是买还是自研呢？_光点科技
    C#多线程（三）线程高级篇
    【ONE·C++ || 网络基础（二）】
    公司真实Java面试题泄露
    流程设计的基本步骤
    python数据分析及可视化（六）Pandas的对齐运算、混合运算、统计函数、排序函数、处理缺失值及数据的存储与读取
    Linux环境（Ubuntu）上的防火墙工具使用方法
    哈达玛矩阵与克罗内克积
    探索 SOCKS5 代理在跨境电商中的网络安全应用
  • 原文地址：https://blog.csdn.net/HelloWorld20161112/article/details/134363798