I develop menu board with Oracle database using spring boot jpa. I face the error messages from the @Query annotation of JPA repository. Belows are the jpa codes
@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);
}
The error message is
[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)
I know this error is oracle syntax exception. But I have no idea which parts of query contains syntax error. Your any reply will be grateful. Best regards
I don't know tools you use, but - from Oracle's perspective:
date is reserved word (for date datatype) and you can't use it as a column alias; use something else, e.g. c_date as in my example. Alternatively (I wouldn't suggest it, though), enclose it into double quotes: "date"
you didn't post tables' description, but syntax you used looks suspicious. Column names can (and should!) be preceded by table's alias, but column names can't contain dot (unless - again - if you enclosed them into double quotes while creating table, but then you have to use double quotes and match letter case, always).
Therefore:
No : M.restaurant.id
(probably) Yes: M.restaurant_id
(I doubt it) Yes: M."restaurant.id"
This might be OK (again, depending on tables' description); I suggest you first test it in Oracle and then - if it works - move it into your code:
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