1.订单超时取消:
订单超时取消,指的是当用户成功提交订单之后在规定时间内没有完成支付,则将订单关闭还原库存。
实现订单的超时取消业务通常有两种解决方案:
- 定时任务(循环扫描quartz)
- 延时队列(MQ)
实现流程:
1.2 quartz定时任务框架使用
1.2.1添加依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-quartz</artifactId>
</dependency>
1.2.2 创建定时任务 :
定时任务,每隔指定的时间就执行一次任务
案例:每隔3秒就打印一次HelloWorld
1.2.3 在启动类开启定时任务:
2.3 实现订单超时取消
2.3.1 在service子工程添加spring-boot-starter-quartz依赖
2.3.2 在api自动启动类添加@EnableScheduling注解
2.3.3 在service模块中添加job包,添加OrderTimeoutCheckJob类
package com.qfedu.fmmall.service.job;
import com.github.wxpay.sdk.WXPay;
import com.qfedu.fmmall.dao.OrdersMapper;
import com.qfedu.fmmall.entity.Orders;
import com.qfedu.fmmall.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import tk.mybatis.mapper.entity.Example;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description:
* @Author : Jerry
* @create : 2022-07-06 15:36
*/
@Component
public class OrderTimeoutCheckJob {
@Autowired
private OrdersMapper ordersMapper;
@Autowired
private OrderService orderService;
WXPay wxPay = new WXPay(new MyPayConfig());
@Scheduled(cron = "0/5 * * * * ?")
public void checkAndCloseOrder(){
try {
//1.查询超过30min订单状态依然为待支付状态的订单
Example example = new Example(Orders.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("status","1");
//当前时间推前30min
Date time = new Date(System.currentTimeMillis() - 30*60*1000);
criteria.andLessThan("createTime",time);
List<Orders> orders = ordersMapper.selectByExample(example);
//2.访问微信平台接口,确定当前订单最终的支付状态
for (int i = 0; i < orders.size(); i++) {
Orders order = orders.get(i);
HashMap<String,String> params = new HashMap<>();
params.put("out_trade_no",order.getOrderId());
Map<String, String> resp = wxPay.orderQuery(params);
System.out.println(resp);
if("SUCCESS".equalsIgnoreCase(resp.get("trade_state"))){
//2.1 如果订单已经支付,则修改订单为"代发货/已支付" status2
Orders updateOrder = new Orders();
updateOrder.setOrderId(order.getOrderId());
updateOrder.setStatus("2");
ordersMapper.updateByPrimaryKeySelective(updateOrder);
}else if("NOTPAY".equalsIgnoreCase(resp.get("trade_state"))){
//2.2 如果确实未支付 则取消订单:
// a.向微信支付发送请求,关闭当前订单的支付继续
Map<String,String> map = wxPay.closeOrder(params);
System.out.println(map);
// b.关闭订单
orderService.closeOrder(order.getOrderId());
}
}
}catch (Exception e) {
e.printStackTrace();
}
}
}
在orderService中新增方法:
SERIALIZABLE序列化级别: 所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰。
public void closeOrder(String orderId);
@Override
@Transactional(isolation = Isolation.SERIALIZABLE)
public void closeOrder(String orderId) {
synchronized (this){
// 1.修改当前订单:status=6已关闭 close_type=1 超时未支付
Orders cancleOrder = new Orders();
cancleOrder.setOrderId(orderId);
cancleOrder.setStatus("6");
cancleOrder.setCloseType(1);
ordersMapper.updateByPrimaryKeySelective(cancleOrder);
// 2.还原库存:先根据当前订单编号查询商品快照(skuid buy_count)-->修改product_sku
Example example1 = new Example(OrderItem.class);
Example.Criteria criteria1 = example1.createCriteria();
criteria1.andEqualTo("orderId",orderId);
List<OrderItem> orderItems = orderItemMapper.selectByExample(example1);
//还原库存
for (int j = 0; j < orderItems.size(); j++) {
OrderItem orderItem = orderItems.get(j);
//修改
ProductSku productSku = productSkuMapper.selectByPrimaryKey(orderItem.getSkuId());
productSku.setStock( productSku.getStock() + orderItem.getBuyCounts());
productSkuMapper.updateByPrimaryKeySelective(productSku);
}
}
}
2.按类别查询商品:
2.1 流程分析 :
2.2 接口开发:
2.2.1 根据类别查询商品接口:
数据库分析sql
数据库实现:
实现类:
productMapper新加:
/**
* 根据三级分类id分页查询商品信息
* @param cid 三级分类id
* @param start 起始索引
* @param limit 查询记录数
* @return
*/
public List<ProductVO> selectProductByCategoryId(@Param("cid") int cid,
@Param("start") int start,
@Param("limit") int limit);
<resultMap id="ProductVOMap2" type="com.qfedu.fmmall.entity.ProductVO" >
<id column="product_id" property="productId" jdbcType="VARCHAR" />
<result column="product_name" property="productName" jdbcType="VARCHAR" />
<result column="category_id" property="categoryId" jdbcType="INTEGER" />
<result column="root_category_id" property="rootCategoryId" jdbcType="INTEGER" />
<result column="sold_num" property="soldNum" jdbcType="INTEGER" />
<result column="product_status" property="productStatus" jdbcType="INTEGER" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
<result column="content" property="content" jdbcType="LONGVARCHAR" />
<!--根据商品id查询价格最低的套餐-->
<collection property="skus" column="product_id" select="com.qfedu.fmmall.dao.ProductSkuMapper.selectLowerestPriceByProductId"/>
</resultMap>
<select id="selectProductByCategoryId" resultMap="ProductVOMap2">
select product_id,
product_name,
category_id,
root_category_id,
sold_num,
product_status,
content,
create_time,
update_time
from product
where category_id=#{cid}
limit #{start},#{limit}
</select>
子查询:productSkuMapper:
package com.qfedu.fmmall.dao;
import com.qfedu.fmmall.entity.ProductSku;
import com.qfedu.fmmall.general.GeneralDAO;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface ProductSkuMapper extends GeneralDAO<ProductSku> {
/**
* 根据商品id,查询当前商品所有套餐中 价格最低的套餐
* @param productId
* @return
*/
public List<ProductSku> selectLowerestPriceByProductId(String productId);
}
<?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.qfedu.fmmall.dao.ProductSkuMapper" >
<resultMap id="BaseResultMap" type="com.qfedu.fmmall.entity.ProductSku" >
<!--
WARNING - @mbg.generated
-->
<id column="sku_id" property="skuId" jdbcType="VARCHAR" />
<result column="product_id" property="productId" jdbcType="VARCHAR" />
<result column="sku_name" property="skuName" jdbcType="VARCHAR" />
<result column="sku_img" property="skuImg" jdbcType="VARCHAR" />
<result column="untitled" property="untitled" jdbcType="VARCHAR" />
<result column="original_price" property="originalPrice" jdbcType="INTEGER" />
<result column="sell_price" property="sellPrice" jdbcType="INTEGER" />
<result column="discounts" property="discounts" jdbcType="DECIMAL" />
<result column="stock" property="stock" jdbcType="INTEGER" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
<result column="status" property="status" jdbcType="INTEGER" />
</resultMap>
<select id="selectLowerestPriceByProductId" resultMap="BaseResultMap">
select sku_id,product_id,sku_name,sku_img,untitled,original_price,
sell_price,discounts,stock,create_time,update_time,status
from product_sku
where product_id = #{productId}
ORDER BY sell_price limit 0,1
</select>
</mapper>
productService接口实现:
public R getProductsByCategoryId(int categoryId,int pageNum,int limit);
@Autowired
private ProductMapper productMapper;
@Override
public R getProductsByCategoryId(int categoryId, int pageNum, int limit) {
//1.查询商品数据
int start = (pageNum - 1)*limit;
List<ProductVO> productVOS = productMapper.selectProductByCategoryId(categoryId, start, limit);
//2.查询当前类别下的商品总记录数
Example example = new Example(Product.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("categoryId",categoryId);
int count = productMapper.selectCountByExample(example);
//3.计算总页数
int pageCount = count%limit==0? count/limit : count/limit+1;
//4.封装返回数据
PageHelper<ProductVO> pageHelper = new PageHelper<>(count,pageCount,productVOS);
return new R(ResStatus.OK,"success",pageHelper);
}
2.2.3 根据类别id查询当前类别下所有商品的品牌接口
sql:
数据库实现:
productmapper接口:
public List<String> selectBrandByCategoryId(int cid);
映射配置:
<select id="selectBrandByCategoryId" resultSets="java.util.List" resultType="String">
select Distinct brand
from product_params
where product_id in (
select product_id
from product
where category_id=49
)
</select>
service接口:
public R listBrands(int categoryId);
@Override
public R listBrands(int categoryId) {
List<String> brands = productMapper.selectBrandByCategoryId(categoryId);
return new R(ResStatus.OK,"success",brands);
}
controller实现:
//根据类别查询商品接口
@GetMapping("/listbycid/{cid}")
public R getProductsByCategoryId(@PathVariable("cid") int cid,int pageNum,int pageSize){
return productService.getProductsByCategoryId(cid,pageNum,pageSize);
}
//根据类别查询商品品牌接口
@GetMapping("/listbrands/{cid}")
public R getBrandsByCategoryId(@PathVariable("cid") int cid){
return productService.listBrands(cid);
}