JPA结合PostgreSQL使用时如何实现查询忽略大小写敏感性

方案1：使用JPQL标准LOWER函数（推荐，通用无数据库依赖） #

该方案利用JPA JPQL标准支持的LOWER函数，同时将查询字段和搜索参数统一转为小写后再做模糊匹配，不需要依赖数据库独有特性，适配性更强。
代码示例：

@Query("SELECT d FROM CarLocationEntityView d WHERE LOWER(d.externalId) LIKE CONCAT('%', LOWER(:carNameAndExternalId), '%') OR LOWER(d.carName) LIKE CONCAT('%', LOWER(:carNameAndExternalId), '%')")
List<CarLocationEntityView> findByCarNameAndExternalId(String carNameAndExternalId);

方案2：使用PostgreSQL原生ILIKE语法 #

如果你要使用PostgreSQL独有的ILIKE大小写不敏感模糊匹配语法，需要声明查询为原生SQL，同时注意字段名要和数据库表的实际字段名一致（通常为下划线命名格式）。
代码示例：

@Query(value = "SELECT * FROM car_location_entity_view d WHERE d.external_id ILIKE CONCAT('%', :carNameAndExternalId, '%') OR d.car_name ILIKE CONCAT('%', :carNameAndExternalId, '%')", nativeQuery = true)
List<CarLocationEntityView> findByCarNameAndExternalId(String carNameAndExternalId);

过往方案失效原因说明 #

• 第一种方案失效：你使用的latin1_general_cs是大小写敏感的排序规则，本身就不符合忽略大小写的需求；同时你把参数包裹在单引号内，会导致参数被识别为普通字符串而非动态传入值，且只给参数加排序规则未处理字段本身，因此不生效。
• 第二种方案失效：JPQL标准小写函数为LOWER而非low，且你仅对搜索参数做了小写转换，未对数据库字段carName做小写转换，两边大小写不统一无法匹配。
• 第三种方案失效：ILIKE是PostgreSQL原生SQL独有语法，你写的是JPQL查询，JPA默认不识别该语法，因此不生效，需要配合nativeQuery = true使用。

性能优化建议 #

如果数据量较大，可以给对应字段创建小写函数索引，避免全表扫描提升查询效率，SQL示例：

CREATE INDEX idx_car_external_id_low ON car_location_entity_view (LOWER(external_id));
CREATE INDEX idx_car_name_low ON car_location_entity_view (LOWER(car_name));

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