MyBatis 动态SQL实现

准备

SQL

CREATE DATABASE test
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

use test;

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, age, email) VALUES ('Alice', 30, 'alice@example.com');
INSERT INTO users (username, age, email) VALUES ('Bob', 25, 'bob@example.com');
INSERT INTO users (username, age, email) VALUES ('Charlie', 35, 'charlie@example.com');
INSERT INTO users (username, age, email) VALUES ('David', 28, 'david@example.com');
INSERT INTO users (username, age, email) VALUES ('Eve', 22, 'eve@example.com');

创建Spring Boot项目

在 Spring Boot 中使用 MyBatis 进行动态 SQL，可以通过 @Mapper 接口来实现。

Maven 依赖

确保在 pom.xml 中添加以下依赖（如果尚未添加）：

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.6</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.6</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

数据库实体类

public class User {
    private Long id;
    private String username;
    private Integer age;
    private String email;
    // Getters and Setters
}

Mapper 接口

使用 @Mapper 注解定义接口，并使用 @SelectProvider 实现动态 SQL：

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.SelectProvider;

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

@Mapper
public interface UserMapper {
    
    @SelectProvider(type = UserSqlProvider.class, method = "buildSelectSql")
    List<User> selectUsers(Map<String, Object> params);
    /**
     * 报错, 单个变量无法转换为Map
     */
    @SelectProvider(type = UserSqlProvider.class, method = "buildSelectSql")
    List<User> selectUsersOne(String username);
    /**
     * 不报错，会被自动转成Map
     */
    @SelectProvider(type = UserSqlProvider.class, method = "buildSelectSql")
    List<User> selectUsersTwo(String username, int age);
    /**
     * 同上
     */
    @SelectProvider(type = UserSqlProvider.class, method = "buildSelectSql")
    List<User> selectUsersThree(@Param("username") String username, @Param("age") int age,@Param("email") String email);
}

动态 SQL 生成类

创建一个 UserSqlProvider 类，生成动态 SQL：

import org.apache.ibatis.jdbc.SQL;

import java.util.Map;

public class UserSqlProvider {
    public String buildSelectSql(Map<String, Object> params) {
        return new SQL() {{
            SELECT("*");
            FROM("users");
            WHERE("1=1");

            if (params.get("username") != null) {
                WHERE("username = #{username}");
            }
            if (params.get("age") != null) {
                WHERE("age = #{age}");
            }
        }}.toString();
    }
}

Service 层

创建一个 Service 来调用 Mapper：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;

    public List<User> getUsers(Map<String, Object> params) {
        return userMapper.selectUsers(params);
    }
}

Controller 层

创建一个简单的 Controller 来接收请求：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

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

@RestController
@RequestMapping("/users")
public class UserController {
    
    @Autowired
    private UserService userService;

    @GetMapping
    public List<User> getUsers(@RequestParam Map<String, Object> params) {
        return userService.getUsers(params);
    }
}

配置数据库连接

在 application.properties 或 application.yml 中配置数据库连接：

spring.datasource.url=jdbc:mysql://localhost:3306/your_database
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mybatis.mapper-locations=classpath*:mappers/*.xml

注意事项

如果使用@SelectProvider，推荐使用Map<String, Object> params 作为入参，万能。也可以使用特定的变量，但是最好一对一，顺序一致，不过ProviderSqlSource类是通过变量参数名来匹配的，顺序不一致，名称一致即可。具体方法如下：

org.apache.ibatis.builder.annotation.ProviderSqlSource#extractProviderMethodArguments(java.util.Map<java.lang.String,java.lang.Object>, java.lang.String[])