外卖霸王餐API扩展Java后端基于MyBatis Dynamic SQL实现“非技术人员可配置的试吃规则查询条件”背景霸王餐业务中动态查询的痛点作为外卖霸王餐API唯一供给源头同时也是霸王餐外卖CPS取链源头俱美开放平台的业务逻辑日益复杂。在“霸王餐”或“试吃”活动中运营人员需要根据不同的推广策略灵活配置用户参与资格的筛选条件。例如一个活动可能要求“新用户且来自北京地区”而另一个活动则要求“老用户且历史订单金额大于100元”。如果为每一种组合都编写固定的SQL开发效率极低且难以维护。我们需要一种机制让非技术人员如运营通过后台配置规则后端Java服务就能动态生成并执行相应的查询。技术选型为什么是MyBatis Dynamic SQLMyBatis Dynamic SQL是MyBatis官方提供的一个强大的Java库它允许我们在Java代码中以类型安全的方式构建动态SQL语句。相比于传统的XML中拼接SQL或使用if标签它具有以下优势类型安全在编译期就能发现字段名错误避免了运行时因SQL语法错误导致的崩溃。Java代码构建将SQL构建逻辑完全放在Java代码中更符合现代Java开发习惯也便于调试和复用。功能强大支持复杂的WHERE、JOIN、ORDER BY等子句的动态构建。核心设计规则配置与SQL构建我们的核心思路是将运营配置的规则转化为一个Java对象然后在Service层根据这个对象使用MyBatis Dynamic SQL构建出最终的查询条件。1. 定义规则配置模型首先我们需要一个数据结构来承载运营配置的规则。packagebaodanbao.com.cn.rule.model;importlombok.Data;importjava.util.List;/** * 试吃活动规则配置 * author baodanbao.com.cn */DatapublicclassTrialRuleConfig{/** * 规则ID */privateLongid;/** * 活动名称 */privateStringactivityName;/** * 规则条件列表 */privateListRuleConditionconditions;}/** * 单个规则条件 * author baodanbao.com.cn */DatapublicclassRuleCondition{/** * 字段名如 userLevel, city, totalOrderAmount */privateStringfield;/** * 操作符如 , , , IN */privateStringoperator;/** * 值 */privateObjectvalue;}2. 创建MyBatis Dynamic SQL的表映射使用MyBatis Dynamic SQL我们需要为数据库表创建一个Java类来映射其结构。packagebaodanbao.com.cn.user.mapper;importbaodanbao.com.cn.user.model.User;importorg.mybatis.dynamic.sql.SqlColumn;importorg.mybatis.dynamic.sql.SqlTable;importjava.sql.JDBCType;/** * 用户表映射类由MyBatis Generator生成或手动编写 * author baodanbao.com.cn */publicfinalclassUserDynamicSqlSupport{publicstaticfinalUserTableusernewUserTable();publicstaticfinalSqlColumnLongiduser.id;publicstaticfinalSqlColumnStringuserLeveluser.userLevel;publicstaticfinalSqlColumnStringcityuser.city;publicstaticfinalSqlColumnIntegertotalOrderAmountuser.totalOrderAmount;publicstaticfinalSqlColumnIntegerstatususer.status;publicstaticfinalclassUserTableextendsSqlTable{publicfinalSqlColumnLongidcolumn(id,JDBCType.BIGINT);publicfinalSqlColumnStringuserLevelcolumn(user_level,JDBCType.VARCHAR);publicfinalSqlColumnStringcitycolumn(city,JDBCType.VARCHAR);publicfinalSqlColumnIntegertotalOrderAmountcolumn(total_order_amount,JDBCType.INTEGER);publicfinalSqlColumnIntegerstatuscolumn(status,JDBCType.INTEGER);publicUserTable(){super(t_user);}}}3. 构建动态查询Service这是整个方案的核心。我们将解析TrialRuleConfig并使用SelectStatementProvider构建最终的SQL。packagebaodanbao.com.cn.rule.service;importbaodanbao.com.cn.rule.model.RuleCondition;importbaodanbao.com.cn.rule.model.TrialRuleConfig;importbaodanbao.com.cn.user.mapper.UserDynamicSqlSupport;importbaodanbao.com.cn.user.model.User;importbaodanbao.com.cn.user.mapper.UserMapper;importorg.apache.ibatis.annotations.Mapper;importorg.apache.ibatis.session.SqlSession;importorg.mybatis.dynamic.sql.select.SelectDSL;importorg.mybatis.dynamic.sql.select.SelectModel;importorg.mybatis.dynamic.sql.select.render.SelectStatementProvider;importorg.mybatis.dynamic.sql.util.SqlProviderAdapter;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importorg.springframework.util.CollectionUtils;importjava.util.ArrayList;importjava.util.List;importstaticbaodanbao.com.cn.user.mapper.UserDynamicSqlSupport.*;importstaticorg.mybatis.dynamic.sql.SqlBuilder.*;/** * 试吃规则查询服务 * author baodanbao.com.cn */ServicepublicclassTrialRuleQueryService{AutowiredprivateSqlSessionsqlSession;/** * 根据规则配置查询符合条件的用户 * param ruleConfig 运营配置的规则 * return 符合条件的用户列表 */publicListUserfindUsersByRule(TrialRuleConfigruleConfig){if(ruleConfignull||CollectionUtils.isEmpty(ruleConfig.getConditions())){returnnewArrayList();}// 1. 构建动态WHERE条件SelectDSLSelectModel.SelectWhereBuilder whereBuilderselect(UserDynamicSqlSupport.user.allColumns()).from(UserDynamicSqlSupport.user).where();booleanfirstConditiontrue;for(RuleConditioncondition:ruleConfig.getConditions()){if(!firstCondition){whereBuilder.and();}applyCondition(whereBuilder,condition);firstConditionfalse;}// 2. 渲染SQL语句SelectStatementProviderselectStatementwhereBuilder.build().render(RenderingStrategies.MYBATIS3);// 3. 执行查询SqlProviderAdaptersqlProviderAdapternewSqlProviderAdapter();returnsqlSession.selectList(org.mybatis.dynamic.sql.util.MyBatis3Utils.selectList,selectStatement,User.class);}/** * 根据单个条件应用WHERE子句 * param whereBuilder WHERE构建器 * param condition 规则条件 */privatevoidapplyCondition(SelectDSLSelectModel.SelectWhereBuilder whereBuilder,RuleConditioncondition){Stringfieldcondition.getField();Stringoperatorcondition.getOperator();Objectvaluecondition.getValue();switch(field){caseuserLevel:applyStringCondition(whereBuilder,userLevel,operator,(String)value);break;casecity:applyStringCondition(whereBuilder,city,operator,(String)value);break;casetotalOrderAmount:applyNumberCondition(whereBuilder,totalOrderAmount,operator,(Integer)value);break;casestatus:applyNumberCondition(whereBuilder,status,operator,(Integer)value);break;default:thrownewIllegalArgumentException(不支持的查询字段: field);}}privatevoidapplyStringCondition(SelectDSLSelectModel.SelectWhereBuilder whereBuilder,SqlColumnStringcolumn,Stringoperator,Stringvalue){switch(operator){case:whereBuilder.where(column,isEqualTo(value));break;case!:whereBuilder.where(column,isNotEqualTo(value));break;caseIN:// 假设value是逗号分隔的字符串String[]valuesvalue.split(,);whereBuilder.where(column,isIn(values));break;default:thrownewIllegalArgumentException(不支持的字符串操作符: operator);}}privatevoidapplyNumberCondition(SelectDSLSelectModel.SelectWhereBuilder whereBuilder,SqlColumnIntegercolumn,Stringoperator,Integervalue){switch(operator){case:whereBuilder.where(column,isEqualTo(value));break;case:whereBuilder.where(column,isGreaterThan(value));break;case:whereBuilder.where(column,isGreaterThanOrEqualTo(value));break;case:whereBuilder.where(column,isLessThan(value));break;case:whereBuilder.where(column,isLessThanOrEqualTo(value));break;default:thrownewIllegalArgumentException(不支持的数字操作符: operator);}}}使用示例假设运营在后台配置了一条规则用户等级为NEW 且 城市在(北京, 上海)。packagebaodanbao.com.cn.rule.controller;importbaodanbao.com.cn.rule.model.RuleCondition;importbaodanbao.com.cn.rule.model.TrialRuleConfig;importbaodanbao.com.cn.rule.service.TrialRuleQueryService;importbaodanbao.com.cn.user.model.User;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RestController;importjava.util.Arrays;importjava.util.List;/** * 规则查询控制器 * author baodanbao.com.cn */RestControllerRequestMapping(/api/rule)publicclassTrialRuleController{AutowiredprivateTrialRuleQueryServicetrialRuleQueryService;GetMapping(/test)publicListUsertestQuery(){// 模拟从数据库或配置中心加载的规则TrialRuleConfigconfignewTrialRuleConfig();RuleConditioncondition1newRuleCondition();condition1.setField(userLevel);condition1.setOperator();condition1.setValue(NEW);RuleConditioncondition2newRuleCondition();condition2.setField(city);condition2.setOperator(IN);condition2.setValue(北京,上海);config.setConditions(Arrays.asList(condition1,condition2));// 执行查询returntrialRuleQueryService.findUsersByRule(config);}}当调用/api/rule/test接口时后端将动态生成并执行类似SELECT * FROM t_user WHERE user_level NEW AND city IN (北京, 上海)的SQL语句完美实现了非技术人员可配置的动态查询需求。本文著作权归 俱美开放平台 转载请注明出处