search

hamidhtc / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

Can't use delete with spring nativeQuery in H2 database? #504

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
i have two tables/entites:

- **Award:** id,EventType
- **AwardReceived:** award_id,user_gid

i want to delete all awards for user where user_gid='someUserID' and award 
event type='someType' so here's what i tried:

    @Modifying
        @Query(nativeQuery = true, value = "delete ar from award_received ar join award aw on ar.award_id=aw.id where ar.user_gid=:user_gid and aw.EventType=:anniversaryEventType")
        public void deleteUserAnniversaryAwardReceiveds(
                @Param("user_gid") String user_gid,
                @Param("anniversaryEventType") String anniversaryEventType);

when i tried the exact samq sql in my database it works fine:

    delete ar from award_received ar join award aw on ar.award_id=aw.id where ar.user_gid='SomeID' and aw.EventType='SomeType'

JPA Relation in AwardReceived entity:

    @ManyToOne
    @JoinColumn(name = "award_id", nullable = false)
    private Award award;

the error i am getting when executing the spring jpa query is:

    org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete ar from award_received ar join award aw on ar.award_id=aw.id where ar.user_gid=? and aw.EventType=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:635)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:106)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.myapp.service.impl.AwardServiceImpl.generateAwards(AwardServiceImpl.java:201)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at test.myapp.web.controllers.AwardsTest.checkAwards(AwardsTest.java:130)
        at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
        at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
        at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
        at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
        at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
        at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
        at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
        at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
        at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
        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)
    Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:188)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:91)
        at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:196)
        at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1312)
        at org.hibernate.internal.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:401)
        at org.hibernate.ejb.QueryImpl.internalExecuteUpdate(QueryImpl.java:194)
        at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:99)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:155)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:55)
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:95)
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:85)
        at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:312)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
        ... 30 more
    Caused by: org.h2.jdbc.JdbcSQLException: Table "AR" not found; SQL statement:
    delete ar from award_received ar join award aw on ar.award_id=aw.id where ar.user_gid=? and aw.EventType=? [42102-172]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
        at org.h2.message.DbException.get(DbException.java:169)
        at org.h2.message.DbException.get(DbException.java:146)
        at org.h2.command.Parser.readTableOrView(Parser.java:4824)
        at org.h2.command.Parser.readTableOrView(Parser.java:4802)
        at org.h2.command.Parser.readSimpleTableFilter(Parser.java:713)
        at org.h2.command.Parser.parseDelete(Parser.java:735)
        at org.h2.command.Parser.parsePrepared(Parser.java:336)
        at org.h2.command.Parser.parse(Parser.java:279)
        at org.h2.command.Parser.parse(Parser.java:251)
        at org.h2.command.Parser.prepareCommand(Parser.java:218)
        at org.h2.engine.Session.prepareLocal(Session.java:425)
        at org.h2.engine.Session.prepareCommand(Session.java:374)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
        at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:70)
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:98)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
        ... 47 more

please advise how to fix it, thanks.

UPDATE: when i use the HQL query as follows:

    @Modifying
        @Query("delete from award_received ar where ar.user=:user and ar.award.eventType=:anniversaryEventType")
        public void deleteUserAnniversaryAwardReceiveds(@Param("user") User user,
                @Param("anniversaryEventType") String anniversaryEventType);

i am getting following exception:

    org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from myapp.award_received cross join myapp.Award award1_ where user_gid=? and EventType=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:635)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:106)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.myapp.service.impl.AwardServiceImpl.generateAwards(AwardServiceImpl.java:201)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at test.myapp.web.controllers.AwardsTest.checkAwards(AwardsTest.java:130)
        at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
        at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
        at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
        at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
        at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
        at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
        at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
        at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
        at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
        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)
    Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:188)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:91)
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:90)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:413)
        at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:282)
        at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1289)
        at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:116)
        at org.hibernate.ejb.QueryImpl.internalExecuteUpdate(QueryImpl.java:194)
        at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:99)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:155)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:55)
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:95)
        at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:85)
        at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:312)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
        ... 30 more
    Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "DELETE FROM myapp.AWARD_RECEIVED CROSS[*] JOIN myapp.AWARD AWARD1_ WHERE USER_GID=? AND EVENTTYPE=? "; SQL statement:
    delete from myapp.award_received cross join myapp.Award award1_ where user_gid=? and EventType=? [42000-172]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
        at org.h2.message.DbException.get(DbException.java:169)
        at org.h2.message.DbException.get(DbException.java:146)
        at org.h2.message.DbException.getSyntaxError(DbException.java:181)
        at org.h2.command.Parser.getSyntaxError(Parser.java:484)
        at org.h2.command.Parser.prepareCommand(Parser.java:233)
        at org.h2.engine.Session.prepareLocal(Session.java:425)
        at org.h2.engine.Session.prepareCommand(Session.java:374)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
        at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:70)
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:98)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
        ... 49 more

i think that this maybe H2/MYSQL issue.

Original issue reported on code.google.com by dev.mahm...@gmail.com on 3 Aug 2013 at 11:42

GoogleCodeExporter commented 9 years ago 
I'm sorry but H2 does not joins in a delete, and does not support cross join. 
For the supported syntax, see http://h2database.com/html/grammar.html

Original comment by thomas.t...@gmail.com on 3 Aug 2013 at 2:08

GoogleCodeExporter commented 9 years ago 
We are using h2 in our DAO unit test, while our production database is mysql. 
It is important for us to let h2 support the mysql syntax, as the alternative
"delete from A where exists (select B.id from B where b.name=? and 
b.a_id=A.id)" is really slow sometimes in Mysql. Mysql uses full table scan on 
table A here.

Any plan to support the Mysql syntax on delete/update across multi tables?

Original comment by jin.xing...@darcytech.com on 30 Jul 2014 at 10:31