Spring Boot中MyBatis Provider注解实现动态SQL

项目配置与依赖

1. Maven依赖配置 (pom.xml)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.0</version>
        <relativePath/>
    </parent>
    
    <groupId>com.example</groupId>
    <artifactId>mybatis-dynamic-sql-demo</artifactId>
    <version>1.0.0</version>
    <name>mybatis-dynamic-sql-demo</name>
    <description>Demo project for MyBatis dynamic SQL with Spring Boot</description>
    
    <properties>
        <java.version>11</java.version>
        <mybatis-spring-boot.version>2.2.2</mybatis-spring-boot.version>
        <mysql-connector.version>8.0.29</mysql-connector.version>
        <lombok.version>1.18.24</lombok.version>
    </properties>
    
    <dependencies>
        <!-- Spring Boot Starter -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        
        <!-- MyBatis Spring Boot Starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot.version}</version>
        </dependency>
        
        <!-- MySQL Connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector.version}</version>
            <scope>runtime</scope>
        </dependency>
        
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>
        
        <!-- Test Dependencies -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <!-- HikariCP (连接池，Spring Boot 2.x默认使用) -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>
    </dependencies>
    
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

2. YAML配置文件 (application.yml)

# 应用配置
spring:
  application:
    name: mybatis-dynamic-sql-demo
    
  # 数据源配置
  datasource:
    url: jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      # 连接池配置
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      pool-name: MyBatisHikariCP
  
  # 开发环境配置
  profiles:
    active: dev

# MyBatis配置
mybatis:
  # mapper.xml文件位置
  mapper-locations: classpath:mapper/*.xml
  # 实体类别名包路径
  type-aliases-package: com.example.demo.entity
  # 开启驼峰命名转换
  configuration:
    map-underscore-to-camel-case: true
    # 其他配置
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

# 日志配置
logging:
  level:
    com.example.demo.mapper: DEBUG
    org.springframework.web: INFO
  pattern:
    console: "%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n"

# 服务器配置
server:
  port: 8080
  servlet:
    context-path: /api

3. 实体类

package com.example.demo.entity;

import lombok.Data;
import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
    private Date createTime;
    private Date updateTime;
    
    // 构造方法
    public User() {}
    
    public User(String name, Integer age, String email) {
        this.name = name;
        this.age = age;
        this.email = email;
    }
}

4. Mapper接口

package com.example.demo.mapper;

import com.example.demo.entity.User;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface UserMapper {
    
    @SelectProvider(type = UserSqlProvider.class, method = "buildGetUsersByCondition")
    List<User> selectByCondition(User user);
    
    @UpdateProvider(type = UserSqlProvider.class, method = "buildUpdateUser")
    int update(User user);
    
    @InsertProvider(type = UserSqlProvider.class, method = "buildInsertUser")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    
    @DeleteProvider(type = UserSqlProvider.class, method = "buildDeleteUser")
    int delete(Long id);
    
    // 复杂查询示例
    @SelectProvider(type = UserSqlProvider.class, method = "buildGetUsersWithPage")
    List<User> selectWithPage(@Param("user") User user, 
                             @Param("offset") Integer offset, 
                             @Param("limit") Integer limit);
}

5. SQL提供者类

package com.example.demo.mapper;

import com.example.demo.entity.User;
import org.apache.ibatis.jdbc.SQL;

public class UserSqlProvider {
    
    public String buildGetUsersByCondition(User user) {
        return new SQL() {{
            SELECT("*");
            FROM("user");
            if (user.getName() != null && !user.getName().trim().isEmpty()) {
                WHERE("name like CONCAT('%', #{name}, '%')");
            }
            if (user.getAge() != null) {
                WHERE("age = #{age}");
            }
            if (user.getEmail() != null && !user.getEmail().trim().isEmpty()) {
                WHERE("email like CONCAT('%', #{email}, '%')");
            }
            ORDER_BY("create_time DESC");
        }}.toString();
    }
    
    public String buildUpdateUser(User user) {
        return new SQL() {{
            UPDATE("user");
            if (user.getName() != null) {
                SET("name = #{name}");
            }
            if (user.getAge() != null) {
                SET("age = #{age}");
            }
            if (user.getEmail() != null) {
                SET("email = #{email}");
            }
            SET("update_time = NOW()");
            WHERE("id = #{id}");
        }}.toString();
    }
    
    public String buildInsertUser(User user) {
        return new SQL() {{
            INSERT_INTO("user");
            if (user.getName() != null) {
                VALUES("name", "#{name}");
            }
            if (user.getAge() != null) {
                VALUES("age", "#{age}");
            }
            if (user.getEmail() != null) {
                VALUES("email", "#{email}");
            }
            VALUES("create_time", "NOW()");
            VALUES("update_time", "NOW()");
        }}.toString();
    }
    
    public String buildDeleteUser(Long id) {
        return new SQL() {{
            DELETE_FROM("user");
            WHERE("id = #{id}");
        }}.toString();
    }
    
    public String buildGetUsersWithPage(User user, Integer offset, Integer limit) {
        String sql = new SQL() {{
            SELECT("*");
            FROM("user");
            if (user.getName() != null && !user.getName().trim().isEmpty()) {
                WHERE("name like CONCAT('%', #{user.name}, '%')");
            }
            if (user.getAge() != null) {
                WHERE("age = #{user.age}");
            }
            if (user.getEmail() != null && !user.getEmail().trim().isEmpty()) {
                WHERE("email like CONCAT('%', #{user.email}, '%')");
            }
            ORDER_BY("create_time DESC");
        }}.toString();
        
        // 添加分页
        if (offset != null && limit != null) {
            sql += " LIMIT #{offset}, #{limit}";
        }
        
        return sql;
    }
}

6. Service层

package com.example.demo.service;

import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    public List<User> getUsersByCondition(User user) {
        return userMapper.selectByCondition(user);
    }
    
    public int updateUser(User user) {
        return userMapper.update(user);
    }
    
    public int addUser(User user) {
        return userMapper.insert(user);
    }
    
    public int deleteUser(Long id) {
        return userMapper.delete(id);
    }
    
    public List<User> getUsersWithPage(User user, Integer page, Integer size) {
        Integer offset = (page - 1) * size;
        return userMapper.selectWithPage(user, offset, size);
    }
}

7. Controller层

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/users")
public class UserController {
    
    @Autowired
    private UserService userService;
    
    @GetMapping
    public Map<String, Object> getUsersByCondition(
            @RequestParam(required = false) String name,
            @RequestParam(required = false) Integer age,
            @RequestParam(required = false) String email,
            @RequestParam(defaultValue = "1") Integer page,
            @RequestParam(defaultValue = "10") Integer size) {
        
        User user = new User();
        user.setName(name);
        user.setAge(age);
        user.setEmail(email);
        
        List<User> users;
        long total = 0;
        
        if (page != null && size != null) {
            users = userService.getUsersWithPage(user, page, size);
            // 实际项目中应查询总数
            total = users.size();
        } else {
            users = userService.getUsersByCondition(user);
            total = users.size();
        }
        
        Map<String, Object> result = new HashMap<>();
        result.put("code", 200);
        result.put("message", "success");
        result.put("data", users);
        result.put("total", total);
        
        return result;
    }
    
    @PutMapping("/{id}")
    public Map<String, Object> updateUser(@PathVariable Long id, @RequestBody User user) {
        user.setId(id);
        int result = userService.updateUser(user);
        
        Map<String, Object> response = new HashMap<>();
        response.put("code", result > 0 ? 200 : 500);
        response.put("message", result > 0 ? "更新成功" : "更新失败");
        response.put("data", result);
        
        return response;
    }
    
    @PostMapping
    public Map<String, Object> addUser(@RequestBody User user) {
        int result = userService.addUser(user);
        
        Map<String, Object> response = new HashMap<>();
        response.put("code", result > 0 ? 200 : 500);
        response.put("message", result > 0 ? "添加成功" : "添加失败");
        response.put("data", user);
        
        return response;
    }
    
    @DeleteMapping("/{id}")
    public Map<String, Object> deleteUser(@PathVariable Long id) {
        int result = userService.deleteUser(id);
        
        Map<String, Object> response = new HashMap<>();
        response.put("code", result > 0 ? 200 : 500);
        response.put("message", result > 0 ? "删除成功" : "删除失败");
        response.put("data", result);
        
        return response;
    }
}

8. 主应用类

package com.example.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.demo.mapper")
public class MybatisDynamicSqlApplication {
    
    public static void main(String[] args) {
        SpringApplication.run(MybatisDynamicSqlApplication.class, args);
    }
}

总结

本教程详细介绍了如何在Spring Boot项目中使用YAML配置方式整合MyBatis，并通过@SelectProvider、@UpdateProvider等注解实现动态SQL拼接。相比XML配置方式，这种方法更加灵活，可以利用Java的全部功能来构建复杂的SQL语句，同时保持代码的类型安全和可维护性。

关键点：

1. 使用YAML配置数据源和MyBatis属性

2. 通过Provider类实现动态SQL拼接

3. 使用MyBatis的SQL类构建类型安全的SQL语句

4. 实现分页查询和条件查询