mybatis 一对多 查询 一下就懂，多种查询实现

前期准备

DROP TABLE IF EXISTS `room`;
CREATE TABLE `room`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of room
-- ----------------------------
INSERT INTO `room` VALUES (1, '主卧');
INSERT INTO `room` VALUES (2, '次卧');


DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `room_id` int NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 1, '张三', 7);
INSERT INTO `user` VALUES (2, 1, '李四', 8);
INSERT INTO `user` VALUES (3, 2, '王五', 55);

Room

user

join 连表

对象

@Data
public class Room {
    private Long id;
    private String name;
    private List<User> userList;

    private User user;
}

@Data
public class User {
    private Long id;
    private Long roomId;
    private String userName;
    private Integer age;
}

查询方法

1. 如果是 1对一 association 对象查询 for 每次查询一个

    <!-- 嵌套查询会在主查询中，去调用子查询-->
    <resultMap id="BaseResultMap" type="com.morttty.mybatis.entity.Room" >
        <result column="id" property="id" />
        <result column="name" property="name" />
        <association property="user" javaType="com.morttty.mybatis.entity.User" select="selectAllUserByRoomId" column="id"/>
    </resultMap>
    
	<select id="selectAllRoom1" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT * FROM `room` LIMIT 0,1000
    </select>

    <select id="selectAllUserByRoomId" resultType="com.morttty.mybatis.entity.User" >
        select id,user_name as userName, age from `user` where room_id =#{roomId} limit 0,1
    </select>

2. 如果是 一对多的话 collection

    <!-- 嵌套结果只需要查询一次，将结果进行封装  需要 join 连表 -->
    <resultMap id="BaseResultMap2" type="com.morttty.mybatis.entity.Room" >
        <result column="id" property="id" />
        <result column="name" property="name" />
        <collection property="userList" ofType="com.morttty.mybatis.entity.User">
            <id column="sid" property="id" />
            <result column="room_id" property="roomId" />
            <result column="user_name" property="userName" />
            <result column="age" property="age" />
        </collection>
    </resultMap>

    <select id="selectAllRoom" parameterType="java.lang.String" resultMap="BaseResultMap2">
        SELECT
            r.id,
            r.NAME,
            u.id AS sid,
            u.room_id,
            u.user_name,
            u.age
        FROM
            `room` AS r
                LEFT JOIN `user` AS u ON r.id = u.room_id
            LIMIT 0,1000

    </select>

3 . 先查 room List 然后 for 循环查 User

    <select id="selectAllRoom1" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT * FROM `room` LIMIT 0,1000
    </select>

    <select id="selectAllUserByRoomId" resultType="com.morttty.mybatis.entity.User" >
        select id,user_name as userName, age from `user` where room_id =#{roomId} limit 0,1
    </select>

    @GetMapping("/sql2")
    @ResponseBody
    List<Room> index2() {
        List<Room> rooms = roomMapper.selectAllRoom();
        rooms.forEach(e->e.setUserList(roomMapper.selectAllUserByRoomId(e.getId())));
        return  rooms;
    }

4 推荐查询方法 利用 where in +groupingBy 模式 组装 子list 对象

    @GetMapping("/sql3")
    @ResponseBody
    List<Room> index3() {
        List<Room> rooms = roomMapper.selectAllRoom();
        String roomIds = rooms.stream().map(e -> e.getId() + "").collect(Collectors.joining(","));

        List<User> users = roomMapper.selectAllUser(roomIds);
        Map<Long, List<User>> mapRoomIdUser = users.stream().collect(Collectors.groupingBy(User::getRoomId));

        rooms.forEach(e->e.setUserList(mapRoomIdUser.get(e.getId())));

        return  rooms;
    }
}

    <select id="selectAllUser" resultType="com.morttty.mybatis.entity.User" >
        select id,user_name as userName,room_id as roomId, age from `user`
        where room_id in (#{roomIds})
    </select>

接口返回

[
  {
    "id": 1,
    "name": "主卧",
    "userList": [
      {
        "id": 2,
        "roomId": 1,
        "userName": "李四",
        "age": 8
      },
      {
        "id": 1,
        "roomId": 1,
        "userName": "张三",
        "age": 7
      }
    ],
    "user": null
  },
  {
    "id": 2,
    "name": "次卧",
    "userList": [
      {
        "id": 3,
        "roomId": 2,
        "userName": "王五",
        "age": 55
      }
    ],
    "user": null
  }
]