mybatise多层集合嵌套查询(3层)

谁借莪１个温暖的怀抱￠ 2022-12-10 02:23 231阅读 0赞

1，用一个sql，多个resultMap  一般另起一个子map，不直接在collection中再套

每个子项都需要id才可避免子项自身被覆盖，永远1条

例如：  
    
    <resultMap id="singleProductVOResultOnline"  
               type="com.mcd.cn.rdc.pcm.product.business.vo.onlinemenu.ProductDetailVO">  
        <id column="id" jdbcType="BIGINT" property="id"/>  
        <result column="code" jdbcType="VARCHAR" property="productCode"/>  
        <result column="name" jdbcType="VARCHAR" property="productNameCn"/>  
        <result column="long\_name" jdbcType="VARCHAR" property="productLongNameCn"/>  
        <result column="productLongNameEn" jdbcType="VARCHAR" property="productLongNameEn"/>  
        <result column="description" jdbcType="VARCHAR" property="descCn"/>  
        <result column="dayweek" jdbcType="VARCHAR" property="dayOfWeek"/>  
        <result column="type" jdbcType="INTEGER" property="productType"/>  
        <result column="ui\_type" jdbcType="TINYINT" property="uiType"/>  
        <result column="bu" jdbcType="VARCHAR" property="bu"/>  
        <result column="daypart" jdbcType="VARCHAR" property="daypart"/>  
        <collection property="productTagList" resultMap="templateCategoryTagVOResultOnline"></collection>  
        <collection property="salesPeriod" resultMap="templateCategoryPeriodVOResultOnline"></collection>  
        <collection property="salesTime" resultMap="templateCategoryDaypartVOResultOnline"></collection>  
        <collection property="grillList" resultMap="GrillAndItemMap"></collection>  
    </resultMap>  
    <resultMap id="templateCategoryTagVOResultOnline" type="com.mcd.cn.rdc.pcm.product.business.vo.onlinemenu.TagVO">  
        <result property="tagCode" column="tab\_code"/>  
        <result property="tagName" column="tab\_name"/>  
        <result property="tagImage" column="tab\_image"/>  
    </resultMap>  
    <resultMap id="templateCategoryPeriodVOResultOnline"  
               type="com.mcd.cn.rdc.pcm.product.business.vo.onlinemenu.SalesPeriodVO">  
        <result property="startDate" column="start\_date"/>  
        <result property="endDate" column="end\_date"/>  
    </resultMap>  
    <resultMap id="templateCategoryDaypartVOResultOnline"  
               type="com.mcd.cn.rdc.pcm.product.business.vo.onlinemenu.SalesTimeVO">  
        <result property="startTime" column="start\_time"/>  
        <result property="endTime" column="end\_time"/>  
    </resultMap>  
  <resultMap id="GrillAndItemMap" type="com.mcd.cn.rdc.pcm.product.business.vo.product.ProductGrillVO">  
        <id column="grill\_id" jdbcType="BIGINT" property="id" />  
        <result column="grill\_code" jdbcType="VARCHAR" property="grillCode" />  
        <result column="grill\_name" jdbcType="VARCHAR" property="grillNameCn" />  
        <result column="grillNameEn" jdbcType="VARCHAR" property="grillNameEn" />  
        <result column="grill\_mode" jdbcType="TINYINT" property="grillMode" />  
        <result column="display\_mode" jdbcType="TINYINT" property="displayMode" />  
        <result column="grill\_image" jdbcType="VARCHAR" property="grillImage" />  
        <result column="quantity" jdbcType="INTEGER" property="quantity" />  
        <result column="max\_quantity" jdbcType="INTEGER" property="maxQuantity" />  
        <result column="min\_quantity" jdbcType="INTEGER" property="minQuantity" />  
        <result column="sort" jdbcType="INTEGER" property="sort" />  
        <result column="tax\_rate\_type\_code" jdbcType="VARCHAR" property="taxRateTypeCode"/>  
        <result column="tax\_rate" jdbcType="DECIMAL" property="taxRate"/>  
        <collection property="grillItemList" resultMap="GrillItemVOResult"></collection>  
    </resultMap>  
    <resultMap id="GrillItemVOResult" type="com.mcd.cn.rdc.pcm.product.business.vo.product.ProductGrillItemVO">  
        <id column="grill\_item\_id" jdbcType="BIGINT" property="id" />  
        <result column="grill\_item\_code" jdbcType="VARCHAR" property="grillItemCode" />  
        <result column="item\_name" jdbcType="VARCHAR" property="grillItemNameCn" />  
        <result column="item\_quantity" jdbcType="INTEGER" property="quantity" />  
        <result column="is\_modify" jdbcType="TINYINT" property="isModify" />  
        <result column="is\_default" jdbcType="TINYINT" property="isDefault" />  
        <result column="grill\_item\_image" jdbcType="VARCHAR" property="grillItemImage" />  
    </resultMap>  
      
    <select id="getSingleItemProductListForNew"  
            parameterType="com.mcd.cn.rdc.pcm.product.business.vo.template.TemplateParam"  
            resultMap="singleProductVOResultOnline">  
        SELECT  
        p.id,  
        p.CODE,  
        p.NAME,  
        p.long\_name,  
        p.description,  
        p.type,  
        p.sales\_flag,  
        p.ui\_type,  
        p.dayweek,  
        p.bu,  
        p.has\_grill,  
        p.daypart,  
        p.channel,  
        ttr.CODE tax\_rate\_type\_code,  
        ttr.tax\_rate,  
        tsp.start\_date,  
        tsp.end\_date,  
        tsd.start\_time,  
        tsd.end\_time,  
        le.content AS productLongNameEn,  
        tpg.id AS grill\_id,  
        tpg.grill\_code,  
        tpg.NAME AS grill\_name,  
        tpg.grill\_mode,  
        tpg.display\_mode,  
        tpg.quantity,  
        tpg.max\_quantity,  
        tpg.min\_quantity,  
        tpg.grill\_image,  
        tpgi.id grill\_item\_id,  
        tpgi.grill\_item\_code,  
        tpgi.NAME item\_name,  
        tpgi.quantity item\_quantity,  
        tpgi.is\_modify,  
        tpgi.grill\_item\_image,  
        tpgi.is\_default,  
        ttr1.CODE grill\_tax\_rate\_type\_code,  
        ttr1.tax\_rate grill\_tax\_rate  
        FROM  
        t\_product p  
        LEFT JOIN t\_product\_sales\_period tsp ON p.CODE = tsp.product\_code  
        AND p.data\_version = tsp.data\_version  
        LEFT JOIN t\_product\_sales\_daypart tsd ON p.CODE = tsd.product\_code  
        AND p.data\_version = tsd.data\_version  
        LEFT JOIN t\_product\_grill tpg ON tpg.product\_code = p.CODE  
        AND tpg.data\_version = p.data\_version  
        LEFT JOIN t\_product\_grill\_item tpgi ON tpg.data\_version = tpgi.data\_version  
        AND tpg.product\_code = tpgi.product\_code  
        AND tpg.grill\_code = tpgi.grill\_code  
        LEFT JOIN t\_master\_tax\_rate ttr1 ON tpg.tax\_rate\_type\_code = ttr1.  
        CODE LEFT JOIN t\_master\_tax\_rate ttr ON p.tax\_rate\_type\_code = ttr.  
        CODE LEFT JOIN t\_international\_language le ON le.key\_code = p.CODE  
        AND le.LANGUAGE = 'en\_US'  
        AND le.field\_name = 'productLongNameEn'  
        AND le.table\_name = 't\_product'  
        AND le.data\_version = p.data\_version  
        WHERE  
        p.\`status\` = '1'  
        AND p.data\_version = \#\{dataVersion\}  
        <if test="productCodes != null  and productCodes.size()>0">  
            AND p.code IN (  
            <foreach collection="productCodes" item="obj" index="index" separator=",">  
                \#\{obj\}  
            </foreach>  
            )  
        </if>  
    </select>  
   
 2，用多个sql,第一个是主sql，其他是map嵌套sql  
 参看：  
  https://www.jianshu.com/p/80711449476c  
 https://www.cnblogs.com/hnsunlong/p/7195594.html  
 自动2层？  
   
   
 https://blog.csdn.net/u011006672/article/details/85258757?utm\_medium=distribute.pc\_aggpage\_search\_result.none-task-blog-2~all~first\_rank\_v2~rank\_v25-10-85258757.nonecase&utm\_term=mybatis%20%E5%A4%9A%E5%B1%82%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2  
          
        SELECT