-- 商品表
CREATE TABLE tb_product (
    id BIGSERIAL PRIMARY KEY,
    product_code VARCHAR(64) UNIQUE NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    category_id BIGINT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    status SMALLINT DEFAULT 1,
    description TEXT,
    specifications JSONB,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 訂單表
CREATE TABLE tb_order (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(32) UNIQUE NOT NULL,
    user_id BIGINT NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL,
    discount_amount NUMERIC(10,2) DEFAULT 0,
    pay_amount NUMERIC(10,2) NOT NULL,
    order_status SMALLINT NOT NULL,
    payment_status SMALLINT NOT NULL,
    payment_time TIMESTAMP,
    delivery_time TIMESTAMP,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 訂單明細(xì)表
CREATE TABLE tb_order_item (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    quantity INTEGER NOT NULL,
    subtotal NUMERIC(10,2) NOT NULL,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

@TableName(value = "tb_product")
public class Product {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    private String productCode;
    private String productName;
    private Long categoryId;
    private BigDecimal price;
    private Integer stockQuantity;
    private Integer status;
    private String description;
    
    @TableField(typeHandler = JsonTypeHandler.class)
    private Map<String, Object> specifications;
    
    private Date createdTime;
    private Date updatedTime;
    
    // 省略getter/setter
}

@TableName(value = "tb_order")
public class Order {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    private String orderNo;
    private Long userId;
    private BigDecimal totalAmount;
    private BigDecimal discountAmount;
    private BigDecimal payAmount;
    private Integer orderStatus;
    private Integer paymentStatus;
    private Date paymentTime;
    private Date deliveryTime;
    private Date createdTime;
    private Date updatedTime;
    
    // 業(yè)務(wù)方法
    public boolean canBeCanceled() {
        return this.orderStatus == 1; // 待支付狀態(tài)可取消
    }
}

@Service
public class ProductServiceImpl extends ServiceImpl<ProductDao, Product> 
    implements ProductService {
    
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean reduceStock(Long productId, Integer quantity) {
        // 使用悲觀鎖確保數(shù)據(jù)一致性
        Product product = baseMapper.selectByIdForUpdate(productId);
        if (product == null) {
            throw new BusinessException("商品不存在");
        }
        if (product.getStockQuantity() < quantity) {
            throw new BusinessException("庫存不足");
        }
        
        // 更新庫存
        Product updateProduct = new Product();
        updateProduct.setId(productId);
        updateProduct.setStockQuantity(product.getStockQuantity() - quantity);
        updateProduct.setUpdatedTime(new Date());
        
        return updateById(updateProduct);
    }
    
    @Override
    public IPage<ProductVo> searchProducts(Page<ProductVo> page, ProductQuery query) {
        return baseMapper.selectProductList(page, query);
    }
}

public interface ProductDao extends BaseMapper<Product> {
    
    @Select("SELECT * FROM tb_product WHERE id = #{id} FOR UPDATE")
    Product selectByIdForUpdate(Long id);
    
    IPage<ProductVo> selectProductList(IPage<ProductVo> page, 
            @Param("query") ProductQuery query);
}

@Service
public class OrderServiceImpl extends ServiceImpl<OrderDao, Order> 
    implements OrderService {
    
    @Autowired
    private ProductService productService;
    
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Order createOrder(OrderCreateRequest request) {
        // 1. 驗(yàn)證商品和庫存
        List<OrderItem> orderItems = validateProducts(request.getItems());
        
        // 2. 計(jì)算訂單金額
        BigDecimal totalAmount = calculateTotalAmount(orderItems);
        BigDecimal payAmount = totalAmount.subtract(request.getDiscountAmount());
        
        // 3. 創(chuàng)建訂單
        Order order = buildOrder(request, totalAmount, payAmount);
        baseMapper.insert(order);
        
        // 4. 創(chuàng)建訂單明細(xì)
        orderItems.forEach(item -> {
            item.setOrderId(order.getId());
            orderItemDao.insert(item);
        });
        
        // 5. 扣減庫存
        reduceProductsStock(orderItems);
        
        return order;
    }
    
    @Override
    public IPage<OrderVo> queryUserOrders(Page<OrderVo> page, Long userId, 
            OrderQuery query) {
        return baseMapper.selectUserOrders(page, userId, query);
    }
}

@Service
public class ProductServiceImpl implements ProductService {
    
    public List<Product> findHotProducts(int limit) {
        QueryWrapper<Product> wrapper = new QueryWrapper<>();
        wrapper.select("id", "product_name", "price", "sales_volume")
               .eq("status", 1)
               .gt("stock_quantity", 0)
               .orderByDesc("sales_volume")
               .last("LIMIT " + limit);
        
        return baseMapper.selectList(wrapper);
    }
    
    public IPage<Product> searchProductsByKeywords(Page<Product> page, 
            String keywords, Long categoryId) {
        QueryWrapper<Product> wrapper = new QueryWrapper<>();
        
        if (StringUtils.isNotBlank(keywords)) {
            wrapper.and(w -> w.like("product_name", keywords)
                             .or().like("description", keywords));
        }
        
        if (categoryId != null) {
            wrapper.eq("category_id", categoryId);
        }
        
        wrapper.eq("status", 1)
               .orderByDesc("created_time");
        
        return baseMapper.selectPage(page, wrapper);
    }
}

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      url: jdbc:kingbase8://localhost:54321/ecommerce
      username: app_user
      password: your_password
      initial-size: 5
      min-idle: 5
      max-active: 50
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false

-- 為常用查詢字段創(chuàng)建索引
CREATE INDEX idx_product_category ON tb_product(category_id, status);
CREATE INDEX idx_product_search ON tb_product USING gin(to_tsvector('simple', product_name));
CREATE INDEX idx_order_user_time ON tb_order(user_id, created_time DESC);

@Repository
public class OrderDao extends BaseMapper<Order> {
    
    public IPage<OrderVo> selectComplexOrders(IPage<OrderVo> page, 
            @Param("query") OrderComplexQuery query) {
        return page.setRecords(baseMapper.selectComplexOrders(page, query));
    }
}

// 錯(cuò)誤的做法：在方法中處理大量數(shù)據(jù)
@Transactional
public void batchProcessOrders(List<Long> orderIds) {
    for (Long orderId : orderIds) {
        processSingleOrder(orderId); // 處理單個(gè)訂單
    }
}

// 正確的做法：分批次處理
public void batchProcessOrders(List<Long> orderIds) {
    List<List<Long>> partitions = Lists.partition(orderIds, 100);
    for (List<Long> partition : partitions) {
        processOrderPartition(partition);
    }
}

@Transactional
void processOrderPartition(List<Long> orderIds) {
    for (Long orderId : orderIds) {
        processSingleOrder(orderId);
    }
}

@Service
public class SecKillService {
    
    @Autowired
    private RedissonClient redissonClient;
    
    @Transactional(rollbackFor = Exception.class)
    public boolean seckillProduct(Long productId, Long userId) {
        String lockKey = "seckill:lock:" + productId;
        RLock lock = redissonClient.getLock(lockKey);
        
        try {
            // 獲取分布式鎖
            if (lock.tryLock(3, 10, TimeUnit.SECONDS)) {
                // 檢查庫存
                Product product = productDao.selectByIdForUpdate(productId);
                if (product.getStockQuantity() <= 0) {
                    return false;
                }
                
                // 扣減庫存
                productDao.reduceStock(productId);
                
                // 創(chuàng)建訂單
                createSeckillOrder(productId, userId);
                return true;
            }
        } finally {
            lock.unlock();
        }
        return false;
    }
}

<configuration>
    <settings>
        <setting name="logImpl" value="SLF4J" />
    </settings>
    <plugins>
        <plugin interceptor="com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor">
            <property name="sqlLog" value="true"/>
        </plugin>
    </plugins>
</configuration>