Spring JPA搭配PostgreSQL时转义字符未加引号的问题排查

问题重现 #

从Spring Boot 2.7迁移至3.1后，Spring Data JPA Repository中定义的姓名模糊查询方法：

interface UserRepository : JpaRepository<UserEntity, String>{
   fun findByFirstNameContainingIgnoreCaseOrNameContainingIgnoreCase(
        firstNameSearchTerm: String,
        nameSearchTerm: String,
    ): List<UserEntity>
}

生成的SQL存在语法错误，其中escape \未用单引号包裹：

select
    u1_0.id,
    u1_0.first_name,
    u1_0.name
from
    users u1_0
where upper(u1_0.first_name) like upper(?) escape \
or upper(u1_0.name) like upper(?) escape \

PostgreSQL返回语法错误：

2023-10-02T12:16:06.997+02:00  WARN 98232 --- [nio-9595-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2023-10-02T12:16:07.310+02:00 ERROR 98232 --- [nio-9595-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "\"

原因 #

Spring Boot 3.1对应的Hibernate 6.x版本，在自动生成ContainingIgnoreCase类查询的SQL时，对escape字符的处理逻辑发生变化，未正确将默认的反斜杠escape字符用单引号包裹，不符合PostgreSQL的SQL语法规范。

解决方案 #

1. 配置Hibernate escape字符属性 #

在application.properties中添加以下配置，强制Hibernate生成带单引号的escape子句：

spring.jpa.properties.hibernate.query.like.escape_char='\\'

或在application.yml中配置：

spring:
  jpa:
    properties:
      hibernate:
        query:
          like:
            escape_char: '\'

配置后，Hibernate会自动生成escape '\'格式的合法SQL。

2. 手动编写@Query查询 #

跳过自动生成SQL的逻辑，直接在Repository方法上用@Query注解编写合法的查询语句：

interface UserRepository : JpaRepository<UserEntity, String> {
    @Query("""
        SELECT u FROM UserEntity u 
        WHERE UPPER(u.firstName) LIKE UPPER(CONCAT('%', :firstNameSearchTerm, '%')) ESCAPE '\'
        OR UPPER(u.name) LIKE UPPER(CONCAT('%', :nameSearchTerm, '%')) ESCAPE '\'
    """)
    fun findByFirstNameContainingIgnoreCaseOrNameContainingIgnoreCase(
        firstNameSearchTerm: String,
        nameSearchTerm: String
    ): List<UserEntity>
}

3. 升级Spring Boot版本 #

该问题属于Spring Boot 3.1早期版本的已知兼容性问题，升级到Spring Boot 3.1.5及以上版本（对应修复后的Hibernate版本），可自动解决SQL生成的语法问题。

验证 #

配置或修改后重启应用，执行查询，检查生成的SQL是否包含escape '\'，确认不再抛出语法错误异常。

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