- 商品表
商品表
create table 'mmall_product'(
'id' int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
'category_id' int(11) NOT NULL COMMENT '分类id,对应mmal_category分类表的主键',
'name' varchar(100) NOT NULL COMMENT '商品名称',
'subtitle' varchar(200) DEFAULT NULL COMMENT '商品副标题',
'main_image' varchar(500) DEFAULT NULL COMMENT '产品主图,url相对地址',
'sub_images' text COMMENT '图片地址,json格式,扩展用',
'detail' text COMMENT '商品详情',
'price' decimal(20,2) NOT NULL COMMENT '价格,单位-元保留两位小数',
'stock' int(11) NOT NULL COMMENT '库存数量',
'status int(6) DEFAULT '1' COMMENT '商品状态:1-在售,2-下架,3-删除',
'create_time' datetime DEFAULT NULL COMMENT '创建时间',
'update_time' datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY ('id')
)ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
- 设置共用常量类
public class Const {
public static final String CURRENT_USER = "currentUser";
public static final String EMAIL="email";
public static final String USERNAME="username";
public interface Role{
int ROLE_CUSTOMER=0;//普通用户
int ROLE_ADMIN=1;//管理员
}
public interface ProductListOrderBy{
Set<String> PRICE_ASC_DESC= Sets.newHashSet("price_desc","price_asc");
}
}
一、前台商品详情
- 思路:根据前端传来的商品id进行查询,转换成Vo类展示在前端上。
- controller
//前台商品详情
//http://localhost:8080/product/detail.do?productId=2
@RequestMapping("detail.do")
@ResponseBody
public ServerResponse<ProductDetailVo> getProductDetail(Integer productId){
return iProductService.productDetail(productId);
}
- impl
//前台商品详情
public ServerResponse<ProductDetailVo> productDetail(Integer productId){
if (productId == null){
return ServerResponse.createByErrorCodeMessage(ResponseCode.ILLEGAL_ARGUMENT.getCode(), ResponseCode.ILLEGAL_ARGUMENT.getDesc());
}
Product product=productMapper.selectByProductId(productId);
if (product == null){
return ServerResponse.createByErrorMessage("商品已下架或者删除");
}
ProductDetailVo productDetailVo=assembleProductDetailVo(product);
return ServerResponse.createBySuccess(productDetailVo);
}
private ProductDetailVo assembleProductDetailVo(Product product){
ProductDetailVo productDetailVo = new ProductDetailVo();
productDetailVo.setId(product.getId());
productDetailVo.setSubtitle(product.getSubtitle());
productDetailVo.setPrice(product.getPrice());
productDetailVo.setMainImage(product.getMainImage());
productDetailVo.setSubImages(product.getSubImages());
productDetailVo.setCategoryId(product.getCategoryId());
productDetailVo.setDetail(product.getDetail());
productDetailVo.setName(product.getName());
productDetailVo.setStatus(product.getStatus());
productDetailVo.setStock(product.getStock());
//赋值imageHost,从配置文件中获取图片的前缀
productDetailVo.setImageHost(PropertiesUtil.getProperty("ftp.server.http.prefix","http://image.mmall.com/"));
//找出该商品的父节点商品
Category category=categoryMapper.selectByPrimaryKey(product.getCategoryId());
//为空则默认为根节点
if (category == null){
productDetailVo.setParentCategoryId(0);
}else {
productDetailVo.setParentCategoryId(category.getParentId());
}
productDetailVo.setCreateTime(DateTimeUtil.dateToStr(product.getCreateTime()));
productDetailVo.setUpdateTime(DateTimeUtil.dateToStr(product.getUpdateTime()));
return productDetailVo;
}
二、商品搜索及动态排序List
- controller
//前台商品搜索及动态排序
//http://localhost:8080/product/list.do?keyword=xxx&categoryId=1&orderBy=price_desc
@RequestMapping("list.do")
@ResponseBody
public ServerResponse<PageInfo> list(@RequestParam(value="keyword",required = false) String keyword,@RequestParam(value="categoryId",required = false) Integer categoryId,@RequestParam(value="pageNum",defaultValue = "1") int pageNum,@RequestParam(value = "pageSize",defaultValue = "10")int pageSize,@RequestParam(value = "orderBy",defaultValue = "")String orderBy){
return iProductService.getProductByKeywordCategory(keyword, categoryId, pageNum, pageSize, orderBy);
}
- impl
//前台商品搜索及动态排序
public ServerResponse<PageInfo> getProductByKeywordCategory(String keyword,Integer categoryId,int pageNum,int pageSize,String orderBy){
if (StringUtils.isBlank(keyword) && categoryId == null ){
return ServerResponse.createByErrorCodeMessage(ResponseCode.ILLEGAL_ARGUMENT.getCode(),ResponseCode.ILLEGAL_ARGUMENT.getDesc());
}
List<Integer> categoryIdList = new ArrayList<Integer>();
//如果直接点击一个分类
if (categoryId != null){
Category category=categoryMapper.selectByPrimaryKey(categoryId);
//没有该分类,并且没有关键字,这个时候返回一个空的结果集,不报错
if (category == null && StringUtils.isBlank(keyword)){
PageHelper.startPage(pageNum,pageSize);
List<ProductListVo> productListVoList=Lists.newArrayList();
PageInfo pageInfo=new PageInfo(productListVoList);
return ServerResponse.createBySuccess(pageInfo);
}
//找出这个分类及其子类的所有商品
categoryIdList=iCategoryService.selecteCategoryAndChildrenById(category.getId()).getData();
}
//关键字不为空
if (StringUtils.isNotBlank(keyword)){
//%关键字%用于实现like模糊查询
keyword=new StringBuilder().append("%").append(keyword).append("%").toString();
}
//设置分页的起始页和每页数量的大小
PageHelper.startPage(pageNum,pageSize);
//从前端传来的orderBy为price_desc或price_asc
if (StringUtils.isNotBlank(orderBy)){
if (Const.ProductListOrderBy.PRICE_ASC_DESC.contains(orderBy)){
//price_desc,以_进行分割成price和desc
String[] orderByArray=orderBy.split("_");
//OrderBy price desc
PageHelper.orderBy(orderByArray[0]+" "+orderByArray[1]);
}
}
//查询,如果关键字为空则为用户直接点击分类查看分类下的商品
List<Product> productList=productMapper.selectByNameAndCategoryIds(StringUtils.isBlank(keyword)?null:keyword,categoryIdList.size()==0?null:categoryIdList);
List<ProductListVo> productListVoList=Lists.newArrayList();
for (Product product : productList){
ProductListVo productListVo=assembleProductListVo(product);
productListVoList.add(productListVo);
}
//封装pojo查询结果,pojo转成Vo展示在前台上
PageInfo pageInfo=new PageInfo(productList);
pageInfo.setList(productListVoList);
return ServerResponse.createBySuccess(pageInfo);
}
- Mybatis-Mapper
public interface ProductMapper {
Product selectByPrimaryKey(Integer id);
//前台商品详情
Product selectByProductId(@Param("productId") Integer productId);
List<Product> selectByNameAndCategoryIds(@Param("productName") String productName,@Param("categoryIdList")List<Integer> categoryIdList);
}
- Mybatis-SQL
<?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.mmall.dao.ProductMapper" >
<resultMap id="BaseResultMap" type="com.mmall.pojo.Product" >
<constructor >
<idArg column="id" jdbcType="INTEGER" javaType="java.lang.Integer" />
<arg column="category_id" jdbcType="INTEGER" javaType="java.lang.Integer" />
<arg column="name" jdbcType="VARCHAR" javaType="java.lang.String" />
<arg column="subtitle" jdbcType="VARCHAR" javaType="java.lang.String" />
<arg column="main_image" jdbcType="VARCHAR" javaType="java.lang.String" />
<arg column="sub_images" jdbcType="VARCHAR" javaType="java.lang.String" />
<arg column="detail" jdbcType="VARCHAR" javaType="java.lang.String" />
<arg column="price" jdbcType="DECIMAL" javaType="java.math.BigDecimal" />
<arg column="stock" jdbcType="INTEGER" javaType="java.lang.Integer" />
<arg column="status" jdbcType="INTEGER" javaType="java.lang.Integer" />
<arg column="create_time" jdbcType="TIMESTAMP" javaType="java.util.Date" />
<arg column="update_time" jdbcType="TIMESTAMP" javaType="java.util.Date" />
</constructor>
</resultMap>
<sql id="Base_Column_List" >
id, category_id, name, subtitle, main_image, sub_images, detail, price, stock, status,
create_time, update_time
</sql>
<select id="selectByProductId" resultMap="BaseResultMap" parameterType="int">
select <include refid="Base_Column_List"></include>
from mmall_product
where id=#{productId} and status=1
</select>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from mmall_product
where id = #{id,jdbcType=INTEGER}
</select>
<select id="selectByNameAndCategoryIds" resultMap="BaseResultMap" parameterType="map">
select <include refid="Base_Column_List"></include>
from mmall_product
where status=1
<if test="productName != null">
and name like #{productName}
</if>
<if test="categoryIdList != null">
and category_id in
<foreach item="item" index="index" open="(" separator="," close=")" collection="categoryIdList">
#{item}
</foreach>
</if>
</select>
</mapper>