Mybatis 傳回對象中包含 List集合

遇到這種情況的實體類，需要在mapper.xml檔案裡使用

resultMap标簽以及其他子标簽.      

先貼代碼：

實體類：

UserEntity類：      

private Long id;// 使用者id
private String username;// 使用者名
private String password;// 密碼
private List<Role> userRoles;// 使用者角色集合      

Role類：

public class Role {
    private Integer id;    //角色id
    private String roleName;   //角色名稱
}      

sql檔案：

t_user:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '使用者id',
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '使用者名',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '密碼',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, '123', 'admin');
INSERT INTO `t_user` VALUES (2, '456', 'admin');

SET FOREIGN_KEY_CHECKS = 1;
           

t_role:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_role
-- ----------------------------
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role`  (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '角色id',
  `role_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '角色名稱',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_role
-- ----------------------------
INSERT INTO `t_role` VALUES (1, 'ROLE_USER');
INSERT INTO `t_role` VALUES (2, 'ROLE_ADMIN');

SET FOREIGN_KEY_CHECKS = 1;
           

t_user_roles:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user_roles
-- ----------------------------
DROP TABLE IF EXISTS `t_user_roles`;
CREATE TABLE `t_user_roles`  (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '使用者角色對照ID',
  `user_id` int(8) NULL DEFAULT NULL COMMENT '使用者ID',
  `role_id` int(8) NULL DEFAULT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user_roles
-- ----------------------------
INSERT INTO `t_user_roles` VALUES (1, 1, 1);
INSERT INTO `t_user_roles` VALUES (2, 2, 2);

SET FOREIGN_KEY_CHECKS = 1;
           

application.yml :

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db03?characterEncoding=UTF-8&useSSL=false
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
logging:
  level:
    com.example.bdatabaserole.mapper: debug  # 列印sql語句

mybatis:
  mapper-locations: classpath:mapper/*.xml
           

Mapper 接口

@Mapper
public interface UserMapper {
    List<UserEntity> getUserByUsername(@Param("username") String username);
}
           

UserMapper .xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.test.mapper.UserMapper">
<!--                                  按住Ctrl+Alt+C可以擷取全限定類名     -->
    <resultMap id="userRoleMap" type="com.example.test.entity.UserEntity">
        <id property="uid" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <collection property="userRoles" ofType="com.example.test.entity.Role">
            <id property="id" column="id"/>
            <result property="roleName" column="role_name"></result>
        </collection>
    </resultMap>

    <select id="getUserByUsername" resultMap="userRoleMap" parameterType="java.lang.String">
        SELECT u.id, u.username, u.`password`, r.role_name,r.id
        FROM `t_role` r,
             t_user u,
             t_user_roles ur
        WHERE r.id = ur.role_id
          AND u.id = ur.user_id
          AND u.username = #{username}
    </select>
</mapper>
           

UserService 接口

public interface UserService {
    List<UserEntity> getUserByUsername(String username);
}
           

UserServiceImpl 實作類

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    UserMapper userMapper;

    @Override
    public List<UserEntity> getUserByUsername(String username) {
        return userMapper.getUserByUsername(username);
    }
}
           

UserController 類

      

@RestController
@RequestMapping("/User")
public class UserController {

    @Autowired
    UserService userService;

    @GetMapping("/getUserByUsername")
    List<UserEntity> getUserByUsername(String username){
        return userService.getUserByUsername(username);
    }
}
           

結果：
      

[
    {
        "uid": 1,
        "username": "123",
        "password": "admin",
        "userRoles": [
            {
                "id": 1,
                "roleName": "ROLE_USER"
            }
        ]
    }
]
           

telnet