MyBatis-Plus实战：用apply搞定那些‘奇奇怪怪’的数据库函数查询

MyBatis-Plus实战：用apply搞定那些‘奇奇怪怪’的数据库函数查询

在业务开发中，我们经常会遇到一些需要借助数据库函数才能实现的查询需求。比如按日期格式化后的结果查询、按字段的某部分匹配、或者使用数据库特有的JSON处理函数等。这些需求如果直接用MyBatis-Plus的常规方法链式调用往往难以实现，而apply方法就是解决这类问题的利器。

apply方法允许我们直接拼接SQL片段，同时又能安全地处理参数绑定，避免了SQL注入风险。它特别适合那些需要使用数据库特有函数或复杂表达式的场景。本文将深入探讨apply的各种实战用法，并分享在不同数据库环境下的兼容性写法。

1. apply方法的核心原理与基础用法

apply方法是MyBatis-Plus中QueryWrapper和LambdaQueryWrapper提供的一个强大工具，它的核心作用是允许开发者直接插入自定义的SQL片段。与直接拼接SQL字符串不同，apply提供了安全的参数绑定机制。

1.1 基本语法结构

apply方法有两种重载形式：

// 基础形式 apply(String applySql, Object... params) // 带条件判断的形式 apply(boolean condition, String applySql, Object... params)

参数说明：

• applySql：要拼接的SQL片段，可以包含{0}、{1}等占位符
• params：用于替换占位符的参数值
• condition：布尔值，决定是否应用此条件

1.2 参数绑定与安全机制

apply最强大的特性是它的参数绑定机制。考虑以下两种写法：

// 不安全的直接拼接 apply("date_format(dateColumn,'%Y-%m-%d') = '2018-08-08'") // 安全的参数绑定 apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2018-08-08")

第一种写法直接将值硬编码在SQL中，存在SQL注入风险。第二种写法使用{0}占位符，MyBatis-Plus会在运行时安全地绑定参数，自动处理类型转换和特殊字符转义。

1.3 基础使用示例

让我们看一个完整的示例，查询生日为特定日期的用户：

@Test void testApplyBasic() { QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.apply("date_format(birthday,'%Y-%m-%d') = {0}", "1990-10-01"); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }

这段代码会生成如下SQL：

SELECT id, name, birthday FROM user WHERE (date_format(birthday,'%Y-%m-%d') = ?)

参数1990-10-01会被安全地绑定到预编译语句中。

2. 处理复杂函数查询场景

apply真正发挥威力的地方在于处理那些常规方法无法表达的复杂查询需求。下面我们来看几个典型场景。

2.1 日期时间处理

日期查询是业务系统中最常见的需求之一。不同数据库的日期函数差异很大，apply可以很好地处理这些差异。

查询某个月份生日的用户：

// MySQL写法 wrapper.apply("month(birthday) = {0}", 10); // PostgreSQL写法 wrapper.apply("extract(month from birthday) = {0}", 10);

查询最近7天注册的用户：

// MySQL写法 wrapper.apply("date(create_time) >= date_sub(curdate(), interval 7 day)"); // 使用参数绑定 wrapper.apply("date(create_time) >= date_sub(curdate(), interval {0} day)", 7);

2.2 字符串处理

字符串处理函数在模糊查询、数据清洗等场景非常有用。

查询用户名包含特定子串的用户：

// 查询用户名第2-4个字符为"abc"的用户 wrapper.apply("substring(username, 2, 3) = {0}", "abc");

按邮箱域名分组统计：

// 获取@符号后的部分 wrapper.groupBy("substring(email, position('@' in email) + 1)") .select("substring(email, position('@' in email) + 1) as domain, count(*) as cnt");

2.3 JSON数据处理

现代数据库大多支持JSON类型和相应的查询函数。

查询JSON字段中的特定属性：

// MySQL JSON_EXTRACT wrapper.apply("JSON_EXTRACT(profile, '$.age') > {0}", 18); // PostgreSQL jsonb wrapper.apply("profile::jsonb->>'age' > {0}", "18");

查询JSON数组包含特定元素：

// MySQL JSON_CONTAINS wrapper.apply("JSON_CONTAINS(tags, {0})", "\"premium\"");

3. 多数据库兼容性解决方案

在实际项目中，我们经常需要支持多种数据库。不同数据库的函数语法差异很大，如何编写兼容的apply语句是一个挑战。

3.1 数据库方言识别

MyBatis-Plus提供了DbType枚举和IDbRouter接口来识别和处理不同数据库的差异。我们可以利用这些特性来编写条件化的apply语句。

String applySql; if (DbType.MYSQL == dbType) { applySql = "date_format(birthday,'%Y-%m-%d') = {0}"; } else if (DbType.POSTGRE_SQL == dbType) { applySql = "to_char(birthday, 'YYYY-MM-DD') = {0}"; } wrapper.apply(applySql, "1990-10-01");

3.2 使用自定义SQL片段

对于复杂的多数据库支持，可以考虑将不同数据库的SQL片段提取到配置文件中：

mybatis-plus: sql-templates: date-format-query: mysql: "date_format({column},'%Y-%m-%d') = {value}" postgresql: "to_char({column}, 'YYYY-MM-DD') = {value}" oracle: "to_char({column}, 'YYYY-MM-DD') = {value}"

然后在代码中根据当前数据库类型选择对应的模板：

String template = sqlTemplates.get("date-format-query." + dbType); String applySql = template.replace("{column}", "birthday") .replace("{value}", "{0}"); wrapper.apply(applySql, "1990-10-01");

3.3 常见函数对照表

下表列出了一些常用函数在不同数据库中的等价实现：

4. 高级技巧与性能优化

掌握了apply的基础用法后，我们来看一些高级技巧和性能优化的方法。

4.1 动态条件构建

apply可以与MyBatis-Plus的其他条件构造方法组合使用，实现复杂的动态查询。

public List<User> searchUsers(UserQuery query) { QueryWrapper<User> wrapper = new QueryWrapper<>(); if (StringUtils.isNotBlank(query.getName())) { wrapper.like("name", query.getName()); } if (query.getMinAge() != null) { wrapper.ge("age", query.getMinAge()); } if (query.getBirthMonth() != null) { wrapper.apply("month(birthday) = {0}", query.getBirthMonth()); } if (query.getTags() != null && !query.getTags().isEmpty()) { wrapper.apply("JSON_CONTAINS(tags, {0})", "\"" + String.join("\" OR JSON_CONTAINS(tags, \"", query.getTags()) + "\""); } return userMapper.selectList(wrapper); }

4.2 索引友好写法

使用apply时要注意确保SQL能够利用索引。一些常见的优化技巧：

避免对索引列使用函数：

// 不推荐 - 无法使用birthday上的索引 wrapper.apply("date_format(birthday,'%Y-%m-%d') = {0}", "1990-10-01"); // 推荐写法 - 可以使用索引 wrapper.between("birthday", "1990-10-01 00:00:00", "1990-10-01 23:59:59");

使用函数索引支持的写法：

// 如果为month(birthday)创建了函数索引 wrapper.apply("month(birthday) = {0}", 10);

4.3 批量操作中的apply

apply也可以用在批量更新和删除操作中：

// 批量更新上个月注册的用户的VIP状态 UpdateWrapper<User> updateWrapper = new UpdateWrapper<>(); updateWrapper.apply("date_format(create_time,'%Y-%m') = date_format(date_sub(curdate(), interval 1 month),'%Y-%m')") .set("vip", true); userMapper.update(null, updateWrapper);

4.4 与自定义SQL结合

对于特别复杂的查询，可以将apply与MyBatis的自定义SQL结合使用：

@Select("SELECT * FROM user ${ew.customSqlSegment}") List<User> selectComplexUsers(@Param(Constants.WRAPPER) QueryWrapper<User> wrapper); // 调用方式 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.apply("JSON_CONTAINS(profile, {0})", "{\"premium\":true}") .apply("date_format(create_time,'%Y-%m') = {0}", "2023-10"); List<User> users = userMapper.selectComplexUsers(wrapper);

5. 实战案例：用户画像查询系统

让我们通过一个完整的实战案例来展示apply的强大能力。假设我们需要开发一个用户画像查询系统，支持以下查询条件：

1. 按年龄段筛选
2. 按兴趣标签筛选
3. 按活跃时间段筛选
4. 按消费水平筛选

5.1 数据模型准备

假设用户表结构如下：

CREATE TABLE `user` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, `birthday` date DEFAULT NULL, `profile` json DEFAULT NULL COMMENT '用户画像JSON', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) );

profile字段存储用户画像信息，格式示例：

{ "age": 28, "interests": ["sports", "music"], "active_hours": [9, 10, 11, 12, 13, 14], "consumption_level": "high" }

5.2 查询服务实现

public List<User> queryUserProfiles(UserProfileQuery query) { QueryWrapper<User> wrapper = new QueryWrapper<>(); // 按年龄段筛选 if (query.getMinAge() != null || query.getMaxAge() != null) { if (query.getMinAge() != null) { wrapper.apply("JSON_EXTRACT(profile, '$.age') >= {0}", query.getMinAge()); } if (query.getMaxAge() != null) { wrapper.apply("JSON_EXTRACT(profile, '$.age') <= {0}", query.getMaxAge()); } } // 按兴趣标签筛选 if (query.getInterests() != null && !query.getInterests().isEmpty()) { String interestsCondition = query.getInterests().stream() .map(interest -> "JSON_CONTAINS(profile, {0}, '$.interests')") .collect(Collectors.joining(" OR ")); Object[] params = query.getInterests().stream() .map(interest -> "\"" + interest + "\"") .toArray(); wrapper.and(w -> w.apply(interestsCondition, params)); } // 按活跃时间段筛选 if (query.getActiveHour() != null) { wrapper.apply("JSON_CONTAINS(profile, {0}, '$.active_hours')", query.getActiveHour()); } // 按消费水平筛选 if (StringUtils.isNotBlank(query.getConsumptionLevel())) { wrapper.apply("JSON_EXTRACT(profile, '$.consumption_level') = {0}", query.getConsumptionLevel()); } return userMapper.selectList(wrapper); }

5.3 多数据库兼容实现

为了使上述代码支持多种数据库，我们可以引入SQL模板：

public class SqlTemplates { private DbType dbType; private Map<String, String> templates; public String getJsonExtract(String path) { switch (dbType) { case MYSQL: return "JSON_EXTRACT(profile, '" + path + "')"; case POSTGRE_SQL: return "profile::json->>'" + path.replace("$.", "") + "'"; case ORACLE: return "JSON_VALUE(profile, '" + path + "')"; default: throw new UnsupportedOperationException("Unsupported database type"); } } public String getJsonContains(String path) { switch (dbType) { case MYSQL: return "JSON_CONTAINS(profile, {0}, '" + path + "')"; case POSTGRE_SQL: return "{0}::jsonb <@ profile::jsonb->'" + path.replace("$.", "") + "'"; case ORACLE: return "JSON_EXISTS(profile, '" + path + "?(@ == {0})')"; default: throw new UnsupportedOperationException("Unsupported database type"); } } }

然后在查询服务中使用：

// 替换原来的JSON_EXTRACT调用 wrapper.apply(sqlTemplates.getJsonExtract("$.age") + " >= {0}", query.getMinAge()); // 替换原来的JSON_CONTAINS调用 wrapper.apply(sqlTemplates.getJsonContains("$.interests"), "\"" + interest + "\"");

在实际项目中，我发现最棘手的不是编写apply语句本身，而是确保这些语句在不同数据库上都能正常工作。特别是在处理JSON数据时，各数据库的实现差异很大。一个好的做法是尽早建立数据库兼容性测试套件，确保所有apply语句在目标数据库上都能正确执行。