Spring JPA @Query使用OVERLAPS报unexpected token错误

Spring Boot 2.7.1内置的Hibernate 5.6版本的HQL/JPQL ANTLR解析器，本身不支持PostgreSQL专属的OVERLAPS区间操作关键字。语句在应用启动阶段做语法校验时就会抛出解析错误，根本不会走到数据库执行环节，和SQL本身在PostgreSQL中能否正常运行无关。

方案1：使用通用区间重叠逻辑改写HQL（最推荐，无兼容问题） #

两个时间区间重叠的判断逻辑是通用的，完全不需要依赖数据库专属语法。对于区间A [startA, endA]和区间B [startB, endB]，重叠的充要条件是startA <= endB AND endA >= startB，直接用标准比较运算符改写即可，HQL原生支持该写法：

@Query("SELECT f FROM Formation f WHERE " +
       "make_date(f.startingYear, 1, 1) <= make_date(:endingYear, 1, 1) " +
       "AND make_date(f.endingYear, 1, 1) >= make_date(:startingYear, 1, 1)")
List<Formation> findAllMatchingFormations(@Param("startingYear") int startingYear, @Param("endingYear") int endingYear);

注意：HQL是面向实体对象的查询语言，FROM后要写实体类名Formation而非数据库表名formations，字段名也要对应实体类的驼峰属性名，而非数据库下划线列名，这也是HQL编写的常见错误。
该写法完全符合JPQL规范，Hibernate可以正常解析生成SQL，在PostgreSQL中的执行效率和OVERLAPS写法完全一致，不需要任何额外配置。

方案2：正确编写原生SQL（需保留OVERLAPS语法时使用） #

如果一定要使用PostgreSQL的OVERLAPS语法，原生查询不需要额外配置SqlResultSetMapping，只要保证查询返回字段和实体类映射字段完全匹配即可，正确写法如下：

@Query(value = "SELECT f.* FROM formations f WHERE " +
               "(make_date(f.starting_year, 1, 1), make_date(f.ending_year, 1, 1)) " +
               "OVERLAPS (make_date(:startingYear, 1, 1), make_date(:endingYear, 1, 1))",
       nativeQuery = true)
List<Formation> findAllMatchingFormations(@Param("startingYear") int startingYear, @Param("endingYear") int endingYear);

之前使用原生查询出现适配问题，通常是两个错误导致的：

• 条件外层套了多余的SELECT (...)子查询，部分PostgreSQL JDBC驱动版本解析结果集元数据时会出现映射异常
• 查询没有返回实体类映射需要的全部字段，或是字段名和数据库实际列名不匹配
  只要保证用SELECT f.*查询对应表的所有映射列，Spring Data JPA会自动完成结果到实体对象的转换，不需要额外映射配置。

方案3：扩展Hibernate方言支持OVERLAPS关键字（多场景复用适用） #

如果项目中多处需要使用OVERLAPS操作，可以通过自定义Hibernate方言注册专属函数，让HQL直接识别该操作：

1. 自定义PostgreSQL方言类：

import org.hibernate.dialect.PostgreSQL10Dialect;
import org.hibernate.type.BooleanType;

public class CustomPostgreSQLDialect extends PostgreSQL10Dialect {
    public CustomPostgreSQLDialect() {
        super();
        registerFunction("overlaps", new SQLFunctionTemplate(BooleanType.INSTANCE, "(?1, ?2) OVERLAPS (?3, ?4)"));
    }
}

2. 在配置文件中指定使用自定义方言：

spring:
  jpa:
    properties:
      hibernate:
        dialect: com.yourproject.config.CustomPostgreSQLDialect

配置完成后就可以在HQL中直接调用注册的overlaps函数：

@Query("SELECT f FROM Formation f WHERE " +
       "overlaps(make_date(f.startingYear,1,1), make_date(f.endingYear,1,1), " +
       "make_date(:startingYear,1,1), make_date(:endingYear,1,1)) = true")
List<Formation> findAllMatchingFormations(@Param("startingYear") int startingYear, @Param("endingYear") int endingYear);

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