create table `foodie-shop-dev`.items
(
id varchar(64) not null comment '商品主键id'
价格搜索
primary key,
item_name varchar(32) not null comment '商品名称商品名称',
cat_id int not null comment '分类外键id 分类id',
root_cat_id int not null comment '⼀级分类外键id',
sell_counts int not null comment '累计销售累计销售',
on_off_status int not null comment '上下架状态上下架状态,1：上架 2：下架',
content text not null comment '商品内容商品内容',
created_time datetime not null comment '创建时间',
updated_time datetime not null comment '更新时间'
)
comment '商品表商品信息相关表：分类表，商品图⽚表，商品规格表，商品参数表' charset = utf8mb4; 商品表 items
create table `foodie-shop-dev`.items_spec
(
id varchar(64) not null comment '商品规格id'
primary key,
item_id varchar(64) not null comment '商品外键id',
name varchar(32) not null comment '规格名称',
stock int not null comment '库存',
discounts decimal(4, 2) not null comment '折扣⼒度',
price_discount int not null comment '优惠价',
price_normal int not null comment '原价',
created_time datetime not null comment '创建时间',
updated_time datetime not null comment '更新时间'
)
comment '商品规格每⼀件商品都有不同的规格，不同的规格⼜有不同的价格和优惠⼒度，规格表为此设计' charset = utf8mb4;
商品规格 items_spec
⼆、SQL查询语句
1、根据关键字搜索商品列表
SELECT
i.id AS itemId,
i.item_name AS itemName,
i.sell_counts AS sellCounts,
ii.url AS imgUrl,
tempSpec.priceDiscount 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 priceDiscount FROM items_spec GROUP BY item_id ) tempSpec ON i.id = tempSpec.item_id WHERE
ii.is_main =1
i.item_name like'%%'
View Code
2、三级分类商品列表
SELECT
i.id AS itemId,
i.item_name AS itemName,
i.sell_counts AS sellCounts,
ii.url AS imgUrl,
tempSpec.priceDiscount 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 priceDiscount FROM items_spec GROUP BY item_id ) tempSpec ON i.id = tempSpec.item_id WHERE
ii.is_main =1
AND i.cat_id =73
View Code
三、Mapper⼦模块实现
1. xml⽂件定义
根据关键字搜索商品列表
三级分类商品列表
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.imooc.mapper.ItemsMapperCustom">
<select id="queryItemComments" parameterType="Map" resultType="com.imooc.pojo.vo.ItemCommentVO">
SELECT
icment_level AS commentLevel,
ic.sepc_name AS specName,
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 icment_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.priceDiscount 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 priceDiscount 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 == "c " ">
i.sell_counts desc
</when>
<when test=" paramsMap.sort == "p " ">
tempSpec.priceDiscount desc
</when>
<otherwise>
i.item_name asc
</otherwise>
</choose>
</select>
<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.priceDiscount 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 priceDiscount 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 == "c " ">
i.sell_counts desc
</when>
<when test=" paramsMap.sort == "p " ">
tempSpec.priceDiscount desc
</when>
<otherwise>
i.item_name asc
</otherwise>
</choose>
</select>
<!-- k：默认，代表默认排序，根据name -->
<!-- c：根据销量排序 -->
<!-- p：根据价格排序 -->
</mapper>
View Code
2. po 定义（前端展⽰使⽤的数据）
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; //以分为单位
}
View Code
3 接⼝定义
package com.imooc.mapper;
import com.imooc.pojo.vo.ItemCommentVO;
import com.imooc.pojo.vo.SearchItemsVO;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface ItemsMapperCustom {
public List<ItemCommentVO> queryItemComments (@Param("paramsMap") Map<String, Object> map); public List<SearchItemsVO> searchItems(@Param("paramsMap") Map<String, Object> map);
public List<SearchItemsVO> searchItemsByThirdCat(@Param("paramsMap") Map<String, Object> map);
}
View Code
四、Service⼦模块实现
1.接⼝定义
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议