hql query to delete records in two tables

2
votes

My query is.

Query query1 = session.createQuery(
                "DELETE Question, Answer FROM Question que LEFT JOIN Answer ans ON que.id=ans.questionId  WHERE que.quiz_type_id=:qtypeid");
        query1.setParameter("qtypeid", id);
        query1.executeUpdate();

This Query is not working..Please help...

question table

answer table

HTTP Status 500 - Request processing failed; nested exception is org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 38 [DELETE com.online.test.model.Question, Answer FROM com.online.test.model.Question que LEFT JOIN Answer ans ON que.id=ans.questionId WHERE que.quiz_type_id=:qtypeid]

type Exception report

message Request processing failed; nested exception is org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 38 [DELETE com.online.test.model.Question, Answer FROM com.online.test.model.Question que LEFT JOIN Answer ans ON que.id=ans.questionId WHERE que.quiz_type_id=:qtypeid]

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 38 [DELETE com.online.test.model.Question, Answer FROM com.online.test.model.Question que LEFT JOIN Answer ans ON que.id=ans.questionId WHERE que.quiz_type_id=:qtypeid] org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852) javax.servlet.http.HttpServlet.service(HttpServlet.java:618) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) javax.servlet.http.HttpServlet.service(HttpServlet.java:725) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

root cause

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 38 [DELETE com.online.test.model.Question, Answer FROM com.online.test.model.Question que LEFT JOIN Answer ans ON que.id=ans.questionId WHERE que.quiz_type_id=:qtypeid] org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91) org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109) org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304) org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203) org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158) org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:126) org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:88) org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190) org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301) org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800) com.online.test.Dao.AdminTestDaoImpl.removeQuiz(AdminTestDaoImpl.java:104) com.online.test.service.AdminTestServicesImpl.removeQuiz(AdminTestServicesImpl.java:58) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:606) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) com.sun.proxy.$Proxy35.removeQuiz(Unknown Source) com.online.test.AdminController.removecountry(AdminController.java:59) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:606) org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852) javax.servlet.http.HttpServlet.service(HttpServlet.java:618) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) javax.servlet.http.HttpServlet.service(HttpServlet.java:725) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

note The full stack trace of the root cause is available in the Apache Tomcat/8.0.9 logs. Apache Tomcat/8.0.9

2
hibernatehqlhql-delete
Can you please post some more details? Do you have an error? If so, what error?, ... – Aleksej
please post you error message and the setting parameter to qtypeid – Musaddique
"DELETE FROM Question que LEFT JOIN Answer ans ON que.id=ans.questionId WHERE que.quiz_type_id=:qtypeid"); – Musaddique
look into this... same question stackoverflow.com/questions/1233451/… – Musaddique

2 Answers

5
votes

Joins are not supported in Hibernate bulk DML operations. From the documentation:

No Section 16.4, “Forms of join syntax”, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.

You can use two delete statements to delete answers and questions separately.

2
votes

You can't use on with join in the HQL. And you can't use join for delete, as @DraganBozanovic suggested. The valid query can be

DELETE FROM Question que WHERE que.quiz_type_id = :qtypeid

To delete answers when you delete a corresponding question you need to have annotation properties — cascade = CascadeType.ALL, orphanRemoval = true

If you use one-to-many association it can be

@OneToMany(mappedBy = "question", cascade = CascadeType.ALL, orphanRemoval = true)
private List<Answer> answers;

Instead of using CascadeType.ALL you can consider to use more specifically cascade.