mybatis返回嵌套list结果(使用collection标签)

1.前言

举个栗子：
表a是书籍信息表（书籍id,书籍名称,书籍作者,出版日期,出版社）
表b是借阅流水表（流水id,借阅者id,借阅者姓名,书籍id）
需求：
获取表a每本书的借阅者姓名
如：

[
  {//书籍1111被 张三、李四借阅过
    "bookId": "11111",
    "borrowList": [
      {
        "readerName": "张三"
      },
      {
        "readerName": "李四"
      }
    ]
  },
  {//书籍2222没有人借阅过
    "bookId": "22222",
    "borrowList": []
  }
]

2.mybatis创建resultMap

直接上代码： resultMap id=“CollResultMap”
在外面的是书籍表a 的对象
在里面的是书籍表b 的对象

<resultMap id="CollResultMap" type="com.example.demo.executer.bookInfo.model.BookInfoModel">
        <result column="id" property="id"/>
        <result column="book_name" property="bookName"/>
        <result column="book_price" property="bookPrice"/>
        <result column="book_auther" property="bookAuther"/>
        <result column="book_type" property="bookType"/>
        <result column="rare_flag" property="rareFlag"/>
        <result column="borrow_times" property="borrowTimes"/>
        <result column="borrow_flag" property="borrowFlag"/>
        <result column="lose_flag" property="loseFlag"/>
        <result column="press" property="press"/>
        <result column="press_date" property="pressDate"/>
        <result column="remark" property="remark"/>
        <collection property="borrowList" javaType="List" ofType="com.example.demo.executer.borrowBoook.model.BorrowBookModel" >
            <result column="book_id" property="bookId"/>
            <result column="card_id" property="cardId"/>
            <result column="reader_id" property="readerId"/>
            <result column="reader_name" property="readerName"/>
            <result column="borrow_date" property="borrowDate"/>
            <result column="borrow_time" property="borrowTime"/>
            <result column="back_date" property="backDate"/>
            <result column="back_time" property="backTime"/>
            <result column="borrow_amt" property="borrowAmt"/>
        </collection>
    </resultMap>

3.联合查询sql

返回对象：resultMap=“CollResultMap”
联表查询两张表，查询

<select id="selectBookInfoAndBorrowInfo"  parameterType="com.example.demo.executer.bookInfo.model.BookInfoModel" resultMap="CollResultMap">
        select
        a.id,
        a.book_name,
        a.book_price,
        a.book_auther,
        a.book_type,
        a.rare_flag,
        a.borrow_times,
        a.borrow_flag,
        a.lose_flag,
        a.press,
        a.press_date,
        a.remark,
        b.book_id,
        b.card_id,
        b.reader_id,
        b.reader_name,
        b.borrow_date,
        b.borrow_time,
        b.back_date,
        b.back_time,
        b.borrow_amt
        from t_book_info a
        LEFT JOIN t_borrow_book b
        on a.id = b.book_id
        <where>
            <if test="id != null ">
                and a.id = #{id}
            </if>
        </where>
        order by a.create_time desc
    </select>

4.在Dao层调用mybatis

@Mapper
@Repository
public interface BookInfoDao extends BaseDao<BookInfoModel> {
    List<BookInfoModel> selectBookInfoAndBorrowInfo(BookInfoModel bookInfoModel);
}

5.navicat执行sql结果

注意： 有两条id相同的数据，也就是意味着这本书被两个人借阅过。

6.postman执行结果

mybatis自动将4条数据 糅合成3条数据：
心灵鸡汤：0人
java从入门到精通：2人
明朝那些事儿：1人

{
    "code": "000000",
    "msg": "交易成功",
    "data": [
        {
            "id": "202010211444031000042",
            "bookName": "心灵鸡汤",
            "bookPrice": 66.50,
            "bookAuther": "汤姆.森",
            "bookType": "3",
            "rareFlag": "unra",
            "borrowTimes": 0,
            "borrowFlag": "in",
            "loseFlag": "0",
            "press": "啊啊啊啊啊",
            "borrowList": []
        },
        {
            "id": "202010211444021000040",
            "bookName": "java从入门到精通",
            "bookPrice": 55.50,
            "bookAuther": "Jack Tomas",
            "bookType": "6",
            "rareFlag": "unra",
            "borrowTimes": 1,
            "borrowFlag": "in",
            "loseFlag": "0",
            "press": "四川工业大学出版社",
            "pressDate": 1601424000000,
            "borrowList": [
                {
                    "bookId": "202010211444021000040",
                    "cardId": "202010211554211000045",
                    "readerId": "202010231007091000060",
                    "readerName": "猪1",
                    "borrowDate": 1601164800000,
                    "borrowTime": 1603784092000,
                    "backDate": 1603843200000,
                    "backTime": 1603876404000,
                    "borrowAmt": 0.00
                },
                {
                    "bookId": "202010211444021000040",
                    "cardId": "202010211554211000046",
                    "readerId": "202010231007091000061",
                    "readerName": "猪2",
                    "borrowDate": 1601164800000,
                    "borrowTime": 1603784092000,
                    "backDate": 1603929600000,
                    "backTime": 1603962804000,
                    "borrowAmt": 0.00
                }
            ]
        },
        {
            "id": "202010211444031000041",
            "bookName": "明朝那些事儿",
            "bookPrice": 48.50,
            "bookAuther": "王朝青",
            "bookType": "2",
            "rareFlag": "unra",
            "borrowTimes": 1,
            "borrowFlag": "out",
            "loseFlag": "0",
            "press": "绍兴三十二年（1162年）",
            "pressDate": 1272326400000,
            "borrowList": [
                {
                    "bookId": "202010211444031000041",
                    "cardId": "202010211554211000046",
                    "readerId": "202010231007091000061",
                    "readerName": "猪2",
                    "borrowDate": 1590537600000,
                    "borrowTime": 1603784092000
                }
            ]
        }
    ]
}