Query syntax error when bulk-deleting in Hibernate.

Question

I try to executer bulk delete using Hibernate HQL query, following reference manunal but I get a query syntax error. This line

final Query qry = getSession(false).createQuery(" delete from NewCalendarDay");

causes exception:

org.hibernate.QueryException: query must begin with SELECT or FROM: delete [ delete from pl.com.bms.avaro.staticData.model.NewCalendarDay ]

Class in which the code is executed extends org.springframework.orm.hibernate3.support.HibernateDaoSupport, so getSession() method should return org.hibernate.Session. I use spring v. 2.5.5 and Hibernate v. 3.2.6.ga

Should I configure something to be able to bulk-delete or what?

EDIT
Bozho asked for complete stack trace, so here it goes:

org.hibernate.QueryException: query must begin with SELECT or FROM: delete [ delete from pl.com.bms.avaro.staticData.model.NewCalendarDay ]
    at org.hibernate.hql.classic.ClauseParser.token(ClauseParser.java:83)
    at org.hibernate.hql.classic.PreprocessingParser.token(PreprocessingParser.java:108)
    at org.hibernate.hql.classic.ParserHelper.parse(ParserHelper.java:28)
    at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:216)
    at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:185)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
    at pl.com.bms.avaro.staticData.dao.implementations.NewCalendarDayDaoImpl.deleteCalendarDaysForYear(NewCalendarDayDaoImpl.java:41)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy43.deleteCalendarDaysForYear(Unknown Source)
    at pl.com.bms.avaro.staticData.dao.tests.TestCalendarDayDaoImpl.testFindByDateCal(TestCalendarDayDaoImpl.java:60)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at junit.framework.TestCase.runTest(TestCase.java:168)
    at junit.framework.TestCase.runBare(TestCase.java:134)
    at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
    at junit.framework.TestResult$1.protect(TestResult.java:110)
    at junit.framework.TestResult.runProtected(TestResult.java:128)
    at junit.framework.TestResult.run(TestResult.java:113)
    at junit.framework.TestCase.run(TestCase.java:124)
    at junit.framework.TestSuite.runTest(TestSuite.java:232)
    at junit.framework.TestSuite.run(TestSuite.java:227)
    at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:91)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Answer 1

Get rid of the leading whitespace for a start. Then execute the query using query.executeUpdate()

Then, even if this works for you, I wouldn't advice using HQL to delete entities. In case they have any relations, these will not be cascaded and you will end up with inconsistencies.

Select all objects and call session.remove(entity) on each. Of course, if you don't have any x-to-x relationships, you can proceed with the HQL, but be careful if you add such in the future.

Update: you might be having something like this in your hibernate configurations:

<prop
 key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory
</prop>

It makes Hibernate use the classic parser. Remove it and try again.