代码之家 › 专栏 › 技术社区 › Joseph Hwang

Ora-00933来自spring boot jpa的@query注释的错误

jpa spring-boot oracle

Joseph Hwang · 技术社区 · 2 年前

我使用spring-boot-jpa开发了带有Oracle数据库的菜单板。我面临来自JPA存储库的@Query注释的错误消息。亲爱的是jpa代码

@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)

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)

    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)

    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)

    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335)

    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)

    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)

    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)

    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)

    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)

    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)

    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)

    at jdk.proxy2/jdk.proxy2.$Proxy117.searchMenus(Unknown Source)

    at com.diet.service.MenuService.searchMenus(MenuService.java:27)

    at com.diet.controller.DietController.pagedweeklymenupan(DietController.java:49)

    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)

    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

    at java.base/java.lang.reflect.Method.invoke(Method.java:568)

    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:261)

    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:189)

    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)

    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:917)

    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:829)

    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)

    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)

    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)

    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)

    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903)

    at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564)

    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)

    at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)

    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:205)

    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149)

    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)

    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174)

    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149)

    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)

    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)

    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174)

    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149)

    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)

    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)

    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174)

    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149)

    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)

    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)

    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:174)

    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:149)

    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)

    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)

    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)

    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)

    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)

    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)

    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:340)

    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:391)

    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)

    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:896)

    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1744)

    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)

    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)

    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)

    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

    at java.base/java.lang.Thread.run(Thread.java:840)

我知道这个错误是oracle语法异常。但我不知道查询的哪些部分包含语法错误。如有任何回复,我们将不胜感激。顺致敬意,

1 回复 | 直到 2 年前

Littlefoot 2 年前

我不知道您使用的工具,但从Oracle的角度来看:

date 是保留字(用于 日期 数据类型),并且不能将其用作列别名;使用其他东西,例如。 c_date 就像在我的例子中一样。或者(不过我不建议这样做),将其括在双引号中: "date"
您没有发布表的描述,但使用的语法看起来可疑。列名可以(也应该!)前面有表的别名,但列名不能包含点(除非——再次——如果在创建表时将它们括在双引号中,但必须使用双引号和匹配字母大小写, 总是 ).

因此
```
No              : M.restaurant.id
(probably)   Yes: M.restaurant_id
(I doubt it) Yes: M."restaurant.id"
```

这可以可以(同样,取决于表的描述);我建议您先在Oracle中测试它,然后——如果它有效——将它移动到您的代码中:

SELECT
    m.ymd     AS c_date,                             --> here
    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 --> here
    JOIN commoncode  ccmt ON m.mealtype_id = ccmt.id --> here
    JOIN food        cf ON m.food_id = cf.id         --> here
    JOIN recipe      cr ON cf.id = cr.food_id        --> here
    JOIN ingredients ci ON cr.ingredients_id = ci.id --> here
WHERE
    m.ymd BETWEEN :startdate AND :enddate
    AND ( :restaurantid IS NULL
          OR m.restaurant_id = :restaurantid )       --> here
    AND ( :mealtypeid IS NULL
          OR :mealtypeid = 'M00'
          OR m.mealtype_id = :mealtypeid )           --> here
ORDER BY
    m.ymd,
    ccmt.id