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() { QueryWrapperUser wrapper new QueryWrapper(); wrapper.apply(date_format(birthday,%Y-%m-%d) {0}, 1990-10-01); ListUser users userMapper.selectList(wrapper); users.forEach(System.out::println); }这段代码会生成如下SQLSELECT 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 常见函数对照表下表列出了一些常用函数在不同数据库中的等价实现功能描述MySQLPostgreSQLOracle当前日期CURDATE()CURRENT_DATETRUNC(SYSDATE)日期格式化DATE_FORMAT(d, f)TO_CHAR(d, f)TO_CHAR(d, f)提取月份MONTH(d)EXTRACT(MONTH FROM d)EXTRACT(MONTH FROM d)JSON提取JSON_EXTRACT(d, p)d::json-pJSON_VALUE(d, p)字符串截取SUBSTRING(s, p, l)SUBSTRING(s, p, l)SUBSTR(s, p, l)4. 高级技巧与性能优化掌握了apply的基础用法后我们来看一些高级技巧和性能优化的方法。4.1 动态条件构建apply可以与MyBatis-Plus的其他条件构造方法组合使用实现复杂的动态查询。public ListUser searchUsers(UserQuery query) { QueryWrapperUser 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 批量操作中的applyapply也可以用在批量更新和删除操作中// 批量更新上个月注册的用户的VIP状态 UpdateWrapperUser 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}) ListUser selectComplexUsers(Param(Constants.WRAPPER) QueryWrapperUser wrapper); // 调用方式 QueryWrapperUser wrapper new QueryWrapper(); wrapper.apply(JSON_CONTAINS(profile, {0}), {\premium\:true}) .apply(date_format(create_time,%Y-%m) {0}, 2023-10); ListUser users userMapper.selectComplexUsers(wrapper);5. 实战案例用户画像查询系统让我们通过一个完整的实战案例来展示apply的强大能力。假设我们需要开发一个用户画像查询系统支持以下查询条件按年龄段筛选按兴趣标签筛选按活跃时间段筛选按消费水平筛选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 ListUser queryUserProfiles(UserProfileQuery query) { QueryWrapperUser 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 MapString, 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语句在目标数据库上都能正确执行。