Spring Boot JPA @Query注解报ORA-00933错误求助

问题描述 #

使用Spring Boot JPA对接Oracle数据库开发菜单看板时，在JPA Repository的@Query注解中编写查询语句触发ORA-00933错误（提示SQL命令未正确结束）。

相关代码 #

@Repository
public interface MenuRepository extends JpaRepository<Menu, Date>{
    
    @Query("SELECT " +
            "M.ymd AS date, " +
            "CCR.name AS restaurant, " +
            "CCMT.name AS mealType, " +
            "CF.name AS meal, " +
            "CI.name AS ingredient " +
            "FROM " +
            "Menu M " +
            "JOIN " +
            "CommonCode CCR ON M.restaurant.id = CCR.id " +
            "JOIN " +
            "CommonCode CCMT ON M.mealType.id = CCMT.id " +
            "JOIN " +
            "Food CF ON M.food.id = CF.id " +
            "JOIN " +
            "Recipe CR ON CF.id = CR.food.id " +
            "JOIN " +
            "Ingredients CI ON CR.ingredients.id = CI.id " +
            "WHERE M.ymd BETWEEN :startDate AND :endDate " +
            "AND (:restaurantId IS NULL OR M.restaurant.id = :restaurantId) " +
            "AND (:mealTypeId IS NULL OR :mealTypeId = 'M00' OR M.mealType.id = :mealTypeId) " +
            "ORDER BY M.ymd, CCMT.id")
    Page<Object[]> searchMenus(@Param("startDate") Date startDate,
                               @Param("endDate") Date endDate,
                               @Param("restaurantId") String restaurantId,
                               @Param("mealTypeId") String mealTypeId,
                               Pageable pageable);
}

错误栈信息 #

[ORA-00933: SQL command not properly ended] [n/a]; SQL [n/a]

org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select m1_0.ymd,cc1_0.code_nm,cc2_0.code_nm,f1_0.food_nm,i1_0.ingredients_nm from cms_menu m1_0 join cms_common_code cc1_0 on m1_0.restaurant_id=cc1_0.code_id join cms_common_code cc2_0 on m1_0.meal_id=cc2_0.code_id join cms_food f1_0 on m1_0.food_id=f1_0.food_id join cms_recipe r2_0 on f1_0.food_id=r2_0.food_id join cms_ingredients i1_0 on r2_0.ingredients_id=i1_0.ingredients_id where m1_0.ymd between ? and ? and (? is null or m1_0.restaurant_id=?) and (? is null or ?='M00' or m1_0.meal_id=?) order by m1_0.ymd,cc2_0.code_id offset ? rows fetch first ? rows only] 

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:277)
    ...

排查与解决 #

核心问题定位 #

错误根源是JPQL中使用了Oracle保留关键字date作为别名，Oracle会将AS date中的date识别为关键字而非别名，导致SQL语法解析失败，触发ORA-00933错误。

另外需注意：OFFSET ... FETCH FIRST ... ONLY是Oracle 12c及以上版本支持的分页语法，若你的Oracle版本低于12c，Hibernate生成的分页语句也会报错，但结合当前错误信息，关键字冲突是更直接的触发原因。

修复步骤 #

1. 修改别名，规避保留关键字
   将JPQL中M.ymd AS date的别名date替换为非关键字（比如menuDate）：

   @Query("SELECT " +
           "M.ymd AS menuDate, " + // 替换原date别名
           "CCR.name AS restaurant, " +
           "CCMT.name AS mealType, " +
           "CF.name AS meal, " +
           "CI.name AS ingredient " +
           "FROM " +
           "Menu M " +
           "JOIN " +
           "CommonCode CCR ON M.restaurant.id = CCR.id " +
           "JOIN " +
           "CommonCode CCMT ON M.mealType.id = CCMT.id " +
           "JOIN " +
           "Food CF ON M.food.id = CF.id " +
           "JOIN " +
           "Recipe CR ON CF.id = CR.food.id " +
           "JOIN " +
           "Ingredients CI ON CR.ingredients.id = CI.id " +
           "WHERE M.ymd BETWEEN :startDate AND :endDate " +
           "AND (:restaurantId IS NULL OR M.restaurant.id = :restaurantId) " +
           "AND (:mealTypeId IS NULL OR :mealTypeId = 'M00' OR M.mealType.id = :mealTypeId) " +
           "ORDER BY M.ymd, CCMT.id")
   

2. （可选）适配低版本Oracle分页
   若你的Oracle版本低于12c，需在配置文件中指定适配旧版分页的方言：

   # application.properties
   spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
   

   或YAML格式：

   # application.yml
   spring:
     jpa:
       properties:
         hibernate:
           dialect: org.hibernate.dialect.Oracle10gDialect
   

验证 #

修改后重新运行程序，ORA-00933错误应会消失，分页查询可正常执行。

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