MyBatis 精雕细琢：打造终极优惠券模板查询 API 的 SQL 艺术 ✨

MyBatis实现优惠券模板查询API

原创于 2025-06-19 14:25:46 发布 · 793 阅读

23 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #sql #java

MyBatis 专栏收录该内容

52 篇文章

订阅专栏

🚀 MyBatis 精雕细琢：打造终极优惠券模板查询 API (Application Programming Interface, 应用程序编程接口) 的 SQL (Structured Query Language, 结构化查询语言) 艺术 🛠️

Hello，各位对数据持久化细节有极致追求的开发者们！👋 我们已经探讨了如何使用 Spring Data JPA (Jakarta Persistence API, Jakarta 持久化应用程序接口) 构建一个功能强大的优惠券模板查询接口。今天，我们将再次挑战这个核心功能，但这次我们将运用 MyBatis 的力量，深入 SQL (Structured Query Language, 结构化查询语言) 的世界，看看如何通过手动编写 SQL (Structured Query Language, 结构化查询语言)、利用 MyBatis 的动态 SQL (Structured Query Language, 结构化查询语言) 特性以及精巧的 <resultMap>，来打造一个同样灵活、高效且数据丰富的查询体验。如果你享受对 SQL (Structured Query Language, 结构化查询语言) 的完全掌控，或者你的项目技术栈是 MyBatis，那么这次的探索之旅绝对不容错过！让我们开始吧！⚙️

📖 接口功能与技术栈概览 (MyBatis 版 - 查询优惠券模板)

特性/方面	描述	关键技术/模式 (MyBatis 版)
🎯 核心功能	管理员根据其权限查询名下小程序关联的优惠券模板 (`CouponTemplate`) 列表。	MyBatis Mapper 接口与 XML (Extensible Markup Language, 可扩展标记语言) 映射文件，手动编写 SQL (Structured Query Language, 结构化查询语言)。
📄 分页支持	支持标准的分页参数 `page` (页码) 和 `size` (每页大小)。	自定义 `PageWithSearch` 类，Service 层配合 MyBatis 分页插件 (如 PageHelper) 实现分页。
↕️ 排序支持	支持基于一个或多个模板字段 (`properties`) 的升序 (`ASC`) 或降序 (`DESC`) (`direction`) 排序。	`PageWithSearch` 处理排序参数，Service 层将 `Sort` 信息转换为分页插件可识别的排序字符串，并在动态 SQL (Structured Query Language, 结构化查询语言) 中安全地应用。
🔍 动态搜索	支持前端传递 `field` (要搜索的字段名，如 “name”, “type”, “miniProgramName”) 和 `value` (搜索值) 进行通用条件查询。	MyBatis 动态 SQL (Structured Query Language, 结构化查询语言) (`<if>`, `<choose>`, `<where>`, `<foreach>`) 在 XML (Extensible Markup Language, 可扩展标记语言) 映射文件中构建动态 `WHERE` 子句。
🛡️ 权限控制	查询结果严格限制在当前登录管理员有权管理的小程序范围内的优惠券模板。	Service 层通过 `AdminMiniProgramMapper` 获取管理员可操作的 `miniProgramId` 列表，并将其作为参数传递给 `CouponTemplateMapper` 的查询方法，在动态 SQL (Structured Query Language, 结构化查询语言) 中应用。
✨ API (Application Programming Interface, 应用程序编程接口) 响应	返回统一的 `BaseResult` 结构，数据部分为 `Page<CouponTemplateDto>`，使用 DTO (Data Transfer Object, 数据传输对象) 避免序列化问题并丰富展示信息。	DTO (Data Transfer Object, 数据传输对象) 转换在 Service 层完成。
🔗 关联数据加载	在一次 SQL (Structured Query Language, 结构化查询语言) 查询中通过 `JOIN` 加载关联的 `MiniProgramConfig` 和 `Currency` 信息，并通过 MyBatis 的 `<resultMap>` 和 `<association>` 进行映射，以在 DTO (Data Transfer Object, 数据传输对象) 中显示名称/符号等，避免 N+1。	MyBatis `<resultMap>` 的高级映射功能。
🧩 技术栈核心	Java (一种面向对象的编程语言), Spring Boot, MyBatis, MySQL (一种关系型数据库管理系统) (或其它), Lombok (一个Java库，可以通过简单的注解形式来帮助消除样板式代码), Swagger (API (Application Programming Interface, 应用程序编程接口) 文档), PageHelper (MyBatis 分页插件 - 推荐)。

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

1. 前端请求参数载体：`PageWithSearch.java` (保持不变)

我们继续使用这个类来接收前端的分页、排序和通用搜索字段。

2. API (Application Programming Interface, 应用程序编程接口) 响应的“形象代言人”：`CouponTemplateDto.java` (保持不变)

DTO (Data Transfer Object, 数据传输对象) 结构保持不变，用于封装返回给前端的数据。

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

AdminMiniProgramMapper.xml (获取管理员小程序ID列表 - 与之前 MyBatis 博客中的一致)

CouponTemplateMapper.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.CouponTemplateMapper">

    <resultMap id="CouponTemplateWithAssociationsResultMap" type="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="total" column="ct_total"/>
        <result property="issued" column="ct_issued"/>
        <result property="limitPerUser" column="ct_limit_per_user"/>
        <result property="validFrom" column="ct_valid_from"/>
        <result property="validTo" column="ct_valid_to"/>
        <result property="description" column="ct_description"/>
        <result property="status" column="ct_status"/>
        <result property="currencyId" column="ct_currency_id"/>
        <result property="miniProgramId" column="ct_mini_program_id"/>
        <result property="stackable" column="ct_stackable"/>
        <result property="createdDate" column="ct_created_date"/>
        <result property="lastModifiedDate" column="ct_last_modified_date"/>
        <!-- 关联 MiniProgramConfig -->
        <association property="miniProgramConfig" javaType="com.productQualification.coupon.domain.MiniProgramConfig">
            <id property="id" column="mpc_id"/>
            <result property="name" column="mpc_name"/>
            <result property="appId" column="mpc_app_id"/>
        </association>
        <!-- 关联 Currency -->
        <association property="currency" javaType="com.productQualification.coupon.domain.Currency">
            <id property="id" column="cur_id"/>
            <result property="code" column="cur_code"/>
            <result property="name" column="cur_name"/>
            <result property="symbol" column="cur_symbol"/>
        </association>
    </resultMap>

    <sql id="selectCouponTemplateFields">
        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.total as ct_total, ct.issued as ct_issued, ct.limit_per_user as ct_limit_per_user,
        ct.valid_from as ct_valid_from, ct.valid_to as ct_valid_to, ct.description as ct_description,
        ct.status as ct_status, ct.currency_id as ct_currency_id, ct.mini_program_id as ct_mini_program_id,
        ct.stackable as ct_stackable, ct.created_date as ct_created_date, ct.last_modified_date as ct_last_modified_date,
        mpc.id as mpc_id, mpc.name as mpc_name, mpc.app_id as mpc_app_id, <!-- MiniProgramConfig fields -->
        cur.id as cur_id, cur.code as cur_code, cur.name as cur_name, cur.symbol as cur_symbol <!-- Currency fields -->
    </sql>

    <sql id="couponTemplateJoins">
        LEFT JOIN mini_program_config mpc ON ct.mini_program_id = mpc.id
        LEFT JOIN currency cur ON ct.currency_id = cur.id
    </sql>

    <sql id="couponTemplateWhereConditions">
        <where>
            <!-- 权限过滤 -->
            <if test="manageableMiniProgramIds != null and !manageableMiniProgramIds.isEmpty()">
                AND ct.mini_program_id IN
                <foreach item="item" index="index" collection="manageableMiniProgramIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <!-- 通用字段搜索 -->
            <if test="field != null and field != '' and value != null and value != ''">
                <choose>
                    <when test="field == 'name'">
                        AND ct.name LIKE CONCAT('%', #{value}, '%')
                    </when>
                    <when test="field == 'type'">
                        AND ct.type = #{value} <!-- 假设value是byte类型 -->
                    </when>
                    <when test="field == 'status'">
                        AND ct.status = #{value} <!-- 假设value是byte类型 -->
                    </when>
                    <when test="field == 'miniProgramName'">
                        AND mpc.name LIKE CONCAT('%', #{value}, '%')
                    </when>
                    <when test="field == 'currencyCode'">
                        AND cur.code = UPPER(#{value})
                    </when>
                    <!-- 更多可搜索字段 -->
                </choose>
            </if>
            <!-- 其他来自 PageWithSearch 的特定搜索条件 -->
        </where>
    </sql>

    <!-- 查询列表 (PageHelper 会自动处理分页和总数) -->
    <select id="findCouponTemplatesByParams" resultMap="CouponTemplateWithAssociationsResultMap">
        SELECT <include refid="selectCouponTemplateFields"/>
        FROM coupon_template ct
        <include refid="couponTemplateJoins"/>
        <include refid="couponTemplateWhereConditions"/>
        <!-- 排序由 PageHelper 根据传入的 Pageable/Sort 信息处理，或通过 PageHelper.orderBy() 设置 -->
    </select>

    <!-- 如果不用 PageHelper，你需要一个单独的 count 查询 -->
    <!--
    <select id="countCouponTemplatesByParams" resultType="long">
        SELECT count(ct.id)
        FROM coupon_template ct
        <include refid="couponTemplateJoins"/>
        <include refid="couponTemplateWhereConditions"/>
    </select>
    -->
    <!-- 其他创建、更新的SQL语句 -->
</mapper>

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

CouponTemplateWithAssociationsResultMap: 使用两个 <association> 分别映射关联的 MiniProgramConfig 和 Currency 对象。这是实现一次查询加载所有DTO (Data Transfer Object, 数据传输对象)所需信息的关键。
列别名: 在 selectCouponTemplateFields 和 resultMap 中大量使用列别名（如 ct_id, mpc_name, cur_code）来清晰区分来自不同表的字段，并正确映射到实体属性。
动态 SQL (Structured Query Language, 结构化查询语言) for Search: couponTemplateWhereConditions 使用动态 SQL (Structured Query Language, 结构化查询语言) 构建 WHERE 子句，包括了基于 manageableMiniProgramIds 的权限过滤和基于 field/value 的通用搜索。

4. 定义 MyBatis Mapper 接口

AdminMiniProgramMapper.java (与之前 MyBatis 博客中的一致)

CouponTemplateMapper.java:

package com.productQualification.coupon.mapper;
import com.productQualification.coupon.domain.CouponTemplate;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;

@Mapper
public interface CouponTemplateMapper {
    // PageHelper 会拦截这个方法进行分页
    // Map<String, Object> params 将包含 manageableMiniProgramIds 和 PageWithSearch 中的搜索字段
    List<CouponTemplate> findCouponTemplatesByParams(@Param("params") Map<String, Object> params);

    // 如果不用 PageHelper，还需要一个 count 方法
    // long countCouponTemplatesByParams(@Param("params") Map<String, Object> params);

    // 其他创建、更新的 Mapper 方法
}

5. Service 层改造：`CouponTemplateService.java` (MyBatis 版)

package com.productQualification.coupon.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.productQualification.common.entity.PageWithSearch;
import com.productQualification.common.util.SqlUtil;
import com.productQualification.coupon.domain.AdminMiniProgram;
import com.productQualification.coupon.domain.CouponTemplate;
import com.productQualification.coupon.domain.Currency;
import com.productQualification.coupon.domain.MiniProgramConfig;
import com.productQualification.coupon.dto.CouponTemplateDto;
// CouponTemplatePayload DTO for create/update
import com.productQualification.coupon.mapper.AdminMiniProgramMapper;
import com.productQualification.coupon.mapper.CouponTemplateMapper;
// Repositories for other entities if still using JPA for them, or Mappers
import com.productQualification.coupon.repository.MiniProgramConfigRepository;
import com.productQualification.coupon.repository.CurrencyRepository;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

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

    private final CouponTemplateMapper couponTemplateMapper;
    private final AdminMiniProgramMapper adminMiniProgramMapper;
    // For DTO conversion, we might still need to fetch these if not fully mapped by MyBatis,
    // or ensure MyBatis resultMap loads them. Our resultMap does load them.
    // private final MiniProgramConfigRepository miniProgramConfigRepository;
    // private final CurrencyRepository currencyRepository;


    @Autowired
    public CouponTemplateService(CouponTemplateMapper couponTemplateMapper,
                                 AdminMiniProgramMapper adminMiniProgramMapper
                                 /*, MiniProgramConfigRepository mpcRepo, CurrencyRepository curRepo */) {
        this.couponTemplateMapper = couponTemplateMapper;
        this.adminMiniProgramMapper = adminMiniProgramMapper;
        // this.miniProgramConfigRepository = mpcRepo;
        // this.currencyRepository = curRepo;
    }

    @Transactional(readOnly = true)
    public Page<CouponTemplateDto> findCouponTemplates(Integer adminId, PageWithSearch pageWithSearch) {
        logger.info("Admin {} searching for coupon templates (MyBatis): {}", adminId, pageWithSearch);

        List<Integer> manageableMiniProgramIds = adminMiniProgramMapper.findMiniProgramIdsByAdminId(adminId);
        if (manageableMiniProgramIds == null || manageableMiniProgramIds.isEmpty()) {
            return new PageImpl<>(Collections.emptyList(), pageWithSearch.toPageable(), 0);
        }

        Pageable pageable = pageWithSearch.toPageable();
        Map<String, Object> params = new HashMap<>();
        params.put("manageableMiniProgramIds", manageableMiniProgramIds);
        if (StringUtils.hasText(pageWithSearch.getField())) params.put("field", pageWithSearch.getField().trim());
        if (StringUtils.hasText(pageWithSearch.getValue())) params.put("value", pageWithSearch.getValue().trim());
        // ... (add other specific search params from PageWithSearch to the map) ...

        PageHelper.startPage(pageable.getPageNumber() + 1, pageable.getPageSize());
        if (pageable.getSort().isSorted()) {
            String orderByClause = pageable.getSort().stream()
                .map(order -> {
                    String property = order.getProperty();
                    String dbColumn;
                    // 根据属性名安全转换为数据库列名，并添加表别名
                    if (property.startsWith("miniProgramConfig.")) { // 假设前端可能传 "miniProgramConfig.name"
                        dbColumn = SqlUtil.camelToUnderlineForMybatis("mpc." + property.substring("miniProgramConfig.".length()));
                    } else if (property.startsWith("currency.")) { // 假设前端可能传 "currency.code"
                        dbColumn = SqlUtil.camelToUnderlineForMybatis("cur." + property.substring("currency.".length()));
                    } else {
                        dbColumn = SqlUtil.camelToUnderlineForMybatis("ct." + property); // 主表 coupon_template
                    }
                    return dbColumn != null ? dbColumn + " " + order.getDirection().name() : null;
                })
                .filter(Objects::nonNull)
                .collect(Collectors.joining(", "));
            if (StringUtils.hasText(orderByClause)) {
                PageHelper.orderBy(orderByClause);
            }
        } else {
            PageHelper.orderBy("ct.created_date DESC"); // 默认排序
        }

        List<CouponTemplate> templateEntities = couponTemplateMapper.findCouponTemplatesByParams(params);
        PageInfo<CouponTemplate> pageInfo = new PageInfo<>(templateEntities);

        List<CouponTemplateDto> dtos = pageInfo.getList().stream()
                .map(template -> convertToDto(template, template.getMiniProgramConfig(), template.getCurrency())) // MyBatis resultMap 已加载关联对象
                .collect(Collectors.toList());

        return new PageImpl<>(dtos, pageable, pageInfo.getTotal());
    }

    // convertToDto 和其他辅助方法 (getCouponTypeDescription等) 与JPA版本博客中的一致
    private CouponTemplateDto convertToDto(CouponTemplate entity, MiniProgramConfig miniProgram, Currency currency) {
        if (entity == null) return null;
        CouponTemplateDto dto = new CouponTemplateDto();
        // ... 基础属性映射 ...
        dto.setId(entity.getId());
        dto.setName(entity.getName());
        dto.setType(entity.getType());
        dto.setTypeDesc(getCouponTypeDescription(entity.getType()));
        dto.setValue(entity.getValue());
        dto.setDiscountRate(entity.getDiscountRate());
        dto.setThreshold(entity.getThreshold());
        dto.setTotal(entity.getTotal());
        dto.setIssued(entity.getIssued());
        if (entity.getTotal() != null && entity.getIssued() != null) {
            dto.setRemaining(Math.max(0, entity.getTotal() - entity.getIssued()));
        } else {
            dto.setRemaining(0);
        }
        dto.setLimitPerUser(entity.getLimitPerUser());
        dto.setValidFrom(entity.getValidFrom());
        dto.setValidTo(entity.getValidTo());
        dto.setDescription(entity.getDescription());
        dto.setStatus(entity.getStatus());
        dto.setStatusDesc(getCouponStatusDescription(entity.getStatus()));
        dto.setCurrencyId(entity.getCurrencyId());
        dto.setMiniProgramId(entity.getMiniProgramId());
        dto.setStackable(entity.getStackable());
        dto.setStackableDesc(getStackableDescription(entity.getStackable()));
        dto.setCreatedDate(entity.getCreatedDate());
        dto.setLastModifiedDate(entity.getLastModifiedDate());

        if (miniProgram != null && miniProgram.getId() != null) { // 检查关联对象是否真的被加载
            dto.setMiniProgramName(miniProgram.getName());
        }
        if (currency != null && currency.getId() != null) { // 检查关联对象是否真的被加载
            dto.setCurrencyCode(currency.getCode());
            dto.setCurrencySymbol(currency.getSymbol());
        }
        return dto;
    }
    private String getCouponTypeDescription(Byte type) { /* ... */ return ""; }
    private String getCouponStatusDescription(Byte status) { /* ... */ return ""; }
    private String getStackableDescription(Byte stackable) { /* ... */ return ""; }

    // createCouponTemplate 和 updateCouponTemplate 的 MyBatis 实现会类似，
    // 涉及调用 CouponTemplateMapper.insertCouponTemplate 和 updateCouponTemplate，
    // 以及手动处理时间戳和实体校验。
}

MyBatis 版本 Service 层改动要点：

依赖注入: 注入 MyBatis Mapper 接口。
参数构造: 将权限相关的 manageableMiniProgramIds 和 PageWithSearch 中的搜索条件放入一个 Map 中，传递给 CouponTemplateMapper。
分页与排序 (使用 PageHelper):
- PageHelper.startPage() 启动分页。
- 从 Pageable 对象中解析排序信息，构建安全的 ORDER BY 子句字符串（注意属性名到列名的转换和表别名），然后调用 PageHelper.orderBy()。
结果包装与 DTO (Data Transfer Object, 数据传输对象) 转换: PageInfo 用于包装 MyBatis 返回的列表，PageImpl 用于构造成 Spring Data Page 对象。convertToDto 方法现在依赖于 MyBatis resultMap 中配置的 <association> 来获取关联的 MiniProgramConfig 和 Currency 对象。

6. Controller 层 (`CouponTemplateController.java`)

Controller 层无需任何改动，继续与 Service 层交互。

📊 交互时序图 (Sequence Diagram - MyBatis 查询优惠券模板)

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

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

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

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

🧠 思维导图 (Markdown 格式)

在这里插入图片描述

🎉 总结：MyBatis 下的优惠券模板查询“利器”！

通过 MyBatis 的精细化 SQL (Structured Query Language, 结构化查询语言) 控制和强大的 resultMap 功能，我们成功构建了一个功能全面的优惠券模板查询接口。它不仅支持分页、排序和基于多种条件的动态搜索，还能在一次数据库查询中高效地加载所需的关联数据（小程序名称、币种信息），并通过 DTO (Data Transfer Object, 数据传输对象) 模式清晰地呈现给前端，有效避免了 N+1 问题。

虽然 MyBatis 要求开发者更深入地参与 SQL (Structured Query Language, 结构化查询语言) 的编写和结果集映射，但这种投入换来的是对数据访问过程的完全掌控和极致的优化潜力。结合 PageHelper 等优秀插件，MyBatis 在处理复杂查询和分页排序等常见需求时，依然能展现出其独特的魅力和高效性。

希望这篇基于 MyBatis 实现的优惠券模板查询接口博客，能为你在持久层技术选型和复杂查询实现方面提供有益的思路和实践参考！如果你在 MyBatis 的高级应用或性能调优方面有更多独到见解，欢迎在评论区分享你的智慧！👇 Happy SQL (Structured Query Language, 结构化查询语言) with Control! 🐘💻