Java+MySQL如何实现设计优惠券系统

优惠券系统是电商平台、餐饮服务、零售等行业中常见的营销工具。通过优惠券系统，商家可以吸引用户、提升销售额、增加用户粘性。本文将介绍如何使用Java和MySQL设计一个简单的优惠券系统。

1. 系统需求分析

在设计优惠券系统之前，首先需要明确系统的需求。一个基本的优惠券系统通常包括以下功能：

• 优惠券的创建：管理员可以创建不同类型的优惠券，如满减券、折扣券、无门槛券等。
• 优惠券的发放：优惠券可以通过手动发放、自动发放（如注册时发放）或用户领取的方式发放给用户。
• 优惠券的使用：用户在下单时可以使用优惠券，系统需要验证优惠券的有效性。
• 优惠券的查询：用户可以查看自己拥有的优惠券，管理员可以查看所有优惠券的使用情况。
• 优惠券的过期处理：系统需要定期检查并处理过期的优惠券。

2. 数据库设计

优惠券系统的核心是数据库设计。以下是几个关键的表结构设计：

2.1 用户表（user）

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.2 优惠券表（coupon）

CREATE TABLE coupon (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(20) NOT NULL UNIQUE,
    type ENUM('DISCOUNT', 'FIXED', 'PERCENTAGE') NOT NULL,
    value DECIMAL(10, 2) NOT NULL,
    min_order_amount DECIMAL(10, 2) DEFAULT 0,
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.3 用户优惠券表（user_coupon）

CREATE TABLE user_coupon (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    coupon_id INT NOT NULL,
    status ENUM('UNUSED', 'USED', 'EXPIRED') DEFAULT 'UNUSED',
    used_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (coupon_id) REFERENCES coupon(id)
);

2.4 订单表（order）

CREATE TABLE order (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    coupon_id INT NULL,
    discount_amount DECIMAL(10, 2) DEFAULT 0,
    final_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (coupon_id) REFERENCES coupon(id)
);

3. Java实现

3.1 创建优惠券

public class CouponService {
    private Connection connection;

    public CouponService(Connection connection) {
        this.connection = connection;
    }

    public void createCoupon(String code, String type, BigDecimal value, BigDecimal minOrderAmount, Timestamp startDate, Timestamp endDate) throws SQLException {
        String sql = "INSERT INTO coupon (code, type, value, min_order_amount, start_date, end_date) VALUES (?, ?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, code);
            stmt.setString(2, type);
            stmt.setBigDecimal(3, value);
            stmt.setBigDecimal(4, minOrderAmount);
            stmt.setTimestamp(5, startDate);
            stmt.setTimestamp(6, endDate);
            stmt.executeUpdate();
        }
    }
}

3.2 发放优惠券

public class UserCouponService {
    private Connection connection;

    public UserCouponService(Connection connection) {
        this.connection = connection;
    }

    public void issueCoupon(int userId, int couponId) throws SQLException {
        String sql = "INSERT INTO user_coupon (user_id, coupon_id) VALUES (?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, couponId);
            stmt.executeUpdate();
        }
    }
}

3.3 使用优惠券

public class OrderService {
    private Connection connection;

    public OrderService(Connection connection) {
        this.connection = connection;
    }

    public void createOrder(int userId, BigDecimal totalAmount, Integer couponId) throws SQLException {
        BigDecimal discountAmount = BigDecimal.ZERO;
        if (couponId != null) {
            Coupon coupon = getCoupon(couponId);
            if (coupon != null && coupon.isValid() && totalAmount.compareTo(coupon.getMinOrderAmount()) >= 0) {
                discountAmount = coupon.calculateDiscount(totalAmount);
                markCouponAsUsed(couponId, userId);
            }
        }
        BigDecimal finalAmount = totalAmount.subtract(discountAmount);

        String sql = "INSERT INTO order (user_id, total_amount, coupon_id, discount_amount, final_amount) VALUES (?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setBigDecimal(2, totalAmount);
            stmt.setObject(3, couponId);
            stmt.setBigDecimal(4, discountAmount);
            stmt.setBigDecimal(5, finalAmount);
            stmt.executeUpdate();
        }
    }

    private Coupon getCoupon(int couponId) throws SQLException {
        String sql = "SELECT * FROM coupon WHERE id = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, couponId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return new Coupon(rs);
            }
        }
        return null;
    }

    private void markCouponAsUsed(int couponId, int userId) throws SQLException {
        String sql = "UPDATE user_coupon SET status = 'USED', used_at = NOW() WHERE user_id = ? AND coupon_id = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, couponId);
            stmt.executeUpdate();
        }
    }
}

3.4 优惠券过期处理

public class CouponExpirationService {
    private Connection connection;

    public CouponExpirationService(Connection connection) {
        this.connection = connection;
    }

    public void expireCoupons() throws SQLException {
        String sql = "UPDATE user_coupon SET status = 'EXPIRED' WHERE status = 'UNUSED' AND coupon_id IN (SELECT id FROM coupon WHERE end_date < NOW())";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.executeUpdate();
        }
    }
}

4. 总结

本文介绍了如何使用Java和MySQL设计一个简单的优惠券系统。通过合理的数据库设计和Java代码实现，可以满足基本的优惠券创建、发放、使用和过期处理等功能。实际应用中，系统可能会更加复杂，需要考虑更多的业务场景和性能优化。