Mybatis 返回对象中包含多个 List＜String＞属性

数据库数据

mysql> select * from ai_user;
+----+-----------+--------+
| id | user_name | status |
+----+-----------+--------+
|  1 | Answer    |      1 |
|  2 | Iris      |      1 |
+----+-----------+--------+

mysql> select * from ai_name;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | Answer    |
|       1 | AI        |
|       1 | AAL       |
|       2 | Iris      |
|       2 | Ellis     |
|       2 | Monta     |
+---------+-----------+

mysql> select * from ai_role;
+---------+-----------------+
| user_id | role_name       |
+---------+-----------------+
|       1 | Admin           |
|       1 | Manager         |
|       1 | Coder           |
|       2 | CustomerService |
+---------+-----------------+

实体类

@Data
public class User {
    private Long id;
    private List<String> names;
    private List<String> roles;
}

Mapper 层

public interface UserMapper {
    List<User> queryUsers();
}

Mapper Sql 映射文件

<resultMap id="UserMap" type="User">
   <result column="id" property="id" jdbcType="BIGINT" />
    <collection property="names" resultMap="NamesMap" />
    <collection property="roles" resultMap="RolesMap" />
</resultMap>

<resultMap id="NamesMap" type="string">
    <result column="user_name" />
</resultMap>

<resultMap id="RolesMap" type="string">
    <result column="role_name" />
</resultMap>

<select id="queryUsers" resultMap="UserMap">
   SELECT au.id, an.user_name, ar.role_name
    FROM ai_user au
    LEFT JOIN ai_name an ON an.user_id = au.id
    LEFT JOIN ai_role ar ON ar.user_id = au.id
</select>

结果输出示例

{"id":1,"names":["Answer","AI","AAL"],"roles":["Admin","Manager","Coder"]}
{"id":2,"names":["Iris","Ellis","Monta"],"roles":["CustomerService"]}

数据库脚本

DROP TABLE IF EXISTS `ai_name`;
CREATE TABLE `ai_name` (
  `user_id` bigint(18) NOT NULL,
  `user_name` varchar(55) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of ai_name
-- ----------------------------
INSERT INTO `ai_name` VALUES ('1', 'Answer');
INSERT INTO `ai_name` VALUES ('1', 'AI');
INSERT INTO `ai_name` VALUES ('1', 'AAL');
INSERT INTO `ai_name` VALUES ('2', 'Iris');
INSERT INTO `ai_name` VALUES ('2', 'Ellis');
INSERT INTO `ai_name` VALUES ('2', 'Monta');

-- ----------------------------
-- Table structure for `ai_role`
-- ----------------------------
DROP TABLE IF EXISTS `ai_role`;
CREATE TABLE `ai_role` (
  `user_id` bigint(18) NOT NULL,
  `role_name` varchar(55) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of ai_role
-- ----------------------------
INSERT INTO `ai_role` VALUES ('1', 'Admin');
INSERT INTO `ai_role` VALUES ('1', 'Manager');
INSERT INTO `ai_role` VALUES ('1', 'Coder');
INSERT INTO `ai_role` VALUES ('2', 'CustomerService');

-- ----------------------------
-- Table structure for `ai_user`
-- ----------------------------
DROP TABLE IF EXISTS `ai_user`;
CREATE TABLE `ai_user` (
  `id` bigint(18) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(55) NOT NULL,
  `status` tinyint(5) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of ai_user
-- ----------------------------
INSERT INTO `ai_user` VALUES ('1', 'Answer', '1');
INSERT INTO `ai_user` VALUES ('2', 'Iris', '1');