springmvc学习指南 之---第32篇 mybatis 嵌套的处理

DTO的写法

package com.msb.dongbao.pms.model.dto;

import com.msb.dongbao.pms.model.entity.Product;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.util.Arrays;
import java.util.List;

/**
 * <p>
 * 首页推荐专题表 传输实体
 * </p>
 * @author 张艳涛 zyt
 * @version 1.0
 * @date 2021/11/9 17:57
 * @description
 */
@Data
@EqualsAndHashCode
@ApiModel(value="HomeRecommendSubject传输实体", description="首页推荐专题表传输实体")
public class HomeRecommendSubjectDTO implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long id;

    @ApiModelProperty(value = "专题ID")
    private Long subjectId;

    @ApiModelProperty(value = "专题名称")
    private String subjectName;

    @ApiModelProperty(value = "推荐状态")
    private Integer recommendStatus;

    @ApiModelProperty(value = "排序")
    private Integer sort;

    @ApiModelProperty(value = "商户ID")
    private Integer merchantId;

    @ApiModelProperty(value = "专题主图")
    private String pic;

    @ApiModelProperty(value = "画册图片用逗号分割")
    private String albumPics;

    @ApiModelProperty(value = "专题热词")
    private String hotWords;


    @ApiModelProperty(value = "专题商品列表，取前10个")
    private List<Product> productList;


    @ApiModelProperty(value = "热词列表，原字段为逗号分隔，取其中10个")
    private List<String> hotWordList;

    @ApiModelProperty(value = "专题图集，原字段为逗号分隔，最多5个")
    private List<String> albumPicList;


    public List<String> getHotWordList() {
        if(hotWords != null && !"".equals(hotWords)){
            return Arrays.asList(hotWords.split(","));
        }
        return null;
    }

    public List<String> getAlbumPicList() {
        if(albumPics != null && !"".equals(albumPics)){
            return Arrays.asList(albumPics.split(","));
        }
        return null;
    }
}

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.msb.dongbao.pms.db.dao.HomeRecommendSubjectDao">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.msb.dongbao.pms.model.entity.HomeRecommendSubject">
        <id column="id" property="id" />
        <result column="subject_id" property="subjectId" />
        <result column="subject_name" property="subjectName" />
        <result column="recommend_status" property="recommendStatus" />
        <result column="sort" property="sort" />
        <result column="gmt_create" property="gmtCreate" />
        <result column="gmt_modified" property="gmtModified" />
        <result column="create_uid" property="createUid" />
        <result column="create_uname" property="createUname" />
        <result column="modified_uid" property="modifiedUid" />
        <result column="modified_uname" property="modifiedUname" />
        <result column="enable" property="enable" />
        <result column="merchant_id" property="merchantId" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, subject_id, subject_name, recommend_status, sort, gmt_create, gmt_modified, create_uid, create_uname, modified_uid, modified_uname, enable, merchant_id
    </sql>


    <!-- 首页查询映射结果 -->
    <resultMap id="HomeResultMap" type="com.msb.dongbao.pms.model.dto.HomeRecommendSubjectDTO">
        <id column="id" property="id" />
        <result column="subject_id" property="subjectId" />
        <result column="subject_name" property="subjectName" />
        <result column="recommend_status" property="recommendStatus" />
        <result column="sort" property="sort" />
        <result column="album_pics" property="albumPics" />
        <result column="hot_words" property="hotWords" />
        <result column="pic" property="pic" />
        <collection property="productList" column="{subjectId=subject_id}" select="selectProductRelationByProductId"/>
    </resultMap>

    <!-- 查询首页推荐的专题信息 -->
    <select id="selectSubjectForHome" resultMap="HomeResultMap">
        SELECT
            hrs.id,
            hrs.subject_id,
            cs.title as subject_name,
            cs.album_pics,
            cs.hot_words,
            cs.pic
        FROM
            `home_recommend_subject` hrs
            LEFT JOIN cms_subject cs ON hrs.subject_id = cs.id
        WHERE
            hrs.recommend_status = 1
        limit 4;
    </select>

    <!-- 查询专题关联的商品列表-->
    <select id="selectProductRelationByProductId" resultMap="com.msb.dongbao.pms.db.dao.ProductDao.BaseResultMap">
        SELECT
            p.id,
            p.product_name,
            p.price,
            p.sales_num,
            p.default_pic,
            p.rel_category3_id,
            p.rel_tenant_id
        FROM
            cms_subject_product_relation cspr
            LEFT JOIN product p ON cspr.product_id = p.id
        WHERE
            cspr.subject_id = #{subjectId};
    </select>




</mapper>