JeecgBoot连接PostgreSQL如何实现忽略大小写查询？

Question

在使用JeecgBoot连接PostgreSQL进行数据查询时，常遇到如何实现忽略大小写敏感的问题。由于PostgreSQL默认区分大小写，当通过JeecgBoot的QueryWrapper或SQL语句查询字符串字段（如用户名、邮箱）时，可能导致“admin”与“Admin”被视为不同值而漏查。尽管可通过`ILIKE`或`LOWER()`函数实现不区分大小写的匹配，但在集成MyBatis-Plus的动态查询中，如何在不破坏原有链式调用的前提下统一处理大小写转换，成为开发中的常见痛点。此外，若在实体类或注解中未合理配置字段映射，还可能引发SQL语法错误或索引失效问题。因此，探索一种兼容性好、性能优且易于维护的忽略大小写查询方案，对提升JeecgBoot+PostgreSQL应用的搜索体验至关重要。

Answer 1

1. 问题背景与技术挑战

在使用JeecgBoot框架连接PostgreSQL数据库进行数据查询时，字符串字段的大小写敏感性是一个常见的痛点。PostgreSQL默认对字符串比较是区分大小写的，这意味着'admin'和'Admin'被视为两个不同的值。

当通过MyBatis-Plus提供的QueryWrapper构建动态查询条件时，若未显式处理大小写，容易导致漏查或误判。例如，在用户登录、邮箱匹配、模糊搜索等场景中，用户体验将大打折扣。

虽然可以通过SQL层面的ILIKE（PostgreSQL特有）或LOWER()函数实现不区分大小写的匹配，但在高复用、链式调用频繁的业务逻辑中，手动添加这些函数会破坏代码的简洁性和可维护性。

2. 常见解决方案对比分析

方案	实现方式	优点	缺点	适用场景
ILIKE	wrapper.like("username", "admin") → ilike	原生支持，语法简洁	仅限PostgreSQL，不可移植	纯PG环境下的快速实现
LOWER() 函数	wrapper.eq("lower(username)", "admin".toLowerCase())	跨数据库兼容性好	可能导致索引失效	多数据库兼容项目
自定义Wrapper扩展	封装忽略大小写的链式方法	保持链式调用，易于复用	需额外开发成本	中大型项目统一规范
数据库Collation配置	设置字段排序规则为ci（大小写不敏感）	无需修改代码	影响全局比较行为，可能引发副作用	新项目初期设计阶段

3. 深入剖析：MyBatis-Plus与JeecgBoot集成机制

JeecgBoot基于MyBatis-Plus构建了丰富的CRUD抽象层，其核心在于QueryWrapper和LambdaQueryWrapper的灵活组合。然而，这些Wrapper并未内置“忽略大小写”的语义操作符。

以用户名查询为例：

QueryWrapper<SysUser> wrapper = new QueryWrapper<>();
wrapper.eq("username", "Admin"); // 实际执行: WHERE username = 'Admin'
// 结果不会匹配 "admin"

若改用LIKE或函数包装，则需改变字段名表达式，破坏了面向对象的链式风格，且不利于IDE提示与重构。

更严重的是，直接在WHERE子句中使用LOWER(username)会导致该字段无法使用B-tree索引，除非创建函数索引：

CREATE INDEX idx_user_lower ON sys_user USING btree (lower(username));

否则性能随数据量增长急剧下降。

4. 解决方案设计：统一的大小写无关查询封装

为兼顾可维护性与性能，我们提出一种分层处理策略：

1. 在Java层封装通用的ignoreCaseEq、ignoreCaseLike等方法；
2. 根据数据库类型自动选择ILIKE（PG）或LOWER()（其他）；
3. 结合实体注解预定义是否启用不区分大小写查询；
4. 数据库侧配合创建函数索引保障性能。

示例代码如下：

public class CaseInsensitiveQueryWrapper<T> extends QueryWrapper<T> {
    
    public CaseInsensitiveQueryWrapper<T> ignoreCaseEq(String column, String value) {
        String dbType = GlobalConfigUtils.currentSessionFactory().getConfiguration().getJdbcEnvironment().getDatabase().getName();
        if ("postgresql".equalsIgnoreCase(dbType)) {
            this.apply(column + " ILIKE {0}", "%" + value + "%");
        } else {
            this.eq("LOWER(" + column + ")", value.toLowerCase());
        }
        return this;
    }
}

5. 架构优化建议与流程图

为了实现系统级的透明化处理，建议引入AOP拦截或字段元数据驱动模式。以下为推荐架构流程：

graph TD A[前端请求查询参数] --> B{是否标记IgnoreCase?} B -- 是 --> C[生成ILIKE或LOWER表达式] B -- 否 --> D[正常EQ/LIKE] C --> E[检查是否有函数索引] E --> F[执行SQL] D --> F F --> G[返回结果]

该流程确保只有标记为忽略大小写的字段才启用特殊处理，避免全量降级为函数索引查询。

6. 性能考量与索引策略

忽略大小写查询的核心性能瓶颈在于索引失效。以下是几种可行的索引优化方案：

• B-tree 函数索引：针对lower(email)建立索引，适用于高频查询字段；
• GIN/GIST 索引：用于全文检索类模糊查询，支持ILIKE '%keyword%'；
• 物化视图 + 定期刷新：将小写标准化字段单独存储并建索引；
• 应用层缓存：对热点数据（如用户名）做Redis缓存，键值统一小写化。

建议优先采用函数索引+ILIKE组合，既保证查询效率又减少冗余存储。

7. 实体类映射与注解增强

可在实体字段上增加自定义注解，标识该字段应参与忽略大小写查询：

@Data
@TableName("sys_user")
public class SysUser {
    private String id;
    
    @CaseInsensitive
    private String username;
    
    @CaseInsensitive
    private String email;
}

配合自定义Wrapper解析注解元信息，实现自动化转换，进一步提升开发效率。

8. 兼容性与未来演进方向

随着JeecgBoot向多租户、微服务架构演进，此类基础能力应下沉至公共组件库。建议将忽略大小写查询能力抽象为独立模块，支持：

• 多数据库自动适配（PG、MySQL、Oracle）；
• Spring Data风格的Repository扩展；
• 与PageHelper、ES集成的统一查询语义；
• 通过配置中心动态开关全局忽略策略。

最终目标是让开发者无需关注底层差异，只需声明“我要模糊查”，系统自动选择最优执行路径。