MyBatis精控优惠券积分核销SQL之道✨

原创于 2025-06-26 19:49:51 发布 · 835 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #sql #tomcat

MyBatis 专栏收录该内容

52 篇文章

订阅专栏

💳 MyBatis 精控核销：管理员一站式处理优惠券与积分的 SQL (Structured Query Language, 结构化查询语言) 之道 🚀

Hello，各位对数据持久化细节与 SQL (Structured Query Language, 结构化查询语言) 艺术有不懈追求的开发者们！👋 我们已经探讨了如何使用 Spring Data JPA (Jakarta Persistence API, Jakarta 持久化应用程序接口) 构建一个集优惠券核销与积分抵扣于一体的管理员操作接口。今天，我们将再次挑战这个核心功能，但这次我们将运用 MyBatis 的强大力量。我们将深入 SQL (Structured Query Language, 结构化查询语言) 的世界，看看如何通过手动编写 SQL (Structured Query Language, 结构化查询语言)、利用 MyBatis 的特性以及在 Service 层精心编排，来打造一个同样健壮、安全且高效的“一站式核销”解决方案。如果你享受对每一行 SQL (Structured Query Language, 结构化查询语言) 的精准控制，或者你的项目技术栈是 MyBatis，那么这次的探索之旅将为你揭示 MyBatis 在处理此类复杂交易时的独特魅力和实践智慧！🛠️

📖 接口核心功能与技术栈概览 (MyBatis 版 - 交易处理)

特性/方面	描述	关键技术/模式 (MyBatis 版)
🎯 核心功能	管理员为指定用户处理一笔交易，该交易可选择使用一张用户优惠券进行抵扣，并/或使用用户的可用积分进行进一步抵扣。	MyBatis Mapper 接口与 XML (Extensible Markup Language, 可扩展标记语言) 映射文件，手动编写 SQL (Structured Query Language, 结构化查询语言) `SELECT`, `INSERT`, `UPDATE`。DTO (Data Transfer Object, 数据传输对象) 模式。
💳 优惠券核销	校验用户优惠券的有效性，计算优惠金额，更新优惠券状态并记录使用详情。	`UserCouponMapper` 和 `CouponTemplateMapper` 执行相关数据库操作。
💰 积分抵扣	校验用户可用积分，根据系统配置计算抵扣，更新用户已用积分，并记录积分流水（含可用积分快照）。	`MemberUserMapper`, `SystemConfigMapper`, `PointTransactionMapper` 执行相关数据库操作。
🛡️ 权限与校验	操作员（管理员）权限校验，对用户、优惠券、模板、积分配置等进行全面的有效性校验。	Service 层自定义校验逻辑, 调用 MyBatis Mapper 执行数据库校验查询。
✨ API (Application Programming Interface, 应用程序编程接口) 交互	前端通过 `POST /api/v1/admin/transactions/process` 发送 `ProcessTransactionPayload` DTO (Data Transfer Object, 数据传输对象)。后端返回 `TransactionProcessResultDto`。	`@RequestBody`, `@Valid` (DTO (Data Transfer Object, 数据传输对象) 校验), DTO (Data Transfer Object, 数据传输对象) 转换。
🔄 事务保障	整个交易处理过程封装在单个数据库事务中。	Spring `@Transactional` 注解应用于核心 Service 方法。
⚙️ 并发考量	对用户优惠券状态更新和用户积分余额更新等关键操作，提示了使用数据库层面锁（如 `SELECT ... FOR UPDATE`）的必要性。	在 Mapper XML (Extensible Markup Language, 可扩展标记语言) 的 `SELECT` 语句中使用数据库特定的锁机制。
⏱️ 时间戳管理	使用 MyBatis 时，实体的 `createdDate` 和 `lastModifiedDate` 字段需要手动在 Service 层或 SQL (Structured Query Language, 结构化查询语言) 中设置。	`new Date()` 设置时间，并在 `INSERT` 和 `UPDATE` 语句中包含这些字段。
🧩 技术栈核心	Java (一种面向对象的编程语言), Spring Boot, MyBatis, MySQL (一种关系型数据库管理系统) (或其它), Lombok (一个Java库，可以通过简单的注解形式来帮助消除样板式代码)。

🛠️ MyBatis 实现之旅：一步步构建

1. DTOs (Data Transfer Object, 数据传输对象) (保持不变)

ProcessTransactionPayload.java (请求)
TransactionProcessResultDto.java (响应)

这两个 DTO (Data Transfer Object, 数据传输对象) 的结构与 JPA (Jakarta Persistence API, Jakarta 持久化应用程序接口) 版本一致。

2. 定义 MyBatis Mapper XML (Extensible Markup Language, 可扩展标记语言) 文件

我们需要为 UserCoupon, CouponTemplate, MemberUser, SystemConfig, PointTransaction 定义相关的查询和更新语句。

UserCouponMapper.xml (部分，聚焦核销相关):

<?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.productQualification.coupon.mapper.UserCouponMapper">
    <resultMap id="UserCouponWithTemplateAndCurrencyResultMap" type="com.productQualification.coupon.domain.UserCoupon">
        <id property="id" column="uc_id"/>
        <result property="memberUserId" column="uc_member_user_id"/>
        <result property="couponTemplateId" column="uc_coupon_template_id"/>
        <result property="status" column="uc_status"/>
        <result property="validFrom" column="uc_valid_from"/>
        <result property="validTo" column="uc_valid_to"/>
        <!-- ... 其他 UserCoupon 字段 ... -->
        <association property="couponTemplate" javaType="com.productQualification.coupon.domain.CouponTemplate">
            <id property="id" column="ct_id"/>
            <result property="name" column="ct_name"/>
            <result property="type" column="ct_type"/>
            <result property="value" column="ct_value"/>
            <result property="discountRate" column="ct_discount_rate"/>
            <result property="threshold" column="ct_threshold"/>
            <result property="status" column="ct_status"/>
            <result property="currencyId" column="ct_currency_id"/>
            <association property="currency" javaType="com.productQualification.coupon.domain.Currency">
                <id property="id" column="cur_id"/>
                <result property="code" column="cur_code"/>
            </association>
        </association>
    </resultMap>

    <select id="findByIdForUpdate" resultMap="UserCouponWithTemplateAndCurrencyResultMap">
        SELECT
            uc.*, <!-- 假设 UserCoupon 表字段无前缀或用 uc.* -->
            ct.id as ct_id, ct.name as ct_name, ct.type as ct_type, ct.value as ct_value,
            ct.discount_rate as ct_discount_rate, ct.threshold as ct_threshold,
            ct.status as ct_status, ct.currency_id as ct_currency_id,
            cur.id as cur_id, cur.code as cur_code
        FROM user_coupon uc
        JOIN coupon_template ct ON uc.coupon_template_id = ct.id
        LEFT JOIN currency cur ON ct.currency_id = cur.id
        WHERE uc.id = #{id}
        FOR UPDATE  <!-- 悲观锁 -->
    </select>

    <update id="updateUserCouponStatusForUsage" parameterType="com.productQualification.coupon.domain.UserCoupon">
        UPDATE user_coupon
        SET status = #{status},
            used_at = #{usedAt},
            order_identifier = #{orderIdentifier},
            last_modified_date = #{lastModifiedDate} <!-- 手动更新时间戳 -->
        WHERE id = #{id} AND status = 0 <!-- 确保只从未使用的状态更新，增加并发保护 -->
    </update>
</mapper>

MemberUserMapper.xml (部分，聚焦积分更新相关):

<!-- MemberUserMapper.xml -->
<mapper namespace="com.productQualification.coupon.mapper.MemberUserMapper">
    <resultMap id="MemberUserResultMap" type="com.productQualification.coupon.domain.MemberUser">
        <!-- ... MemberUser 字段映射 ... -->
        <id property="id" column="id"/>
        <result property="totalPoints" column="total_points"/>
        <result property="usedPoints" column="used_points"/>
        <result property="status" column="status"/>
        <result property="miniProgramId" column="mini_program_id"/>
    </resultMap>

    <select id="findByIdForUpdate" resultMap="MemberUserResultMap">
        SELECT * FROM member_user WHERE id = #{id} FOR UPDATE <!-- 悲观锁 -->
    </select>

    <update id="updateUserUsedPoints" parameterType="com.productQualification.coupon.domain.MemberUser">
        UPDATE member_user
        SET used_points = #{usedPoints},
            last_modified_date = #{lastModifiedDate} <!-- 手动更新时间戳 -->
        WHERE id = #{id}
    </update>
</mapper>

SystemConfigMapper.xml (需要 findByMiniProgramId):

<!-- SystemConfigMapper.xml -->
<mapper namespace="com.productQualification.coupon.mapper.SystemConfigMapper">
    <resultMap id="SystemConfigResultMap" type="com.productQualification.coupon.domain.SystemConfig">
        <id property="id" column="id"/>
        <result property="exchangeRate" column="exchange_rate"/>
        <result property="maxDeductionLimit" column="max_deduction_limit"/>
        <result property="miniProgramId" column="mini_program_id"/>
    </resultMap>
    <select id="findByMiniProgramId" resultMap="SystemConfigResultMap">
        SELECT * FROM system_config WHERE mini_program_id = #{miniProgramId}
    </select>
</mapper>

PointTransactionMapper.xml (需要 insertPointTransaction - 与之前博客一致)

XML (Extensible Markup Language, 可扩展标记语言) 关键点：

UserCouponWithDetailsResultMap: 使用嵌套的 <association> 来一次性加载 UserCoupon 及其关联的 CouponTemplate 和 Currency（部分字段）。
FOR UPDATE: 在 findByIdForUpdate 方法中，为 SELECT 语句添加 FOR UPDATE (MySQL (一种关系型数据库管理系统) 语法，其他数据库可能不同) 来实现数据库级别的悲观锁，防止并发问题。
原子更新条件: 在 updateUserCouponStatusForUsage 中，WHERE id = #{id} AND status = 0 增加了一层并发保护，确保只有在优惠券是未使用状态时才更新。Service 层仍需检查更新行数。

3. 定义 MyBatis Mapper 接口

UserCouponMapper.java:

@Mapper
public interface UserCouponMapper {
    Optional<UserCoupon> findByIdForUpdate(@Param("id") Integer id); // 带锁查询
    int updateUserCouponStatusForUsage(UserCoupon userCoupon); // 返回影响行数
    // ... (insertUserCoupon, countByMemberUserIdAndCouponTemplateId 等)
}

MemberUserMapper.java:

@Mapper
public interface MemberUserMapper {
    Optional<MemberUser> findByIdForUpdate(@Param("id") Integer id); // 带锁查询
    int updateUserUsedPoints(MemberUser memberUser); // 返回影响行数
    // ... (findById 等)
}

SystemConfigMapper.java, PointTransactionMapper.java, AdminMiniProgramMapper.java (与之前 MyBatis 博客中的定义类似)。

4. Service 层改造：`TransactionProcessingService.java` (MyBatis 版)

package com.productQualification.coupon.service;

import com.productQualification.common.exception.*; // All custom exceptions
import com.productQualification.coupon.domain.*;
import com.productQualification.coupon.dto.ProcessTransactionPayload;
import com.productQualification.coupon.dto.TransactionProcessResultDto;
import com.productQualification.coupon.mapper.*; // All mappers
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.Date;
import java.util.Objects;
import java.util.Optional;

@Service
public class TransactionProcessingService {
    private static final Logger logger = LoggerFactory.getLogger(TransactionProcessingService.class);

    private final UserCouponMapper userCouponMapper;
    // CouponTemplateMapper might not be needed if UserCouponMapper.findByIdForUpdate loads enough template details
    private final MemberUserMapper memberUserMapper;
    private final SystemConfigMapper systemConfigMapper;
    private final PointTransactionMapper pointTransactionMapper;
    private final AdminMiniProgramMapper adminMiniProgramMapper;

    // ... (Constants for status, types etc. as in previous JPA blog) ...
    public static final byte USER_COUPON_STATUS_UNUSED = 0; /* ... */
    public static final byte TEMPLATE_TYPE_THRESHOLD_VOUCHER = 1; /* ... */
    public static final byte POINT_TX_TYPE_USE_FOR_DEDUCTION = 2; /* ... */
    public static final byte POINT_SOURCE_CONSUME_DEDUCTION = 3; /* ... */


    @Autowired
    public TransactionProcessingService(UserCouponMapper ucMapper, CouponTemplateMapper ctMapper,
                                        MemberUserMapper muMapper, SystemConfigMapper scMapper,
                                        PointTransactionMapper ptMapper, AdminMiniProgramMapper ampMapper) {
        this.userCouponMapper = ucMapper;
        // this.couponTemplateMapper = ctMapper; // May not be needed if UserCoupon's resultMap is comprehensive
        this.memberUserMapper = muMapper;
        this.systemConfigMapper = scMapper;
        this.pointTransactionMapper = ptMapper;
        this.adminMiniProgramMapper = ampMapper;
    }

    @Transactional
    public TransactionProcessResultDto processTransactionByAdmin(ProcessTransactionPayload payload, Integer operatingAdminId) {
        logger.info("Admin {} processing transaction (MyBatis) for member {}: {}", operatingAdminId, payload.getMemberUserId(), payload);
        // ... (Asserts for payload) ...
        Date now = new Date();

        MemberUser memberUser = memberUserMapper.findByIdForUpdate(payload.getMemberUserId()) // 加锁
                .orElseThrow(() -> new NotFoundException("目标用户不存在: ID " + payload.getMemberUserId()));
        if (memberUser.getStatus() == 0) throw new MyRuntimeException("目标用户状态异常。");
        checkAdminPermissionForMiniProgram(operatingAdminId, memberUser.getMiniProgramId());

        TransactionProcessResultDto result = new TransactionProcessResultDto(/*...*/);
        BigDecimal currentAmountToPay = payload.getOriginalAmount();
        BigDecimal totalDiscountApplied = BigDecimal.ZERO;

        // --- 1. 处理优惠券 ---
        if (payload.getUserCouponId() != null) {
            UserCoupon userCoupon = userCouponMapper.findByIdForUpdate(payload.getUserCouponId()) // 加锁并加载关联模板
                    .orElseThrow(() -> new NotFoundException("优惠券不存在: ID " + payload.getUserCouponId()));
            CouponTemplate template = userCoupon.getCouponTemplate(); // 从 resultMap 获取
            if (template == null) throw new MyRuntimeException("无法获取优惠券模板信息 for UserCoupon ID: " + userCoupon.getId());

            validateUserCouponForUsage(userCoupon, memberUser, template, payload.getOriginalAmount(), now);
            BigDecimal couponDiscount = calculateCouponDiscount(template, payload.getOriginalAmount());

            if (couponDiscount.compareTo(BigDecimal.ZERO) > 0) {
                BigDecimal actualCouponDiscount = couponDiscount.min(currentAmountToPay);
                currentAmountToPay = currentAmountToPay.subtract(actualCouponDiscount);
                totalDiscountApplied = totalDiscountApplied.add(actualCouponDiscount);

                userCoupon.setStatus(USER_COUPON_STATUS_USED);
                userCoupon.setUsedAt(now);
                userCoupon.setOrderIdentifier(payload.getTransactionReference());
                userCoupon.setLastModifiedDate(now); // 手动设置时间戳
                int updatedRows = userCouponMapper.updateUserCouponStatusForUsage(userCoupon);
                if (updatedRows == 0) throw new MyRuntimeException("优惠券核销失败，可能已被并发使用或状态已改变。");

                result.setUsedUserCouponId(userCoupon.getId());
                result.setCouponDiscountAmount(actualCouponDiscount);
            }
        }
        result.setTotalDiscountAmount(totalDiscountApplied);
        result.setFinalAmount(currentAmountToPay);

        // --- 2. 处理积分抵扣 ---
        if (payload.getPointsToUse() != null && payload.getPointsToUse() > 0 && currentAmountToPay.compareTo(BigDecimal.ZERO) > 0) {
            MemberUser freshMemberUserForPoints = memberUserMapper.findByIdForUpdate(memberUser.getId()).orElseThrow(/*...*/); // 加锁
            long availablePoints = freshMemberUserForPoints.getTotalPoints() - freshMemberUserForPoints.getUsedPoints();
            if (payload.getPointsToUse() > availablePoints) throw new InsufficientPointsException(/*...*/);

            SystemConfig pointsConfig = systemConfigMapper.findByMiniProgramId(freshMemberUserForPoints.getMiniProgramId())
                    .orElseThrow(() -> new MyRuntimeException("小程序未配置积分规则。"));
            // ... (计算 actualPointsDeductionAmount 和 actualPointsUsed) ...
            BigDecimal actualPointsDeductionAmount = calculateActualPointsDeduction(payload.getPointsToUse(), availablePoints, pointsConfig, currentAmountToPay);
            int actualPointsUsed = calculateActualPointsUsed(actualPointsDeductionAmount, pointsConfig.getExchangeRate());


            if (actualPointsUsed > 0) {
                long currentUsedPoints = freshMemberUserForPoints.getUsedPoints();
                freshMemberUserForPoints.setUsedPoints(currentUsedPoints + actualPointsUsed);
                freshMemberUserForPoints.setLastModifiedDate(now); // 手动设置时间戳
                memberUserMapper.updateUserUsedPoints(freshMemberUserForPoints);

                PointTransaction transaction = new PointTransaction();
                // ... (填充 PointTransaction，手动设置 createdDate, lastModifiedDate, transactionDate) ...
                transaction.setMemberUserId(freshMemberUserForPoints.getId());
                transaction.setPointsChange(-actualPointsUsed);
                transaction.setAvailablePointsBefore(availablePoints);
                transaction.setAvailablePointsAfter(availablePoints - actualPointsUsed);
                transaction.setTransactionType(POINT_TX_TYPE_USE_FOR_DEDUCTION);
                transaction.setSourceTriggerType(POINT_SOURCE_CONSUME_DEDUCTION);
                transaction.setSourceBusinessId(payload.getTransactionReference());
                transaction.setDescription("订单/交易积分抵扣");
                transaction.setTransactionDate(now);
                transaction.setCreatedDate(now);
                transaction.setLastModifiedDate(now);
                pointTransactionMapper.insertPointTransaction(transaction);

                currentAmountToPay = currentAmountToPay.subtract(actualPointsDeductionAmount);
                totalDiscountApplied = totalDiscountApplied.add(actualPointsDeductionAmount);
                result.setPointsUsed(actualPointsUsed);
                result.setPointsDeductionAmount(actualPointsDeductionAmount);
            }
        }
        result.setTotalDiscountAmount(totalDiscountApplied);
        result.setFinalAmount(currentAmountToPay);
        result.setSuccess(true);
        result.setMessage("交易处理成功。");
        return result;
    }

    // 辅助方法 (validateUserCouponForUsage, calculateCouponDiscount, checkAdminPermissionForMiniProgram,
    // calculateActualPointsDeduction, calculateActualPointsUsed) 与 JPA 版本博客中的类似，
    // 确保 checkAdminPermissionForMiniProgram 使用 AdminMiniProgramMapper。
    private void validateUserCouponForUsage(UserCoupon uc, MemberUser mu, CouponTemplate ct, BigDecimal amount, Date date){/*...*/}
    private BigDecimal calculateCouponDiscount(CouponTemplate ct, BigDecimal amount){/*...*/ return BigDecimal.ZERO;}
    private void checkAdminPermissionForMiniProgram(Integer adminId, Integer mpId){
         if (mpId == null) throw new MyRuntimeException("权限校验失败：小程序ID为空");
         if(!adminMiniProgramMapper.existsByAdminIdAndMiniProgramId(adminId, mpId)) throw new PermissionDeniedException("管理员无权操作此小程序资源");
    }
    private BigDecimal calculateActualPointsDeduction(long pointsToUse, long availablePoints, SystemConfig config, BigDecimal amountToPay){/*...*/ return BigDecimal.ZERO;}
    private int calculateActualPointsUsed(BigDecimal deductionAmount, BigDecimal exchangeRate){/*...*/ return 0;}
}

MyBatis 版本 Service 层改动要点：

依赖注入: 注入 MyBatis Mapper 接口。
数据库操作: 调用 Mapper 方法执行 SQL (Structured Query Language, 结构化查询语言)。
时间戳管理: 必须手动为所有被修改或新创建的实体设置 createdDate 和 lastModifiedDate。
并发控制: 在 Mapper XML (Extensible Markup Language, 可扩展标记语言) 的 SELECT 语句中使用 FOR UPDATE 实现悲观锁。在 UPDATE user_coupon 时，通过 WHERE ... AND status = 0 增加乐观检查。
关联数据加载: UserCouponMapper.findByIdForUpdate 的 resultMap 通过 <association> 一次性加载了 CouponTemplate 和 Currency 的必要信息，避免了 N+1。

6. Controller 层 (`TransactionProcessingController.java`)

Controller 层无需任何改动。

📊 交互时序图 (Sequence Diagram - MyBatis 交易处理)

🔄 状态图与类图 (概念上与之前博客类似)

实体状态流转概念不变。类图的主要变化是 Service 层现在依赖 MyBatis Mapper 接口。

💡 英文缩写全称及中文解释

(与上一篇博客中的列表一致)

🧠 思维导图 (Markdown 格式)

在这里插入图片描述

🎉 总结：MyBatis 下的交易处理“定盘星”！

通过 MyBatis 实现管理员代客核销优惠券和抵扣积分的统一接口，我们再次展现了其在 SQL (Structured Query Language, 结构化查询语言) 精细控制和复杂映射方面的强大能力。虽然这意味着我们需要手动编写更多的 SQL (Structured Query Language, 结构化查询语言) 和 XML (Extensible Markup Language, 可扩展标记语言) 配置，并更细致地处理时间戳、并发控制等问题，但最终获得的是对数据访问每一个环节的完全掌控。

在这个实现中，我们利用了 MyBatis 的 resultMap 和 <association> 来高效加载关联数据，通过在 SQL (Structured Query Language, 结构化查询语言)层面使用 FOR UPDATE 来处理并发读写，并通过 Service 层的精心编排确保了整个复杂交易的原子性和数据一致性。

选择 MyBatis，就是选择了一条更贴近数据库、更能发挥 SQL (Structured Query Language, 结构化查询语言)威力的道路。希望这篇基于 MyBatis 的实战博客，能为你在构建类似复杂业务功能时提供有力的技术参考和设计启发！如果你在 MyBatis 的高级应用、事务并发或性能调优方面有更多独到见解，欢迎在评论区分享你的宝贵经验！👇 Happy SQL (Structured Query Language, 结构化查询语言) Orchestration! 🌊💻