Java JDBC DAO对接PostgreSQL实现不区分大小写查询问题求助

开发环境确认 #

• IDE：IntelliJ
• SDK：Java 11.0
• 数据库：PostgreSQL

可行解决方法 #

方案1：使用PostgreSQL原生ILIKE操作符（改动最小，专属PostgreSQL） #

直接修改SQL语句中的LIKE为ILIKE即可，ILIKE是PostgreSQL内置的大小写不敏感匹配操作符，不需要调整其他业务逻辑：

@Override
public List<Employee> searchEmployeesByName(String firstNameSearch, String lastNameSearch) {
    List<Employee> employees = new ArrayList<>();
    // 仅将LIKE替换为ILIKE
    String sql = "SELECT employee_id, department_id, first_name, last_name, birth_date, hire_date " +
            "FROM employee " +
            "WHERE first_name ILIKE ? AND last_name ILIKE ?;";
    firstNameSearch = "%" + firstNameSearch + "%";
    lastNameSearch = "%" + lastNameSearch + "%";

    SqlRowSet results = jdbcTemplate.queryForRowSet(sql, firstNameSearch, lastNameSearch);
    while (results.next()) {
        employees.add(mapRowToEmployee(results));
    }
    return employees;
}

适合不需要兼容其他数据库的业务场景。

方案2：统一转小写匹配（数据库通用，兼容性强） #

将数据库字段和查询参数都转为小写后再做匹配，该写法兼容所有关系型数据库，后续更换数据库不需要修改核心逻辑：

@Override
public List<Employee> searchEmployeesByName(String firstNameSearch, String lastNameSearch) {
    List<Employee> employees = new ArrayList<>();
    String sql = "SELECT employee_id, department_id, first_name, last_name, birth_date, hire_date " +
            "FROM employee " +
            "WHERE LOWER(first_name) LIKE LOWER(?) AND LOWER(last_name) LIKE LOWER(?);";
    firstNameSearch = "%" + firstNameSearch + "%";
    lastNameSearch = "%" + lastNameSearch + "%";

    SqlRowSet results = jdbcTemplate.queryForRowSet(sql, firstNameSearch, lastNameSearch);
    while (results.next()) {
        employees.add(mapRowToEmployee(results));
    }
    return employees;
}

也可以在Java层提前将参数转为小写，减少数据库运算压力，两种写法效果一致。

方案3：新增表达式索引优化查询性能（适合高频查询场景） #

如果该搜索接口是业务高频接口，数据量较大时全表扫描性能较低，可以给对应字段建小写表达式索引，加快查询速度：

-- 给first_name、last_name创建小写索引
CREATE INDEX idx_employee_first_name_lower ON employee(LOWER(first_name));
CREATE INDEX idx_employee_last_name_lower ON employee(LOWER(last_name));

建索引后配合方案2的转小写匹配写法，可以达到和普通字段查询接近的性能。

注意事项 #

• 模糊查询如果使用%xxx前缀匹配的写法，即使建了索引也无法命中，业务如果有高频前缀匹配需求可以考虑使用PostgreSQL的pg_trgm扩展做优化
• 如果搜索需求是全词匹配不需要模糊，也可以将字段类型修改为大小写不敏感的citext类型，后续所有查询自动忽略大小写

内容的提问来源于stack exchange，提问作者Chexpeare