商城系统查询商品




    <resultMap id="blogResult" type="com.cryq.plus.entity.more.ProductVo">
        <id property="brandName" column="brand_name"/>
        <result property="spuTitle" column="spu_title"/>
        <result property="name" column="category_name"/>
        <result property="spuTitle" column="spu_title"/>
        <result property="categoryId" column="category_id"/>

        <result property="spuStatus" column="spu_status"/>
        <result property="spuActive" column="spu_active"/>

        <result property="spuInfo" column="spu_info"/>
        <collection property="imgSrc" ofType="com.cryq.plus.entity.ShopSpuImg">
            <id property="id" column="SpuImgId"/>
            <result property="mainImg" column="main_img"/>
            <result property="imgSrc" column="img_src"/>
            <result property="status" column="img_status"/>
        </collection>


        <collection property="lists" ofType="com.cryq.plus.entity.more.SkuAttrVo" column="id">
            <id property="id" column="attr_id"/>
            <result property="attrName" column="attr_name"/>
            <collection property="lists" ofType="com.cryq.plus.entity.more.SkuValVo" column="id" javaType="list">
               <id property="id" column="id"/>
                <result property="skuAttrValue" column="sku_attr_value"/>
                <result property="imgSrc" column="img_src"/>
                <result property="skuAttrId" column="skuAttrId"/>
            </collection>
        </collection>
    </resultMap>

    <select id="selectInfo" resultMap="blogResult" >
          SELECT
                b.brand_name AS brand_name,
                c.`name` AS category_name,
                c.id AS category_id,
                spu.spu_title AS spu_title,
                spu.spu_status AS spu_status,
                spu.spu_active AS spu_active,
                img.id AS SpuImgId,
                img.img_src AS img_src,
                img.status as img_status,
                img.main_img as main_img,
                info.spu_info AS spu_info,
                attr.id AS attr_id,
                attr.attr_name AS attr_name,
                val.id as id,
                val.sku_attr_value AS sku_attr_value,
                val.img_src AS img_src ,
                val.sku_attr_id AS skuAttrId
            FROM
                shop_brand b,
                shop_category c,
                shop_sku_attr attr,
                shop_sku_val2 val,
                shop_spu_img img,
                shop_spu_info info,
                shop_spu spu
            WHERE
                b.id = spu.brand_id
                AND c.id = spu.category_id
                AND spu.id = img.spu_id
                AND spu.store_id = img.store_id
                AND spu.id = info.spu_id
                AND spu.id = val.spu_id
                AND attr.id = val.sku_attr_id
                AND spu.store_id = #{storeId}
                AND spu.id = #{spuId}
                AND spu.spu_status = #{status}

            ORDER BY
                img.main_img DESC,val.is_main DESC,
                val.sort ASC

    </select>

推荐阅读更多精彩内容