how to make codes of composite key of oracle table?

Question

I try to use @query annotation with composite key in Oracle JPA. First I show you the entity classes and diagram

==== Menu.java

@Entity
@Data
@Table(name = "CMS_MENU")
@IdClass(MenuId.class)
public class Menu {

    @Id
    @Column(name = "YMD")
    private Date ymd;
    
    @Id
    @ManyToOne
    @JoinColumn(name="RESTAURANT_ID", referencedColumnName = "CODE_ID")
    private CommonCode restaurant;
    
    @Id
    @ManyToOne
    @JoinColumn(name="MEAL_ID", referencedColumnName = "CODE_ID")
    private CommonCode mealType;
    
    @Id
    @ManyToOne
    @JoinColumn(name="FOOD_ID")
    private Food food;
}

==== CommonCode.java

@Entity
@Data
@Table(name="CMS_COMMON_CODE")
public class CommonCode {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="CODE_ID")
    private String id;
    
    @Column(name="CODE_NM")
    private String name;

    @ManyToOne
    @JoinColumn(name="CODE_TYPE_ID", referencedColumnName = "CODE_TYPE_ID")
    private CommonCodeType commonCodeType;
}

== Table Diagram

Now I try to send SELECT query with @query annotation to Oracle database like belows

@Repository
public interface MenuRepository extends JpaRepository<Menu, Date>{
    
    @Query("SELECT " +
            "M.ymd AS c_date, " +
            "CCR.name AS restaurant, " +
            "CCMT.name AS meal_type, " +
            "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
                                );
}

But the ora-00933 exception is thrown with above select query statement like below,

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

The issue is I have no idea how to make codes of the composite key of menu entity and commoncode entity. Any idea?

== Updated Part==

My first attempt was that the return value type of searchMenus function was not Page<Object[]> but List type like below,

@Repository
public interface MenuRepository extends JpaRepository<Menu, Date>{

        @Query("SELECT " +
                "M.ymd AS c_date, " +
                "CCR.name AS restaurant, " +
                "CCMT.name AS meal_type, " +
                "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.name")
        List<Object[]> searchMenus(@Param("startDate")Date startDate,
                                    @Param("endDate")Date endDate,
                                    @Param("restaurantId")String restaurantId,
                                    @Param("mealTypeId")String mealTypeId
                                    );
    }

But the code worked without any exception, but I changed the java List type return value to Page type, then this ora-00933 error is thrown. Does these code changes bring the ora-00933 error? Any reply desperately !!

You've fixed the date reserved word but you still have typos with periods (e.g. restaurant.id instead of restaurant_id). Is this supposed to be a JPQL query or a native (Oracle SQL) query? I don't know how JPQL is converted - it would be helpful to see the actual SQL this generates and tries to run against the database. — Alex Poole
– Alex Poole, Commented Jan 27, 2024 at 19:04

Prateek · Accepted Answer · 2024-01-27 15:19:44Z

1

Composite Key in JPA are generally handeled using @Embeddable and @EmbeddedId.

Menu Entity migh will look something like below:

@Embeddable
public class MenuPk implements Serializable{

    @Column(name = "YMD")
    private Date ymd;

    @Column(name="RESTAURANT_ID")
    private String restaurantId;

    @Column(name="MEAL_ID")
    private String mealId;
    
    @Column(name="FOOD_ID")
    private Integer food;
}


@Entity
@Data
@Table(name = "CMS_MENU")
public class Menu {

    @EmbeddedId
    private MenuPk;
    
    @ManyToOne
    @JoinColumn(name="RESTAURANT_ID", referencedColumnName = "CODE_ID")
    private CommonCode restaurant;
    
    @ManyToOne
    @JoinColumn(name="MEAL_ID", referencedColumnName = "CODE_ID")
    private CommonCode mealType;
    
    @ManyToOne
    @JoinColumn(name="FOOD_ID")
    private Food food;
}

However, the error in the stackstrace shows [ORA-00933: SQL command not properly ended] [n/a]; SQL [n/a], which is a syntax error in the query, most probably be due to CCMT.id column added to the order by query but is missign in SELECT LIST.

answered Jan 27, 2024 at 15:19

Prateek

414 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joseph Hwang Over a year ago

Thanks for your reply. I change the CCMT.id column added to order by query to CCMT.name. But the same error, ora-00933 is thrown. Kindly check my updated part. When the return value of searchMenus function was List type, no error was thrown. But I change the List type to Page type, ora-00933 error is brought.

Collectives™ on Stack Overflow

how to make codes of composite key of oracle table?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related