前言

MyBatis动态SQL作为企业级应用的核心功能之一，直接影响着数据库性能和系统稳定性。通过深入理解MyBatis动态SQL的常见陷阱，掌握SQL优化技巧，能够构建一个高效、稳定、可扩展的SQL构建系统，确保企业级应用的数据库性能。本文从动态SQL陷阱分析到性能优化，从基础原理到企业级应用，系统梳理MyBatis动态SQL的完整解决方案。

一、MyBatis动态SQL架构设计

1.1 MyBatis动态SQL整体架构

1.2 动态SQL陷阱架构

二、MyBatis动态SQL陷阱分析

2.1 过度动态化陷阱

2.1.1 问题描述

过度使用动态SQL导致SQL语句复杂，难以维护和优化。

2.1.2 代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<!-- ❌ 错误：过度动态化的SQL -->
<select id="findUsers" parameterType="UserQuery" resultType="User">
    SELECT * FROM users
    <where>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="createTimeStart != null">
            AND create_time >= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND create_time <= #{createTimeEnd}
        </if>
        <if test="departmentId != null">
            AND department_id = #{departmentId}
        </if>
        <if test="roleId != null">
            AND role_id = #{roleId}
        </if>
        <if test="salaryMin != null">
            AND salary >= #{salaryMin}
        </if>
        <if test="salaryMax != null">
            AND salary <= #{salaryMax}
        </if>
    </where>
    <choose>
        <when test="orderBy == 'name'">
            ORDER BY name
        </when>
        <when test="orderBy == 'age'">
            ORDER BY age
        </when>
        <when test="orderBy == 'createTime'">
            ORDER BY create_time
        </when>
        <when test="orderBy == 'salary'">
            ORDER BY salary
        </when>
        <otherwise>
            ORDER BY id
        </otherwise>
    </choose>
    <if test="orderDirection != null and orderDirection != ''">
        ${orderDirection}
    </if>
</select>

2.1.3 问题分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    // ❌ 错误：过度动态化导致的问题
    public List<User> findUsers(UserQuery query) {
        // 1. SQL语句复杂，难以理解
        // 2. 索引使用效率低
        // 3. 维护困难
        // 4. 性能不可预测
        return userMapper.findUsers(query);
    }

    // ✅ 正确：拆分为多个专用查询
    public List<User> findUsersByName(String name) {
        return userMapper.findUsersByName(name);
    }

    public List<User> findUsersByEmail(String email) {
        return userMapper.findUsersByEmail(email);
    }

    public List<User> findUsersByAgeRange(Integer minAge, Integer maxAge) {
        return userMapper.findUsersByAgeRange(minAge, maxAge);
    }
}

2.2 SQL注入陷阱

2.2.1 问题描述

使用${}进行字符串拼接可能导致SQL注入攻击。

2.2.2 代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!-- ❌ 错误：使用${}导致SQL注入 -->
<select id="findUsersByOrder" parameterType="map" resultType="User">
    SELECT * FROM users
    <where>
        <if test="name != null">
            AND name LIKE '%${name}%'
        </if>
        <if test="orderBy != null">
            ORDER BY ${orderBy}
        </if>
        <if test="orderDirection != null">
            ${orderDirection}
        </if>
    </where>
</select>

2.2.3 安全解决方案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<!-- ✅ 正确：使用#{}防止SQL注入 -->
<select id="findUsersByOrder" parameterType="map" resultType="User">
    SELECT * FROM users
    <where>
        <if test="name != null">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <choose>
            <when test="orderBy == 'name'">
                ORDER BY name
            </when>
            <when test="orderBy == 'age'">
                ORDER BY age
            </when>
            <when test="orderBy == 'createTime'">
                ORDER BY create_time
            </when>
            <otherwise>
                ORDER BY id
            </otherwise>
        </choose>
        <choose>
            <when test="orderDirection == 'ASC'">
                ASC
            </when>
            <when test="orderDirection == 'DESC'">
                DESC
            </when>
            <otherwise>
                ASC
            </otherwise>
        </choose>
    </where>
</select>

2.3 索引失效陷阱

2.3.1 问题描述

动态SQL中的条件组合可能导致索引失效。

2.3.2 代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<!-- ❌ 错误：索引失效的SQL -->
<select id="findOrders" parameterType="OrderQuery" resultType="Order">
    SELECT * FROM orders
    <where>
        <if test="userId != null">
            AND user_id = #{userId}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="createTimeStart != null">
            AND create_time >= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND create_time <= #{createTimeEnd}
        </if>
        <if test="amountMin != null">
            AND amount >= #{amountMin}
        </if>
        <if test="amountMax != null">
            AND amount <= #{amountMax}
        </if>
    </where>
</select>

2.3.3 索引优化方案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<!-- ✅ 正确：优化索引使用的SQL -->
<select id="findOrdersByUser" parameterType="OrderQuery" resultType="Order">
    SELECT * FROM orders
    WHERE user_id = #{userId}
    <if test="status != null">
        AND status = #{status}
    </if>
    <if test="createTimeStart != null">
        AND create_time >= #{createTimeStart}
    </if>
    <if test="createTimeEnd != null">
        AND create_time <= #{createTimeEnd}
    </if>
    ORDER BY create_time DESC
</select>

<select id="findOrdersByStatus" parameterType="OrderQuery" resultType="Order">
    SELECT * FROM orders
    WHERE status = #{status}
    <if test="createTimeStart != null">
        AND create_time >= #{createTimeStart}
    </if>
    <if test="createTimeEnd != null">
        AND create_time <= #{createTimeEnd}
    </if>
    ORDER BY create_time DESC
</select>

2.4 维护困难陷阱

2.4.1 问题描述

复杂的动态SQL难以理解和维护。

2.4.2 代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
<!-- ❌ 错误：难以维护的复杂SQL -->
<select id="complexQuery" parameterType="ComplexQuery" resultType="Result">
    SELECT 
        u.id, u.name, u.email,
        d.name as department_name,
        r.name as role_name,
        COUNT(o.id) as order_count,
        SUM(o.amount) as total_amount
    FROM users u
    LEFT JOIN departments d ON u.department_id = d.id
    LEFT JOIN user_roles ur ON u.id = ur.user_id
    LEFT JOIN roles r ON ur.role_id = r.id
    LEFT JOIN orders o ON u.id = o.user_id
    <where>
        <if test="name != null and name != ''">
            AND u.name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="email != null and email != ''">
            AND u.email = #{email}
        </if>
        <if test="departmentId != null">
            AND u.department_id = #{departmentId}
        </if>
        <if test="roleId != null">
            AND ur.role_id = #{roleId}
        </if>
        <if test="status != null">
            AND u.status = #{status}
        </if>
        <if test="createTimeStart != null">
            AND u.create_time >= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND u.create_time <= #{createTimeEnd}
        </if>
        <if test="orderStatus != null">
            AND o.status = #{orderStatus}
        </if>
        <if test="orderAmountMin != null">
            AND o.amount >= #{orderAmountMin}
        </if>
        <if test="orderAmountMax != null">
            AND o.amount <= #{orderAmountMax}
        </if>
    </where>
    GROUP BY u.id, u.name, u.email, d.name, r.name
    <choose>
        <when test="orderBy == 'name'">
            ORDER BY u.name
        </when>
        <when test="orderBy == 'email'">
            ORDER BY u.email
        </when>
        <when test="orderBy == 'department'">
            ORDER BY d.name
        </when>
        <when test="orderBy == 'role'">
            ORDER BY r.name
        </when>
        <when test="orderBy == 'orderCount'">
            ORDER BY order_count
        </when>
        <when test="orderBy == 'totalAmount'">
            ORDER BY total_amount
        </when>
        <otherwise>
            ORDER BY u.id
        </otherwise>
    </choose>
    <if test="orderDirection != null and orderDirection != ''">
        ${orderDirection}
    </if>
</select>

三、MyBatis动态SQL优化策略

3.1 SQL模板化优化

3.1.1 模板化设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<!-- ✅ 正确：模板化的SQL设计 -->
<sql id="userBaseColumns">
    id, name, email, age, status, create_time, update_time
</sql>

<sql id="userWhereConditions">
    <where>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</sql>

<select id="findUsers" parameterType="UserQuery" resultType="User">
    SELECT <include refid="userBaseColumns"/>
    FROM users
    <include refid="userWhereConditions"/>
    ORDER BY create_time DESC
</select>

3.1.2 专用查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<!-- ✅ 正确：专用查询设计 -->
<select id="findUsersByName" parameterType="string" resultType="User">
    SELECT <include refid="userBaseColumns"/>
    FROM users
    WHERE name LIKE CONCAT('%', #{name}, '%')
    ORDER BY name
</select>

<select id="findUsersByEmail" parameterType="string" resultType="User">
    SELECT <include refid="userBaseColumns"/>
    FROM users
    WHERE email = #{email}
</select>

<select id="findUsersByStatus" parameterType="int" resultType="User">
    SELECT <include refid="userBaseColumns"/>
    FROM users
    WHERE status = #{status}
    ORDER BY create_time DESC
</select>

3.2 参数验证优化

3.2.1 参数验证器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@Component
public class QueryValidator {

    public void validateUserQuery(UserQuery query) {
        if (query == null) {
            throw new IllegalArgumentException("Query cannot be null");
        }

        if (query.getName() != null && query.getName().length() > 50) {
            throw new IllegalArgumentException("Name length cannot exceed 50 characters");
        }

        if (query.getEmail() != null && !isValidEmail(query.getEmail())) {
            throw new IllegalArgumentException("Invalid email format");
        }

        if (query.getAge() != null && (query.getAge() < 0 || query.getAge() > 150)) {
            throw new IllegalArgumentException("Age must be between 0 and 150");
        }
    }

    private boolean isValidEmail(String email) {
        return email.matches("^[A-Za-z0-9+_.-]+@([A-Za-z0-9.-]+\\.[A-Za-z]{2,})$");
    }
}

3.2.2 参数标准化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
@Data
public class UserQuery {
    private String name;
    private String email;
    private Integer status;
    private Integer age;
    private LocalDateTime createTimeStart;
    private LocalDateTime createTimeEnd;
    private String orderBy;
    private String orderDirection;

    // 标准化参数
    public void normalize() {
        if (this.name != null) {
            this.name = this.name.trim();
        }

        if (this.email != null) {
            this.email = this.email.trim().toLowerCase();
        }

        if (this.orderBy == null) {
            this.orderBy = "createTime";
        }

        if (this.orderDirection == null) {
            this.orderDirection = "DESC";
        }

        // 验证排序字段
        if (!isValidOrderBy(this.orderBy)) {
            this.orderBy = "createTime";
        }

        // 验证排序方向
        if (!isValidOrderDirection(this.orderDirection)) {
            this.orderDirection = "DESC";
        }
    }

    private boolean isValidOrderBy(String orderBy) {
        return Arrays.asList("name", "email", "age", "createTime", "status").contains(orderBy);
    }

    private boolean isValidOrderDirection(String orderDirection) {
        return Arrays.asList("ASC", "DESC").contains(orderDirection.toUpperCase());
    }
}

3.3 缓存优化策略

3.3.1 查询结果缓存

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Service
public class CachedUserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    private static final String USER_CACHE_PREFIX = "user:query:";
    private static final int CACHE_EXPIRE_SECONDS = 300;

    @Cacheable(value = "userQuery", key = "#query.hashCode()")
    public List<User> findUsers(UserQuery query) {
        return userMapper.findUsers(query);
    }

    @CacheEvict(value = "userQuery", allEntries = true)
    public void clearUserCache() {
        // 清除用户查询缓存
    }
}

3.3.2 SQL模板缓存

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Component
public class SqlTemplateCache {

    private final Map<String, String> templateCache = new ConcurrentHashMap<>();

    public String getTemplate(String templateKey) {
        return templateCache.computeIfAbsent(templateKey, this::loadTemplate);
    }

    private String loadTemplate(String templateKey) {
        // 从配置文件或数据库加载SQL模板
        return loadTemplateFromConfig(templateKey);
    }

    private String loadTemplateFromConfig(String templateKey) {
        // 实现模板加载逻辑
        return "";
    }
}

3.4 性能监控优化

3.4.1 SQL性能监控

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Component
public class SqlPerformanceMonitor {

    private final MeterRegistry meterRegistry;
    private final Timer sqlTimer;
    private final Counter sqlCounter;

    public SqlPerformanceMonitor(MeterRegistry meterRegistry) {
        this.meterRegistry = meterRegistry;
        this.sqlTimer = Timer.builder("sql.execution.duration")
            .description("SQL执行耗时")
            .register(meterRegistry);
        this.sqlCounter = Counter.builder("sql.execution.count")
            .description("SQL执行次数")
            .register(meterRegistry);
    }

    public <T> T monitorSqlExecution(Supplier<T> sqlExecution, String sqlId) {
        return Timer.Sample.start(meterRegistry)
            .stop(Timer.builder("sql.execution.duration")
                .tag("sqlId", sqlId)
                .register(meterRegistry))
            .recordCallable(() -> {
                sqlCounter.increment();
                return sqlExecution.get();
            });
    }
}

3.4.2 SQL复杂度分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@Component
public class SqlComplexityAnalyzer {

    public SqlComplexity analyzeSql(String sql) {
        SqlComplexity complexity = new SqlComplexity();

        // 分析SQL复杂度
        complexity.setJoinCount(countJoins(sql));
        complexity.setConditionCount(countConditions(sql));
        complexity.setSubqueryCount(countSubqueries(sql));
        complexity.setDynamicConditionCount(countDynamicConditions(sql));

        return complexity;
    }

    private int countJoins(String sql) {
        return (int) Pattern.compile("\\bJOIN\\b", Pattern.CASE_INSENSITIVE)
            .matcher(sql).results().count();
    }

    private int countConditions(String sql) {
        return (int) Pattern.compile("\\bAND\\b|\\bOR\\b", Pattern.CASE_INSENSITIVE)
            .matcher(sql).results().count();
    }

    private int countSubqueries(String sql) {
        return (int) Pattern.compile("\\(\\s*SELECT\\b", Pattern.CASE_INSENSITIVE)
            .matcher(sql).results().count();
    }

    private int countDynamicConditions(String sql) {
        return (int) Pattern.compile("<if\\b|<choose\\b|<when\\b|<otherwise\\b")
            .matcher(sql).results().count();
    }
}

四、企业级SQL构建解决方案

4.1 SQL构建器设计

4.1.1 通用SQL构建器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
@Component
public class SqlBuilder {

    public String buildSelectSql(String tableName, List<String> columns, Map<String, Object> conditions) {
        StringBuilder sql = new StringBuilder();

        // SELECT子句
        sql.append("SELECT ");
        if (columns.isEmpty()) {
            sql.append("*");
        } else {
            sql.append(String.join(", ", columns));
        }

        // FROM子句
        sql.append(" FROM ").append(tableName);

        // WHERE子句
        if (!conditions.isEmpty()) {
            sql.append(" WHERE ");
            List<String> whereConditions = new ArrayList<>();
            for (Map.Entry<String, Object> entry : conditions.entrySet()) {
                whereConditions.add(entry.getKey() + " = #{" + entry.getKey() + "}");
            }
            sql.append(String.join(" AND ", whereConditions));
        }

        return sql.toString();
    }

    public String buildInsertSql(String tableName, Map<String, Object> values) {
        StringBuilder sql = new StringBuilder();

        sql.append("INSERT INTO ").append(tableName).append(" (");
        sql.append(String.join(", ", values.keySet()));
        sql.append(") VALUES (");

        List<String> valuePlaceholders = new ArrayList<>();
        for (String key : values.keySet()) {
            valuePlaceholders.add("#{" + key + "}");
        }
        sql.append(String.join(", ", valuePlaceholders));
        sql.append(")");

        return sql.toString();
    }

    public String buildUpdateSql(String tableName, Map<String, Object> values, Map<String, Object> conditions) {
        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE ").append(tableName).append(" SET ");

        List<String> setClauses = new ArrayList<>();
        for (String key : values.keySet()) {
            setClauses.add(key + " = #{" + key + "}");
        }
        sql.append(String.join(", ", setClauses));

        if (!conditions.isEmpty()) {
            sql.append(" WHERE ");
            List<String> whereConditions = new ArrayList<>();
            for (String key : conditions.keySet()) {
                whereConditions.add(key + " = #{" + key + "}");
            }
            sql.append(String.join(" AND ", whereConditions));
        }

        return sql.toString();
    }
}

4.1.2 动态SQL构建器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
@Component
public class DynamicSqlBuilder {

    public String buildDynamicSelect(String tableName, List<String> columns, 
                                   Map<String, Object> conditions, String orderBy) {
        StringBuilder sql = new StringBuilder();

        // SELECT子句
        sql.append("SELECT ");
        if (columns.isEmpty()) {
            sql.append("*");
        } else {
            sql.append(String.join(", ", columns));
        }

        // FROM子句
        sql.append(" FROM ").append(tableName);

        // WHERE子句
        if (!conditions.isEmpty()) {
            sql.append(" WHERE ");
            List<String> whereConditions = new ArrayList<>();
            for (Map.Entry<String, Object> entry : conditions.entrySet()) {
                if (entry.getValue() != null) {
                    whereConditions.add(entry.getKey() + " = #{" + entry.getKey() + "}");
                }
            }
            if (!whereConditions.isEmpty()) {
                sql.append(String.join(" AND ", whereConditions));
            }
        }

        // ORDER BY子句
        if (orderBy != null && !orderBy.isEmpty()) {
            sql.append(" ORDER BY ").append(orderBy);
        }

        return sql.toString();
    }
}

4.2 SQL模板管理

4.2.1 模板管理器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@Service
public class SqlTemplateManager {

    private final Map<String, String> templates = new ConcurrentHashMap<>();

    @PostConstruct
    public void loadTemplates() {
        // 加载SQL模板
        loadTemplate("user.select.by.id", "SELECT * FROM users WHERE id = #{id}");
        loadTemplate("user.select.by.name", "SELECT * FROM users WHERE name LIKE CONCAT('%', #{name}, '%')");
        loadTemplate("user.select.by.email", "SELECT * FROM users WHERE email = #{email}");
        loadTemplate("user.select.by.status", "SELECT * FROM users WHERE status = #{status}");
    }

    public String getTemplate(String templateKey) {
        return templates.get(templateKey);
    }

    public String buildSql(String templateKey, Map<String, Object> parameters) {
        String template = getTemplate(templateKey);
        if (template == null) {
            throw new IllegalArgumentException("Template not found: " + templateKey);
        }

        // 简单的参数替换
        String sql = template;
        for (Map.Entry<String, Object> entry : parameters.entrySet()) {
            sql = sql.replace("#{" + entry.getKey() + "}", String.valueOf(entry.getValue()));
        }

        return sql;
    }

    private void loadTemplate(String key, String template) {
        templates.put(key, template);
    }
}

4.2.2 模板配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# application.yml
sql:
  templates:
    user:
      select:
        by-id: "SELECT * FROM users WHERE id = #{id}"
        by-name: "SELECT * FROM users WHERE name LIKE CONCAT('%', #{name}, '%')"
        by-email: "SELECT * FROM users WHERE email = #{email}"
        by-status: "SELECT * FROM users WHERE status = #{status}"
    order:
      select:
        by-user: "SELECT * FROM orders WHERE user_id = #{userId}"
        by-status: "SELECT * FROM orders WHERE status = #{status}"
        by-date-range: "SELECT * FROM orders WHERE create_time BETWEEN #{startDate} AND #{endDate}"

4.3 SQL安全防护

4.3.1 SQL注入防护

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
@Component
public class SqlInjectionProtector {

    private static final Set<String> DANGEROUS_KEYWORDS = Set.of(
        "DROP", "DELETE", "INSERT", "UPDATE", "ALTER", "CREATE", "EXEC", "EXECUTE"
    );

    public void validateParameter(String parameter) {
        if (parameter == null) {
            return;
        }

        String upperParam = parameter.toUpperCase();
        for (String keyword : DANGEROUS_KEYWORDS) {
            if (upperParam.contains(keyword)) {
                throw new SecurityException("Potentially dangerous SQL keyword detected: " + keyword);
            }
        }
    }

    public void validateOrderBy(String orderBy) {
        if (orderBy == null || orderBy.isEmpty()) {
            return;
        }

        // 只允许字母、数字、下划线和逗号
        if (!orderBy.matches("^[a-zA-Z0-9_,\\s]+$")) {
            throw new SecurityException("Invalid characters in ORDER BY clause");
        }

        // 检查是否包含危险关键字
        validateParameter(orderBy);
    }

    public void validateOrderDirection(String orderDirection) {
        if (orderDirection == null || orderDirection.isEmpty()) {
            return;
        }

        if (!orderDirection.toUpperCase().matches("^(ASC|DESC)$")) {
            throw new SecurityException("Invalid ORDER BY direction");
        }
    }
}

4.3.2 参数验证器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
@Component
public class SqlParameterValidator {

    public void validateUserQuery(UserQuery query) {
        if (query == null) {
            throw new IllegalArgumentException("Query cannot be null");
        }

        // 验证字符串参数
        if (query.getName() != null) {
            validateStringParameter(query.getName(), "name", 50);
        }

        if (query.getEmail() != null) {
            validateEmailParameter(query.getEmail());
        }

        // 验证数值参数
        if (query.getAge() != null) {
            validateNumericParameter(query.getAge(), "age", 0, 150);
        }

        if (query.getStatus() != null) {
            validateNumericParameter(query.getStatus(), "status", 0, 10);
        }

        // 验证排序参数
        if (query.getOrderBy() != null) {
            validateOrderByParameter(query.getOrderBy());
        }

        if (query.getOrderDirection() != null) {
            validateOrderDirectionParameter(query.getOrderDirection());
        }
    }

    private void validateStringParameter(String value, String paramName, int maxLength) {
        if (value.length() > maxLength) {
            throw new IllegalArgumentException(paramName + " length cannot exceed " + maxLength);
        }

        // 检查特殊字符
        if (value.contains("'") || value.contains("\"") || value.contains(";")) {
            throw new IllegalArgumentException("Invalid characters in " + paramName);
        }
    }

    private void validateEmailParameter(String email) {
        if (!email.matches("^[A-Za-z0-9+_.-]+@([A-Za-z0-9.-]+\\.[A-Za-z]{2,})$")) {
            throw new IllegalArgumentException("Invalid email format");
        }
    }

    private void validateNumericParameter(Number value, String paramName, int min, int max) {
        int intValue = value.intValue();
        if (intValue < min || intValue > max) {
            throw new IllegalArgumentException(paramName + " must be between " + min + " and " + max);
        }
    }

    private void validateOrderByParameter(String orderBy) {
        Set<String> allowedColumns = Set.of("id", "name", "email", "age", "status", "create_time");
        if (!allowedColumns.contains(orderBy.toLowerCase())) {
            throw new IllegalArgumentException("Invalid ORDER BY column: " + orderBy);
        }
    }

    private void validateOrderDirectionParameter(String orderDirection) {
        if (!orderDirection.toUpperCase().matches("^(ASC|DESC)$")) {
            throw new IllegalArgumentException("Invalid ORDER BY direction: " + orderDirection);
        }
    }
}

4.4 SQL性能优化

4.4.1 查询优化器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
@Component
public class SqlQueryOptimizer {

    public String optimizeQuery(String sql) {
        // 移除不必要的空格
        sql = sql.replaceAll("\\s+", " ").trim();

        // 优化WHERE子句
        sql = optimizeWhereClause(sql);

        // 优化ORDER BY子句
        sql = optimizeOrderByClause(sql);

        return sql;
    }

    private String optimizeWhereClause(String sql) {
        // 移除空的WHERE子句
        sql = sql.replaceAll("WHERE\\s+AND", "WHERE");
        sql = sql.replaceAll("WHERE\\s+OR", "WHERE");

        // 移除末尾的AND/OR
        sql = sql.replaceAll("\\s+(AND|OR)\\s*$", "");

        return sql;
    }

    private String optimizeOrderByClause(String sql) {
        // 移除重复的ORDER BY
        sql = sql.replaceAll("ORDER\\s+BY.*?ORDER\\s+BY", "ORDER BY");

        return sql;
    }
}

4.4.2 索引建议器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
@Component
public class SqlIndexAdvisor {

    public List<String> suggestIndexes(String sql) {
        List<String> suggestions = new ArrayList<>();

        // 分析WHERE子句
        suggestions.addAll(analyzeWhereClause(sql));

        // 分析ORDER BY子句
        suggestions.addAll(analyzeOrderByClause(sql));

        // 分析JOIN子句
        suggestions.addAll(analyzeJoinClause(sql));

        return suggestions;
    }

    private List<String> analyzeWhereClause(String sql) {
        List<String> suggestions = new ArrayList<>();

        // 提取WHERE条件
        Pattern wherePattern = Pattern.compile("WHERE\\s+(.*?)(?:ORDER|GROUP|LIMIT|$)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = wherePattern.matcher(sql);

        if (matcher.find()) {
            String whereClause = matcher.group(1);

            // 分析等值条件
            Pattern eqPattern = Pattern.compile("(\\w+)\\s*=\\s*#\\{[^}]+\\}");
            Matcher eqMatcher = eqPattern.matcher(whereClause);

            while (eqMatcher.find()) {
                String column = eqMatcher.group(1);
                suggestions.add("CREATE INDEX idx_" + column + " ON table_name (" + column + ")");
            }
        }

        return suggestions;
    }

    private List<String> analyzeOrderByClause(String sql) {
        List<String> suggestions = new ArrayList<>();

        Pattern orderPattern = Pattern.compile("ORDER\\s+BY\\s+(.*?)(?:LIMIT|$)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = orderPattern.matcher(sql);

        if (matcher.find()) {
            String orderClause = matcher.group(1);
            String[] columns = orderClause.split(",");

            for (String column : columns) {
                column = column.trim().split("\\s+")[0]; // 移除ASC/DESC
                suggestions.add("CREATE INDEX idx_order_" + column + " ON table_name (" + column + ")");
            }
        }

        return suggestions;
    }

    private List<String> analyzeJoinClause(String sql) {
        List<String> suggestions = new ArrayList<>();

        Pattern joinPattern = Pattern.compile("JOIN\\s+\\w+\\s+\\w+\\s+ON\\s+(\\w+)\\s*=\\s*(\\w+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = joinPattern.matcher(sql);

        while (matcher.find()) {
            String leftColumn = matcher.group(1);
            String rightColumn = matcher.group(2);

            suggestions.add("CREATE INDEX idx_join_" + leftColumn + " ON table_name (" + leftColumn + ")");
            suggestions.add("CREATE INDEX idx_join_" + rightColumn + " ON table_name (" + rightColumn + ")");
        }

        return suggestions;
    }
}

五、MyBatis动态SQL最佳实践

5.1 使用原则

5.1.1 设计原则

  1. 简单优先：优先使用简单的静态SQL
  2. 专用查询：为不同场景设计专用查询
  3. 模板复用：使用SQL片段提高复用性
  4. 安全第一：始终使用#{}防止SQL注入

5.1.2 性能原则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
@Service
public class OptimizedUserService {

    @Autowired
    private UserMapper userMapper;

    // ✅ 正确：专用查询设计
    public List<User> findUsersByName(String name) {
        return userMapper.findUsersByName(name);
    }

    public List<User> findUsersByEmail(String email) {
        return userMapper.findUsersByEmail(email);
    }

    public List<User> findUsersByStatus(int status) {
        return userMapper.findUsersByStatus(status);
    }

    // ✅ 正确：组合查询
    public List<User> findUsersByNameAndStatus(String name, int status) {
        return userMapper.findUsersByNameAndStatus(name, status);
    }

    // ✅ 正确：分页查询
    public PageResult<User> findUsersWithPagination(UserQuery query, int page, int size) {
        int offset = (page - 1) * size;
        List<User> users = userMapper.findUsersWithPagination(query, offset, size);
        long total = userMapper.countUsers(query);

        return new PageResult<>(users, total, page, size);
    }
}

5.2 维护策略

5.2.1 代码组织

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Mapper
public interface UserMapper {

    // 基础查询
    User findById(Long id);
    List<User> findAll();

    // 条件查询
    List<User> findByName(String name);
    List<User> findByEmail(String email);
    List<User> findByStatus(int status);

    // 组合查询
    List<User> findByNameAndStatus(String name, int status);
    List<User> findByEmailAndStatus(String email, int status);

    // 范围查询
    List<User> findByAgeRange(int minAge, int maxAge);
    List<User> findByCreateTimeRange(LocalDateTime start, LocalDateTime end);

    // 分页查询
    List<User> findWithPagination(UserQuery query, int offset, int limit);
    long countUsers(UserQuery query);

    // 统计查询
    long countByStatus(int status);
    BigDecimal sumSalaryByStatus(int status);
}

5.2.2 文档管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/**
 * 用户查询Mapper
 * 
 * 查询方法命名规范：
 * - findById: 根据ID查询
 * - findByName: 根据名称查询
 * - findByStatus: 根据状态查询
 * - findWithPagination: 分页查询
 * - countUsers: 统计用户数量
 * 
 * 性能优化建议：
 * - 为常用查询字段创建索引
 * - 使用专用查询避免复杂动态SQL
 * - 合理使用缓存提高查询性能
 */
@Mapper
public interface UserMapper {
    // 方法实现...
}

5.3 监控与运维

5.3.1 SQL监控指标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@Component
public class SqlMonitoringMetrics {

    private final MeterRegistry meterRegistry;

    public SqlMonitoringMetrics(MeterRegistry meterRegistry) {
        this.meterRegistry = meterRegistry;
    }

    public void recordSqlExecution(String sqlId, long duration, boolean success) {
        // 记录SQL执行时间
        Timer.builder("sql.execution.duration")
            .tag("sqlId", sqlId)
            .tag("success", String.valueOf(success))
            .register(meterRegistry)
            .record(duration, TimeUnit.MILLISECONDS);

        // 记录SQL执行次数
        Counter.builder("sql.execution.count")
            .tag("sqlId", sqlId)
            .tag("success", String.valueOf(success))
            .register(meterRegistry)
            .increment();
    }

    public void recordSqlComplexity(String sqlId, int complexity) {
        Gauge.builder("sql.complexity")
            .tag("sqlId", sqlId)
            .register(meterRegistry, () -> complexity);
    }
}

5.3.2 SQL健康检查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
@Component
public class SqlHealthIndicator implements HealthIndicator {

    @Autowired
    private DataSource dataSource;

    @Override
    public Health health() {
        try {
            // 检查数据库连接
            try (Connection connection = dataSource.getConnection()) {
                boolean isValid = connection.isValid(5);

                if (isValid) {
                    return Health.up()
                        .withDetail("database", "Available")
                        .withDetail("sql", "Healthy")
                        .build();
                } else {
                    return Health.down()
                        .withDetail("database", "Unavailable")
                        .withDetail("sql", "Unhealthy")
                        .build();
                }
            }
        } catch (Exception e) {
            return Health.down()
                .withDetail("error", e.getMessage())
                .build();
        }
    }
}

六、总结

MyBatis动态SQL是企业级应用开发中的重要工具，通过深入理解动态SQL的各种陷阱，掌握有效的优化技巧，能够构建一个高效、稳定、可维护的SQL构建系统。

6.1 核心要点

  1. 动态SQL陷阱：过度动态化、SQL注入、索引失效、维护困难
  2. 优化策略：SQL模板化、参数验证、缓存优化、性能监控
  3. 解决方案：专用查询设计、安全防护、索引优化、监控运维
  4. 最佳实践：使用原则、维护策略、监控运维

6.2 企业级应用建议

  1. 建立完善的SQL设计规范，确保SQL使用的一致性
  2. 实施严格的SQL安全防护，防止SQL注入攻击
  3. 建立SQL性能监控体系，实时监控SQL执行性能
  4. 制定SQL维护策略，确保系统长期稳定运行

通过以上措施，可以构建一个高效、安全、可维护的MyBatis动态SQL系统，为企业的各种业务场景提供可靠的数据库访问保障。