1. 从“自动挡”到“手动挡”为什么需要自定义SQL在Java后端开发里Mybatis-Plus简称MP几乎成了操作数据库的“标配”。它那套强大的Wrapper条件构造器配合上IService接口里琳琅满目的save、update、list、page方法让我们处理单表的CRUD增删改查时体验堪比开自动挡汽车——踩油门就走省心省力。我见过不少项目靠着MP的自动生成和条件拼接就能完成80%以上的数据操作开发效率确实高。但就像自动挡车遇到复杂路况或追求极致操控时老司机还是会切到手动模式一样我们在数据库操作中也总会遇到那些MP的“自动挡”覆盖不了的场景。这时候自定义SQL就成了我们必须掌握的“手动挡”技能。我最初接触自定义SQL就是因为一个多表关联的复杂统计需求。MP的Wrapper再强大面对那种需要嵌套子查询、多重JOIN、甚至用到数据库特有函数比如GROUP_CONCAT、窗口函数的SQL时就显得力不从心了。强行用Java代码在内存里拼凑和计算不仅代码臃肿性能更是灾难。所以自定义SQL的核心价值就在于填补MP自动化能力的“空白区”让我们在享受MP便捷的同时又不失对复杂查询的掌控力。它不是为了替代MP而是作为其能力的重要补充。接下来我会结合几种最常见的场景带你看看具体哪些情况我们必须请出“手动挡”。2. 自定义SQL的四大典型应用场景2.1 场景一复杂多表关联查询这是自定义SQL最经典的应用场景。比如你需要查询订单信息同时关联出用户详情、商品列表每个订单可能对应多个商品并且还要按商品类目进行筛选。这种“一对多再对多”的关联用MP的TableField注解做简单的一对一或一对多映射还行但复杂的多对多和多重筛选写起来会非常别扭。一个反例我曾见过有同事试图用MP的QueryWrapper在主表查询后再循环调用其他Service的list方法去填充数据。代码里充满了循环和临时集合一次列表查询可能触发几十次甚至上百次数据库请求N1问题页面打开慢得像蜗牛。正确的做法就是写一条清晰的LEFT JOIN ... ON ...SQL一次查询将所有关联数据按需组装好。2.2 场景二使用数据库特定函数或语法不同的数据库MySQL, PostgreSQL, Oracle等都有自己特有的函数和语法糖。比如MySQL的DATE_FORMAT()进行日期格式化、FIND_IN_SET()处理逗号分隔的字符串PostgreSQL的JSONB类型操作符或者像WITH RECURSIVE这样的递归查询语法。MP的通用API为了保持兼容性无法直接生成这些数据库特有的语句。这时自定义SQL是唯一的选择。2.3 场景三高性能的批量更新或删除虽然MP提供了updateBatchById这样的批量方法但其底层通常是遍历集合逐条生成UPDATE语句执行取决于配置。对于需要根据复杂条件更新海量数据的场景比如“将过去30天未登录的用户的status字段改为‘冻结’”一条基于WHERE条件的UPDATE语句在性能上远超在Java中分批调用。同理基于复杂条件的批量删除也是如此。这种操作必须通过自定义SQL来实现。2.4 场景四复杂的统计报表与聚合计算做报表时我们经常需要写包含GROUP BY多个字段、HAVING过滤、SUM/COUNT/AVG聚合甚至多层嵌套子查询的SQL。MP的groupBy和聚合方法虽然能处理简单分组但对于字段别名、多层聚合、子查询别名引用等复杂情况其链式调用的代码可读性会急剧下降远不如一条精心编排的原生SQL直观和易于维护。明确了为什么需要以及何时需要之后我们来看看在Mybatis-Plus的体系下具体有哪几种“手动挡”的挂挡方式。3. 核心方法一在Mapper接口中直接使用Select/Update等注解这是最直接、最轻量的一种方式适合SQL语句相对固定、不太复杂的场景。Mybatis-Plus完全兼容MyBatis的原生注解。具体做法 在你的XxxMapper接口需继承MP的BaseMapper中直接定义方法并使用Select、Update、Insert、Delete注解来编写SQL。public interface UserMapper extends BaseMapperUser { // 场景查询某个部门下所有用户并关联查询部门名称 Select(SELECT u.*, d.name as dept_name FROM user u LEFT JOIN department d ON u.dept_id d.id WHERE u.dept_id #{deptId}) ListUserDeptVO selectUsersWithDept(Param(deptId) Long deptId); // 场景使用数据库函数进行复杂查询 Select(SELECT * FROM user WHERE DATE_FORMAT(create_time, %Y-%m) #{month}) ListUser selectUsersByCreateMonth(Param(month) String month); // 场景执行批量更新例如根据条件激活用户 Update(UPDATE user SET status 1 WHERE last_login_time #{thresholdDate} AND status 0) int activateInactiveUsers(Param(thresholdDate) Date thresholdDate); }关键点与避坑经验参数绑定务必使用Param注解明确指定参数名并在SQL中使用#{参数名}进行引用。这是防止参数绑定错误的最基本也最重要的习惯。结果映射如果查询返回的字段与实体类User不完全对应比如上面多了个dept_name你需要定义一个结果对象UserDeptVO或者DTO包含所有返回字段。或者使用MyBatis的Results注解进行手动映射稍显繁琐不推荐复杂场景使用。SQL注入注解中的SQL是静态字符串使用#{}语法是安全的预编译方式可以防止SQL注入。绝对不要用字符串拼接‘${}’来传递条件值除非你非常清楚它在做字符串替换而非预编译且参数绝对安全。可维护性当SQL很长时写在注解里会破坏代码格式可读性变差。可以考虑使用script标签虽然写在注解里有点怪或者当SQL非常复杂时转向我们后面要讲的XML方式。注意Select注解等返回的是ListT如果你需要MP的IPage分页对象单纯用注解是不够的。虽然可以手动计算LIMIT但会失去MP分页插件自动处理总数等便利。这时需要结合分页插件和XML或者使用Select注解的方法其参数接受一个IPage对象但SQL里需要包含${ew.customSqlSegment}不推荐易出错。更规范的分页做法在XML部分讲解。4. 核心方法二结合XML映射文件处理复杂动态SQL当SQL非常复杂或者需要动态拼接条件即IF、WHERE、FOREACH等标签时将SQL写在独立的XML映射文件中是行业内的最佳实践。这种方式分离了SQL和Java代码结构清晰尤其利于维护长而复杂的SQL语句。第一步创建XML文件并配置路径在resources目录下创建与Mapper接口包名相同的目录结构。例如接口com.example.mapper.UserMapper则XML文件应放在resources/com/example/mapper/UserMapper.xml。在application.yml中确保MyBatis的XML映射路径被正确扫描mybatis-plus: mapper-locations: classpath*:/mapper/**/*.xml第二步编写XML映射文件?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.mapper.UserMapper !-- 通用查询结果映射可复用 -- resultMap idUserDeptResultMap typecom.example.vo.UserDeptVO id propertyid columnid/ result propertyusername columnusername/ !-- ... 映射其他User字段 ... -- result propertydeptName columndept_name/ /resultMap !-- 场景动态多条件分页查询用户列表 -- select idselectUserPage resultTypecom.example.entity.User SELECT * FROM user where if testquery.username ! null and query.username ! AND username LIKE CONCAT(%, #{query.username}, %) /if if testquery.status ! null AND status #{query.status} /if if testquery.deptId ! null AND dept_id #{query.deptId} /if if testquery.createTimeStart ! null AND create_time #{query.createTimeStart} /if if testquery.createTimeEnd ! null AND create_time #{query.createTimeEnd} /if /where ORDER BY create_time DESC /select !-- 场景使用foreach进行批量插入 (MySQL语法示例) -- insert idbatchInsert parameterTypejava.util.List INSERT INTO user (username, email, dept_id) VALUES foreach collectionlist itemitem separator, (#{item.username}, #{item.email}, #{item.deptId}) /foreach /insert !-- 场景复杂的统计报表SQL -- select idselectDeptUserStats resultTypecom.example.vo.DeptStatVO SELECT d.id as deptId, d.name as deptName, COUNT(u.id) as userCount, SUM(CASE WHEN u.status 1 THEN 1 ELSE 0 END) as activeUserCount, AVG(u.some_score) as avgScore FROM department d LEFT JOIN user u ON d.id u.dept_id WHERE d.parent_id #{rootDeptId} GROUP BY d.id, d.name HAVING COUNT(u.id) #{minUserCount} ORDER BY userCount DESC /select /mapper第三步在Mapper接口中定义对应方法public interface UserMapper extends BaseMapperUser { // 对应XML中的 selectUserPage IPageUser selectUserPage(IPageUser page, Param(query) UserQueryDTO query); // 对应XML中的 batchInsert int batchInsert(Param(list) ListUser userList); // 对应XML中的 selectDeptUserStats ListDeptStatVO selectDeptUserStats(Param(rootDeptId) Long rootDeptId, Param(minUserCount) Integer minUserCount); }关键点与避坑经验namespace必须对应XML文件顶部的namespace属性值必须是Mapper接口的全限定名包名类名一个字符都不能错这是MyBatis将它们关联起来的唯一依据。分页插件的无缝集成这是XML方式的一大优势。如上例selectUserPage方法参数中直接传入IPage对象Mybatis-Plus的分页插件PaginationInnerInterceptor会自动工作。它会在执行查询时自动生成并执行一条COUNT(*)语句获取总数同时为原SQL加上LIMIT分页子句。你完全不用在XML里写LIMIT插件帮你搞定。返回的也是包含分页信息总条数、每页大小、当前页数据列表的IPage对象。动态SQL标签where、if、foreach、choose、set等标签是MyBatis的核心功能它们能根据传入参数动态生成SQL片段完美解决条件不确定的问题。注意where标签会智能地处理AND/OR开头的问题。参数传递在XML中通过Param注解定义的参数名如query来引用。对于对象中的属性使用#{query.username}这样的点号语法。结果映射resultType直接指定返回的实体类或VO类。对于字段名和属性名不一致的情况可以使用resultMap进行详细映射如上例中的UserDeptResultMap。5. 核心方法三使用Wrapper条件构造器拼接自定义SQL片段这是Mybatis-Plus提供的一种“混合动力”模式。它允许你在自定义SQL的WHERE部分仍然使用MP强大的Wrapper来构造条件从而将自定义的SELECT ... FROM ...部分与动态条件生成部分解耦兼具灵活与便捷。使用场景当你需要自定义SELECT的字段或者FROM的表比如复杂的JOIN但WHERE条件又是动态的、且希望用MP的Wrapper来优雅构造时。具体做法 在XML文件中在WHERE位置使用${ew.customSqlSegment}来嵌入Wrapper生成的条件。!-- UserMapper.xml -- select idselectCustomWithWrapper resultTypecom.example.vo.UserDetailVO SELECT u.id, u.username, d.name as department_name, r.role_name FROM user u LEFT JOIN department d ON u.dept_id d.id LEFT JOIN user_role ur ON u.id ur.user_id LEFT JOIN role r ON ur.role_id r.id ${ew.customSqlSegment} !-- 关键此处注入Wrapper生成的条件 -- /select在Mapper接口和Service中的调用// Mapper接口 public interface UserMapper extends BaseMapperUser { ListUserDetailVO selectCustomWithWrapper(Param(ew) WrapperUser wrapper); } // Service或Controller中使用 Service public class UserServiceImpl extends ServiceImplUserMapper, User implements UserService { public ListUserDetailVO getUsersByComplexCondition(UserQueryDTO query) { // 1. 构建QueryWrapper QueryWrapperUser wrapper new QueryWrapper(); wrapper.like(StringUtils.isNotBlank(query.getUsername()), u.username, query.getUsername()) .eq(query.getStatus() ! null, u.status, query.getStatus()) .ge(query.getStartTime() ! null, u.create_time, query.getStartTime()) .le(query.getEndTime() ! null, u.create_time, query.getEndTime()) .orderByDesc(u.create_time); // 2. 注意Wrapper中的列名需要与自定义SQL中的表别名对应 // 这里用的是 u.username u.status 而不是username // 3. 调用自定义方法传入wrapper return this.baseMapper.selectCustomWithWrapper(wrapper); } }关键点与避坑经验表别名一致性这是最容易踩的坑在自定义SQL的SELECT和FROM部分你给表起了别名如u、d。那么在构造QueryWrapper时所有涉及列名的条件eq,like,ge等其列名参数必须带上相同的表别名前缀如u.username。否则SQL会报错“列名不明确”或找不到列。${ew.customSqlSegment}的含义ew是Param(ew)定义的参数名customSqlSegment是Wrapper对象的一个属性它包含了Wrapper生成的WHERE之后的SQL片段不包括WHERE关键字本身。使用${}进行字符串替换这意味着它是直接拼接进SQL的因此要确保Wrapper的构造是安全的避免SQL注入。只要不将用户输入直接用于列名等结构部分通过Wrapper的eq、like等方法设置的值都是预编译安全的。排序和分页你可以在Wrapper中使用orderByAsc/orderByDesc来添加排序它也会被包含在customSqlSegment中。对于分页你依然可以像XML方式一样在Mapper方法参数中添加IPage对象分页插件会正常生效。灵活性这种方法非常适合后台管理系统中那些“过滤条件复杂但查询主体固定”的列表页。前端传回各种过滤参数后端用Wrapper轻松构建条件而查询主体包含哪些字段、关联哪些表则在XML中一次定义好。6. 实战演练一个完整的多表分页查询案例假设我们有一个博客系统需要实现一个后台文章管理列表需求如下查询文章列表需关联显示文章分类名称和作者昵称。支持根据文章标题模糊、分类ID、发布状态、发布时间范围进行筛选。结果需要分页并按发布时间倒序排列。步骤1定义查询参数DTO、返回结果VO// 查询参数 Data public class ArticleQueryDTO { private String title; private Long categoryId; private Integer status; // 0-草稿1-已发布 private LocalDateTime publishTimeStart; private LocalDateTime publishTimeEnd; } // 返回结果视图对象 Data public class ArticleAdminVO { private Long id; private String title; private String summary; private Integer status; private LocalDateTime publishTime; // 关联字段 private String categoryName; private String authorNickname; }步骤2在ArticleMapper接口中定义方法public interface ArticleMapper extends BaseMapperArticle { /** * 后台文章分页查询 * param page 分页参数由分页插件自动处理 * param query 查询条件 * return 分页结果 */ IPageArticleAdminVO selectAdminArticlePage(IPageArticle page, Param(query) ArticleQueryDTO query); }步骤3编写ArticleMapper.xml?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.blog.mapper.ArticleMapper select idselectAdminArticlePage resultTypecom.example.blog.vo.ArticleAdminVO SELECT a.id, a.title, a.summary, a.status, a.publish_time, c.name as category_name, u.nickname as author_nickname FROM article a LEFT JOIN category c ON a.category_id c.id LEFT JOIN user u ON a.author_id u.id where if testquery.title ! null and query.title ! AND a.title LIKE CONCAT(%, #{query.title}, %) /if if testquery.categoryId ! null AND a.category_id #{query.categoryId} /if if testquery.status ! null AND a.status #{query.status} /if if testquery.publishTimeStart ! null AND a.publish_time #{query.publishTimeStart} /if if testquery.publishTimeEnd ! null AND a.publish_time #{query.publishTimeEnd} /if /where ORDER BY a.publish_time DESC /select /mapper步骤4在Service中调用Service public class ArticleAdminServiceImpl extends ServiceImplArticleMapper, Article implements ArticleAdminService { Override public PageResultArticleAdminVO getAdminArticlePage(ArticleQueryDTO queryDTO, PageParam pageParam) { // 1. 构建Mybatis-Plus的分页对象 PageArticle page new Page(pageParam.getPageNum(), pageParam.getPageSize()); // 2. 调用自定义的Mapper方法 IPageArticleAdminVO resultPage this.baseMapper.selectAdminArticlePage(page, queryDTO); // 3. 将IPage转换为自定义的PageResult可选根据项目规范 PageResultArticleAdminVO pageResult new PageResult(); pageResult.setList(resultPage.getRecords()); pageResult.setTotal(resultPage.getTotal()); pageResult.setPageNum((int) resultPage.getCurrent()); pageResult.setPageSize((int) resultPage.getSize()); pageResult.setPages((int) resultPage.getPages()); return pageResult; } }案例要点分析分页在Service层创建Page对象并传入Mapper方法返回IPageVO。分页插件自动拦截执行两条SQLCOUNT(*)和 添加了LIMIT的原SQL。我们无需手动计算分页参数。动态WHEREXML中的where和if标签根据queryDTO中字段是否为null来动态拼接条件完美支持前端可选过滤。结果映射resultTypeArticleAdminVOMyBatis会自动将查询结果的列名通过AS别名指定如category_name映射到VO对象的属性上categoryName遵循下划线转驼峰的默认映射规则需在配置中开启map-underscore-to-camel-case: true。性能一条SQL完成多表关联和过滤避免了N1查询问题效率远高于在Java中循环查询关联数据。7. 高级技巧与深度避坑指南掌握了基本方法后在实际项目中还会遇到一些更细致的问题。这里分享几个我踩过坑才总结出来的高级技巧和注意事项。7.1 如何优雅地返回Map或自定义DTO/VO有时我们不需要完整的实体对象只需要几个字段。除了定义VO还可以直接返回MapString, Object或ListMap。在Mapper接口中Select(SELECT id, username FROM user WHERE status #{status}) ListMapString, Object selectSimpleUserMap(Param(status) Integer status);这种方式非常灵活但牺牲了类型安全。在XML中使用resultTypejava.util.Map即可。更推荐的做法对于固定的字段组合显式地定义DTO或VO类。这保证了类型安全、良好的代码提示和可维护性。不要因为偷懒而滥用Map。7.2 使用script标签处理“动态SELECT字段”或“动态表名”极少数情况下我们可能需要动态决定查询哪些字段或者根据参数切换查询的表。这可以在XML中使用script标签内嵌逻辑实现。select idselectDynamic resultTypecom.example.entity.User SELECT choose when testfields ! null and fields.size() 0 foreach collectionfields itemfield separator, ${field} !-- 注意这里是${}因为field是列名/SQL片段 -- /foreach /when otherwise * /otherwise /choose FROM user WHERE id #{id} /select警告${field}是直接的字符串替换存在SQL注入风险必须确保fields集合内的值如username,email来自可信的代码逻辑而非用户直接输入。动态表名同理需极度谨慎。7.3 分页插件与自定义SQL的“坑”与“解”坑1自定义COUNT语句当你的自定义SQL非常复杂例如带有大量GROUP BY或DISTINCTMP分页插件自动生成的COUNT(*)语句可能会很慢甚至语义错误。这时你需要为这个查询单独指定一个COUNT语句。解决方案在XML中为同一个查询ID定义一个后缀为_COUNT的查询。select idselectComplexReport resultType... SELECT ... FROM ... [复杂的JOIN和GROUP BY] /select select idselectComplexReport_COUNT resultTypejava.lang.Long SELECT COUNT(DISTINCT main.id) FROM (...) main !-- 一个更高效的COUNT写法 -- /select分页插件会优先寻找{你的方法名}_COUNT的语句来执行计数。坑2ORDER BY在分页时丢失在某些数据库如某些版本的MySQL和复杂SQL下分页插件自动添加的LIMIT可能会与你的ORDER BY子句产生冲突导致排序失效。确保你的ORDER BY子句在SQL中是明确的并且是最后的部分。7.4 事务管理自定义更新/删除操作对于在Mapper中使用Update、Delete或XML中定义的INSERT/UPDATE/DELETE语句默认情况下它们会参与Spring管理的事务。如果你的Service方法上标注了Transactional那么这些自定义的DML操作也会在同一个事务中。重要实践确保你的自定义更新操作是幂等的特别是在重试或并发场景下。对于复杂的批量更新建议在Service层方法上添加Transactional(rollbackFor Exception.class)以保证数据一致性。7.5 性能考量大数据量下的自定义查询避免SELECT *在自定义SQL中尤其是关联查询务必只SELECT你需要的字段。这能显著减少网络传输和结果集映射的开销。善用索引自定义SQL让你对最终执行的SQL有完全的控制权也意味着你需要自己考虑查询性能。确保WHERE、JOIN ON、ORDER BY子句中的字段有合适的索引。分批处理对于需要处理大量数据的自定义更新或查询考虑在Service层进行分批操作避免单次操作耗时过长或占用过多内存。自定义SQL是Mybatis-Plus这把利器开刃的关键。它让我们从MP的“舒适区”走向更广阔的数据库操作领域处理那些自动化框架无法覆盖的复杂场景。核心在于理解三种主要方式的应用边界简单固定查询用注解复杂动态SQL用XML想复用Wrapper条件就用${ew.customSqlSegment}混合模式。记住能力越大责任越大直接编写SQL意味着你需要对性能和安全SQL注入有更高的意识。多写、多调试、多查看实际执行的SQL你就能在这“手动挡”的模式下游刃有余真正掌控数据层的每一行代码。