Sql语句

 

ItemsMapperCustom.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.imooc.mapper.ItemsMapperCustom" >

  <select id="queryItemComments" parameterType="Map" resultType="com.imooc.pojo.vo.ItemCommentVO">
    SELECT
        ic.comment_level as commentLevel,
        ic.content as content,
        ic.sepc_name as specName,
        ic.created_time as createdTime,
        u.face as userFace,
        u.nickname as nickname
    FROM
        items_comments ic
    LEFT JOIN
        users u
    ON
        ic.user_id = u.id
    WHERE
        ic.item_id = #{paramsMap.itemId}
        <if test=" paramsMap.level != null and paramsMap.level != '' ">
          AND ic.comment_level = #{paramsMap.level}
        </if>
  </select>

    <select id="searchItems" parameterType="Map" resultType="com.imooc.pojo.vo.SearchItemsVO">
        SELECT
            i.id as itemId,
            i.item_name as itemName,
            i.sell_counts as sellCounts,
            ii.url as imgUrl,
            tempSpec.price_discount as price
        FROM
            items i
        LEFT JOIN
            items_img ii
        on
            i.id = ii.item_id
        LEFT JOIN
            (SELECT item_id,MIN(price_discount) as price_discount from items_spec GROUP BY item_id) tempSpec
        on
            i.id = tempSpec.item_id
        WHERE
            ii.is_main = 1
            <if test=" paramsMap.keywords != null and paramsMap.keywords != '' ">
                AND i.item_name like '%${paramsMap.keywords}%'
            </if>
            order by
            <choose>
                <when test=" paramsMap.sort == &quot;c&quot; ">
                    i.sell_counts desc
                </when>
                <when test=" paramsMap.sort == &quot;p&quot; ">
                    tempSpec.price_discount asc
                </when>
                <otherwise>
                    i.item_name asc
                </otherwise>
            </choose>
    </select>
    <!-- k: 默认，代表默认排序，根据name-->
    <!-- c: 根据销量排序-->
    <!-- p: 根据价格排序-->


    <select id="searchItemsByThirdCat" parameterType="Map" resultType="com.imooc.pojo.vo.SearchItemsVO">
        SELECT
            i.id as itemId,
            i.item_name as itemName,
            i.sell_counts as sellCounts,
            ii.url as imgUrl,
            tempSpec.price_discount as price
        FROM
          items i
        LEFT JOIN
          items_img ii
        on
          i.id = ii.item_id
        LEFT JOIN
          (SELECT item_id,MIN(price_discount) as price_discount from items_spec GROUP BY item_id) tempSpec
        on
          i.id = tempSpec.item_id
        WHERE
          ii.is_main = 1
          and
          i.cat_id = #{paramsMap.catId}
        order by
        <choose>
            <when test=" paramsMap.sort == &quot;c&quot; ">
                i.sell_counts desc
            </when>
            <when test=" paramsMap.sort == &quot;p&quot; ">
                tempSpec.price_discount asc
            </when>
            <otherwise>
                i.item_name asc
            </otherwise>
        </choose>
    </select>


    <select id="queryItemsBySpecIds" parameterType="List" resultType="com.imooc.pojo.vo.ShopcartVO">
        SELECT
            t_items.id as itemId,
            t_items.item_name as itemName,
            t_items_img.url as itemImgUrl,
            t_items_spec.id as specId,
            t_items_spec.`name` as specName,
            t_items_spec.price_discount as priceDiscount,
            t_items_spec.price_normal as priceNormal
        FROM
            items_spec t_items_spec
        LEFT JOIN
            items t_items
        ON
            t_items.id = t_items_spec.item_id
        LEFT JOIN
            items_img t_items_img
        on
            t_items_img.item_id = t_items.id
        WHERE
            t_items_img.is_main = 1
        AND
            t_items_spec.id IN
            <foreach collection="paramsList" index="index" item="specId" open="(" separator="," close=")">
              #{specId}
            </foreach>
    </select>

    <update id="decreaseItemSpecStock">

        update
            items_spec
        set
            stock = stock - #{pendingCounts}
        where
            id = #{specId}
        and
            stock >= #{pendingCounts}

    </update>

</mapper>

View Code

 

SearchItemsVO.java

package com.imooc.pojo.vo;

import java.util.Date;

/**
 * 用于展示商品搜索列表结果的VO
 */
public class SearchItemsVO {

    private String itemId;
    private String itemName;
    private int sellCounts;
    private String imgUrl;
    private int price;

    public String getItemId() {
        return itemId;
    }

    public void setItemId(String itemId) {
        this.itemId = itemId;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }

    public int getSellCounts() {
        return sellCounts;
    }

    public void setSellCounts(int sellCounts) {
        this.sellCounts = sellCounts;
    }

    public String getImgUrl() {
        return imgUrl;
    }

    public void setImgUrl(String imgUrl) {
        this.imgUrl = imgUrl;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }
}

View Code