调用JPA自定义查询函数时出现语法错误求助

我的Repository中有如下代码：

public interface TenantRepository extends JpaRepository<Tenant, UUID> {
    ...

    @Query("select substring(email from '@(.*)$') as domain from Tenant group by domain")
    public List<String> findAllDomain();
}

执行该查询时出现错误：

Syntax error in SQL statement "select substring(tenant0_.email, [*]from, '@(.*)ParseError: KaTeX parse error: Can't use function '\)' in math mode at position 2: '\̲)̲ as c…') as col_0_0_ from tenants tenant0_ group by col_0_0_

修改查询为：

"select substring(email from '@(.*)\\$') as domain from Tenant group by domain"

仍报错：

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select substring(tenant0_.email, [*]from, '@(.*)\\$') as col_0_0_ from tenants tenant0_ group by col_0_0_"; expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
select substring(tenant0_.email, from, '@(.*)\$') as col_0_0_ from tenants tenant0_ group by col_0_0_

该查询在PgAdmin4中可正常运行，求解决建议。

• 核心原因：你使用的是PostgreSQL专属的substring(string from pattern)语法，但H2数据库不支持该格式，两者的SUBSTRING函数语法规则存在差异，导致解析报错。

• 方案1：用JPQL标准函数实现跨库兼容
  改用JPQL通用的LOCATE函数定位@的位置，再截取后续内容，这种写法在PostgreSQL和H2中都能正常运行：

  @Query("select SUBSTRING(t.email, LOCATE('@', t.email) + 1) as domain from Tenant t group by domain")
  public List<String> findAllDomain();
  

• 方案2：针对不同数据库编写专属原生查询
  利用Spring Data JPA的原生查询特性，结合数据库方言区分实现：

  1. 针对PostgreSQL的原生查询：

     @Query(value = "select substring(email from '@(.*)$') as domain from tenant group by domain", nativeQuery = true)
     public List<String> findAllDomainPg();
     

  2. 针对H2的原生查询：

     @Query(value = "select substring(email, locate('@', email) + 1) as domain from tenant group by domain", nativeQuery = true)
     public List<String> findAllDomainH2();
     

  也可以通过动态语句适配不同方言：

  @Query(value = """
      SELECT CASE WHEN '#{#entityManager.dialect.name}' LIKE 'PostgreSQL%' 
                  THEN substring(email from '@(.*)$') 
                  ELSE substring(email, locate('@', email) + 1) 
             END as domain 
      FROM tenant 
      GROUP BY domain
      """, nativeQuery = true)
  public List<String> findAllDomain();
  

• 方案3：开启H2的PostgreSQL兼容模式
  在H2数据库的连接URL中添加MODE=PostgreSQL参数，让H2模拟PostgreSQL的语法规则，这样就能直接运行原有的PG风格查询：

  jdbc:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1
  

  注意：该模式下H2并非完全兼容PG所有语法，复杂查询可能仍存在适配问题，但简单的substring from语法可正常解析。

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