MyBatis-Plus 原生 SQL 混合使用

在保留 MyBatis-Plus 便捷性的同时，处理复杂 SQL 场景的终极解决方案。

一、核心概念

1. 原生SQL混合模式
   将 MP 的条件构造器、分页插件等与自定义 SQL（XML/注解）结合使用
2. 混合使用场景
   • 复杂多表 JOIN 查询
   • 特殊数据库函数/语法
   • 存储过程调用
   • 大数据量特殊优化
3. 核心接口
   • @Param(Constants.WRAPPER)：注入条件构造器
   • ew.customSqlSegment：获取 Wrapper 生成的 SQL 片段

二、详细操作步骤

1. 基础配置（确保 XML 扫描）

# application.yml
mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml

2. Mapper 接口定义

public interface UserMapper extends BaseMapper<User> {
    
    // 1. 注解方式混合SQL
    @Select("SELECT u.*, d.name AS dept_name " +
            "FROM user u " +
            "LEFT JOIN department d ON u.dept_id = d.id " +
            "${ew.customSqlSegment}")
    List<UserVO> selectUserWithDept(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
    
    // 2. XML引用方式
    List<User> selectComplexQuery(@Param(Constants.WRAPPER) Wrapper<User> wrapper,
                                 @Param("minSalary") BigDecimal minSalary);
}

3. XML 映射文件配置

<!-- resources/mapper/UserMapper.xml -->
<select id="selectComplexQuery" resultType="com.example.entity.User">
    SELECT * FROM user
    <!-- 插入Wrapper生成的WHERE条件 -->
    ${ew.customSqlSegment}
    <!-- 追加额外条件 -->
    AND salary >= #{minSalary}
    <!-- 特殊排序 -->
    ORDER BY 
        CASE WHEN status = 1 THEN 0 ELSE 1 END,
        create_time DESC
</select>

4. Service 层调用

public List<UserVO> findUsers(String name, Integer deptId) {
    LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
    wrapper.like(StringUtils.isNotBlank(name), User::getName, name)
           .eq(deptId != null, User::getDeptId, deptId)
           .apply("DATE_FORMAT(create_time,'%Y%m') = {0}", "202405");
    
    return userMapper.selectUserWithDept(wrapper);
}

public Page<User> pageUsers(Page<User> page, BigDecimal minSalary) {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.select("id", "name", "MAX(salary) AS salary")
           .groupBy("id", "name");
    
    return userMapper.selectComplexQuery(page, wrapper, minSalary);
}

5. 分页特殊处理

// Mapper接口
IPage<UserVO> selectPageWithJoin(IPage<User> page, 
                                @Param(Constants.WRAPPER) Wrapper<User> wrapper);

// XML配置
<select id="selectPageWithJoin" resultType="com.example.vo.UserVO">
    SELECT u.id, u.name, d.name AS dept_name 
    FROM user u
    JOIN department d ON u.dept_id = d.id
    ${ew.customSqlSegment}
</select>

三、常见错误与解决

1. SQL注入风险

   • 错误：直接拼接 ${param}
   • 解决：使用 #{param} 预编译占位符，必须用 ${} 时进行过滤：

     wrapper.apply("column = {0}", safeParam) // 安全
     wrapper.apply("column = '" + safeParam + "'") // 危险!
     

2. 分页总数不准

   • 错误：多表JOIN时分页COUNT语句错误
   • 解决：自定义COUNT查询：

     <select id="selectPageWithJoin_count" resultType="java.lang.Long">
         SELECT COUNT(1) FROM user u
         ${ew.customSqlSegment}
     </select>
     

3. Wrapper条件失效

   • 错误：XML中忘记添加 ${ew.customSqlSegment}
   • 解决：检查SQL拼接位置

4. 参数类型不匹配

   • 错误：@Param 名称与XML中不一致
   • 解决：保持参数命名一致

四、注意事项

1. XML 优先级规则

   • 当 Mapper 方法有对应的 XML 时，XML 实现优先于注解
   • 避免方法名冲突

2. Wrapper 使用限制

   • 仅能生成 WHERE 条件部分（不能生成 SELECT/JOIN）
   • 复杂 JOIN 需手动编写

3. 分页插件拦截范围

   • 只拦截 BaseMapper 的 selectPage() 方法
   • 自定义分页需在 XML 中声明 IPage 参数

4. 事务边界控制
   混合操作建议在 Service 层添加 @Transactional

五、使用技巧

1. 动态表名+Wrapper

   <select id="selectByMonth" resultType="User">
     SELECT * FROM ${tableName}
     ${ew.customSqlSegment}
   </select>
   
   // Java调用
   wrapper.eq("status", 1).orderByDesc("create_time");
   userMapper.selectByMonth("user_202405", wrapper);
   

2. 存储过程调用

   @Select("{CALL sp_get_user_data(#{id, mode=IN}, #{result, mode=OUT, jdbcType=CURSOR})}")
   @Options(statementType = StatementType.CALLABLE)
   void callUserProcedure(@Param("id") Long id, @Param("result") ResultHandler<User> handler);
   

3. UNION 查询优化

   <select id="selectUnionData" resultType="User">
     SELECT * FROM (
       (SELECT id, name FROM user_active ${ew.customSqlSegment})
       UNION ALL
       (SELECT id, name FROM user_archive WHERE flag = 1)
     ) t ORDER BY t.id DESC
   </select>
   

4. 批量操作混合

   // 使用MP批处理+原生SQL
   @Insert("<script>" +
           "INSERT INTO user_log (user_id, action) VALUES " +
           "<foreach item='item' collection='list' separator=','>" +
           "(#{item.userId}, #{item.action})" +
           "</foreach>" +
           "</script>")
   void batchInsertLog(@Param("list") List<UserLog> logs);
   

六、最佳实践与性能优化

1. 超大分页优化

   <!-- 使用游标代替传统分页 -->
   SELECT * FROM user 
   WHERE id > #{lastId} 
   ORDER BY id ASC 
   LIMIT #{pageSize}
   

2. 索引命中检查
   添加执行计划输出：

   wrapper.last("EXPLAIN FORMAT=JSON");
   String explain = userMapper.selectObjs(wrapper).toString();
   

3. 二级缓存策略
   按命名空间配置缓存：

   <cache eviction="LRU" flushInterval="600000" size="1024"/>
   

4. 结果集流式处理

   try (Cursor<User> cursor = userMapper.selectCursor(wrapper)) {
       cursor.forEach(user -> process(user));
   }
   

5. SQL 性能监控
   集成 Druid 监控：

   spring:
     datasource:
       druid:
         filter:
           stat:
             enabled: true
             slow-sql-millis: 2000
         web-stat-filter:
           enabled: true
   

七、复杂场景综合示例

场景：多层级组织架构用户查询

// Service 层
public Page<UserVO> searchOrgUsers(OrgUserQuery query) {
    Page<User> page = new Page<>(query.getPage(), query.getSize());
    
    LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
    wrapper.like(StringUtils.isNotBlank(query.getName()), User::getName, query.getName())
           .inSql(User::getDeptId, 
               "SELECT id FROM dept WHERE path LIKE '" + getCurrentOrgPath() + "%'");
    
    return userMapper.selectOrgUsers(page, wrapper, query.getMinLevel());
}

<!-- XML 映射 -->
<select id="selectOrgUsers" resultType="com.example.vo.UserVO">
    SELECT 
        u.id, u.name, 
        d.name AS dept_name,
        o.org_name 
    FROM user u
    JOIN dept d ON u.dept_id = d.id
    JOIN organization o ON d.org_id = o.id
    ${ew.customSqlSegment}
    AND d.level >= #{minLevel}
    <!-- 权限过滤 -->
    <if test="!isAdmin()">
        AND o.id = #{currentOrgId}
    </if>
</select>

<!-- 优化COUNT查询 -->
<select id="selectOrgUsers_count" resultType="long">
    SELECT COUNT(1) 
    FROM user u
    ${ew.customSqlSegment}
    AND EXISTS (SELECT 1 FROM dept d 
                WHERE d.id = u.dept_id 
                AND d.level >= #{minLevel})
</select>

八、调试与监控

1. SQL 输出格式化

   mybatis-plus:
     configuration:
       log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
       # 开启美化打印
       log-sql-pretty: true 
   

2. 执行时间监控

   @Bean
   public PerformanceInterceptor performanceInterceptor() {
       PerformanceInterceptor interceptor = new PerformanceInterceptor();
       interceptor.setFormat(true); // SQL格式化
       interceptor.setMaxTime(500); // 超时阈值(ms)
       return interceptor;
   }
   

3. 慢SQL自动告警
   集成 SkyWalking 或 Prometheus 监控慢查询

关键总结

1. 混合原则

   • 简单操作用 Wrapper
   • 复杂逻辑用 XML/注解
   • 两者通过 ${ew.customSqlSegment} 衔接

2. 性能要点

   • 分页查询优化 COUNT 语句
   • 避免循环内执行 SQL
   • 大数据量用流式处理

3. 安全规范

   • 动态参数必须用 #{}
   • 必须用 ${} 时进行白名单过滤
   • 禁止前端参数直拼 SQL

4. 架构建议

   • 复杂查询下沉到 XML
   • Service 层控制事务边界
   • 读写分离场景明确数据源

掌握这些技巧，可完美平衡开发效率与 SQL 灵活性！