mybatis级联查询 一对多(获取班级圈的同时获取评论列表和点赞列表)

第一种查询效果(第一种查询多次查询数据库, 如果可能尽量使用第二种)

{
    "success": true,
    "status": "200",
    "msg": "获取班级圈列表成功",
    "data": {
        "pageSize": 4,
        "firstIndex": 0,
        "lastIndex": 1,
        "items": [
            {
                "id": "2",
                "openId": "123456789",
                "classNumber": 1201801,
                "orgId": "11111111",
                "content": "今天开学了",
                "picture": "2.jpg",
                "userType": "0",
                "userId": "39914d265901458aa06aba9d5b0c61ba",
                "sort": 0,
                "del": "0",
                "createdatetime": "2019-02-26 09:49:49",
                "modifydatetime": "2019-02-26 09:49:52",
                "userName": "张瑛3",
                "commentList": [],
                "praiseList": []
            },
            {
                "id": "1",
                "openId": "123456789",
                "classNumber": 1201801,
                "orgId": "11111111",
                "content": "今天天气真好",
                "picture": "1.jpg",
                "userType": "0",
                "userId": "39914d265901458aa06aba9d5b0c61ba",
                "sort": 0,
                "del": "0",
                "createdatetime": "2019-02-26 09:49:13",
                "modifydatetime": "2019-02-26 09:49:16",
                "userName": "张瑛3",
                "commentList": [
                    {
                        "id": "1",
                        "classCircleId": "1",
                        "openId": "1",
                        "userId": "39914d265901458aa06aba9d5b0c61ba",
                        "content": "看上去真不错",
                        "sort": 0,
                        "del": 0,
                        "createdatetime": "2019-02-26 15:33:47",
                        "modifydatetime": "2019-02-26 15:33:48",
                        "userName": "张瑛3"
                    },
                    {
                        "id": "2",
                        "classCircleId": "1",
                        "openId": "1",
                        "userId": "39914d265901458aa06aba9d5b0c61ba",
                        "content": "还行吧,也就那么回事",
                        "sort": 0,
                        "del": 0,
                        "createdatetime": "2019-02-27 10:24:29",
                        "modifydatetime": "2019-02-27 10:24:32",
                        "userName": "张瑛3"
                    }
                ],
                "praiseList": [
                    {
                        "id": "1",
                        "classCircleId": "1",
                        "userIds": "1,2,3,",
                        "counts": 3
                    }
                ]
            }
        ],
        "totalItems": 2,
        "totalPages": 1,
        "currentPage": 1,
        "pageItemsCount": 2,
        "lastPage": true,
        "firstPage": true
    }
}

mapper.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.welsee.mapper.ClassCircleMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.welsee.entity.ClassCircle">
        <id column="id" property="id"/>
        <result column="open_id" property="openId"/>
        <result column="class_number" property="classNumber"/>
        <result column="org_id" property="orgId"/>
        <result column="content" property="content"/>
        <result column="picture" property="picture"/>
        <result column="user_type" property="userType"/>
        <result column="user_id" property="userId"/>
        <result column="sort" property="sort"/>
        <result column="del" property="del"/>
        <result column="createdatetime" property="createdatetime"/>
        <result column="modifydatetime" property="modifydatetime"/>
    </resultMap>
    <resultMap id="extClassCircleMap" type="com.welsee.extentity.ExtClassCircle" extends="BaseResultMap">
        <result column="user_name" property="userName"/>
        <collection property="commentList" column="comment_list" ofType="com.welsee.entity.ClassCircleComment"
                    select="findClassCircleCommentList"/>
        <collection property="praiseList" column="praise_list" ofType="com.welsee.entity.ClassCirclePraise"
                    select="findClassCirclePraise"/>
    </resultMap>

    <select id="findClassCircleCommentList" resultType="com.welsee.extentity.ExtClassCircleComment">
        SELECT ccc.* ,p.realname user_name
        FROM class_circle_comment ccc
        LEFT JOIN person p ON ccc.user_id = p.id AND p.del = 0
        WHERE ccc.class_circle_id = #{arg0}
    </select>
    
    <select id="findClassCirclePraise" resultType="com.welsee.entity.ClassCirclePraise">
        SELECT * FROM class_circle_praise WHERE class_circle_id = #{arg0}
    </select>
    
    <select id="getClassCircleListInfo" resultMap="extClassCircleMap">
        SELECT c.*,p.realname user_name,c.id comment_list,c.id praise_list
        FROM class_circle c
        LEFT JOIN  person p ON c.user_id = p.id AND p.del = 0
        WHERE c.class_number = #{classNumber} AND c.org_id = #{orgId} AND c.del = 0 ORDER BY c.sort DESC,c.createdatetime DESC
    </select>
</mapper>

第二种查询效果

{
  "id": "1",
  "pname": "Bootstrap开发框架",
  "parentid": null,
  "grade": null,
  "ptype": null,
  "description": null,
  "courseid": null,
  "status": null,
  "orderby": null,
  "timelength": null,
  "trylearn": null,
  "children": [
    {
      "id": "2",
      "pname": "计算机原理",
      "parentid": null,
      "grade": null,
      "ptype": null,
      "description": null,
      "courseid": null,
      "status": null,
      "orderby": null,
      "timelength": null,
      "trylearn": null,
      "children": [
        {
          "id": "3",
          "pname": "计算机硬件",
          "parentid": null,
          "grade": null,
          "ptype": null,
          "description": null,
          "courseid": null,
          "status": null,
          "orderby": null,
          "timelength": null,
          "trylearn": null,
          "children": null
        },
        {
          "id": "4",
          "pname": "计算机软件",
          "parentid": null,
          "grade": null,
          "ptype": null,
          "description": null,
          "courseid": null,
          "status": null,
          "orderby": null,
          "timelength": null,
          "trylearn": null,
          "children": null
        }
      ]
    },
    {
      "id": "5",
      "pname": "计算机编程入门",
      "parentid": null,
      "grade": null,
      "ptype": null,
      "description": null,
      "courseid": null,
      "status": null,
      "orderby": null,
      "timelength": null,
      "trylearn": null,
      "children": [
        {
          "id": "6",
          "pname": "java语法介绍",
          "parentid": null,
          "grade": null,
          "ptype": null,
          "description": null,
          "courseid": null,
          "status": null,
          "orderby": null,
          "timelength": null,
          "trylearn": null,
          "children": null
        },
        {
          "id": "7",
          "pname": "Hello World",
          "parentid": null,
          "grade": null,
          "ptype": null,
          "description": null,
          "courseid": null,
          "status": null,
          "orderby": null,
          "timelength": null,
          "trylearn": null,
          "children": null
        }
      ]
    }
  ]
}

mapper编写

<?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.xuecheng.manage_course.dao.TeachplanMapper">

    <resultMap id="teachplanMap" type="com.xuecheng.framework.domain.course.ext.TeachplanNode">
        <id column="one_id" property="id"></id>
        <result column="one_pname" property="pname"></result>
        <collection property="children" ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode">
            <id column="two_id" property="id"></id>
            <result column="two_pname" property="pname"></result>
            <collection property="children" ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode">
                <id column="three_id" property="id"></id>
                <result column="three_pname" property="pname"></result>
            </collection>
        </collection>

    </resultMap>

    <select id="selectList" parameterType="java.lang.String" resultMap="teachplanMap">
        SELECT
        a.id one_id,
        a.pname one_pname,
        b.id two_id,
        b.pname two_pname,
        c.id three_id,
        c.pname three_pname
        FROM
        teachplan a
        LEFT JOIN teachplan b ON b.parentid = a.id
        LEFT JOIN teachplan c ON c.parentid = b.id
        WHERE
        a.parentid = '0'
        <if test="_parameter!=null and _parameter != ''">
            AND a.courseid = #{courseId}
        </if>
        ORDER BY
        a.orderby,
        b.orderby,
        c.orderby
    </select>
</mapper>

第二种适用场景如下