How to delete entity with JOINs using JPA and Hibernate

Question

I have following entities:

I want to delete WordSet by id and username of the user using JPA.

Here is entity declarations:

User

@Entity
@Data
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
public class User extends AbstractModelClass {

    private String name;
    private String username;
    private String password;
    private String email;

    @ManyToMany(fetch = FetchType.EAGER)
    private Set<Role> roles;
}

UserDictionary

@Entity
@Data
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
public class UserDictionary extends AbstractModelClass {

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "userDictionary")
    private Set<WordSet> wordSets;

    @OneToOne(cascade = CascadeType.ALL)
    private User user;

    @PrePersist
    private void addDictionaryToWordSets() {
        wordSets.forEach(wordSet -> wordSet.setUserDictionary(this));
    }
}

WordSet

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = true, exclude = {"studiedWords", "userDictionary"})
@ToString(callSuper = true, exclude = {"studiedWords", "userDictionary"})
public class WordSet extends AbstractModelClass {

    @NotBlank
    private String name;

    @NotBlank
    private String description;

    @ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
    private List<StudiedWord> studiedWords;

    @ManyToOne(fetch = FetchType.EAGER)
    private UserDictionary userDictionary;

    public WordSet(String name, String description, List<StudiedWord> studiedWords) {
        this.name = name;
        this.description = description;
        this.studiedWords = studiedWords;
    }

    public WordSet(Long id, String name, String description, List<StudiedWord> studiedWords) {
        super(id);
        this.name = name;
        this.description = description;
        this.studiedWords = studiedWords;
    }

    @PreRemove
    private void removeFromUserDictionary() {
        userDictionary.getWordSets().removeIf(this::equals);
    }
}

Here is part of my WordSetDao:

@Override
public Optional<WordSet> findByIdAndUsername(long id, String username) {
    return findOrEmpty(() -> entityManager.createQuery(
            "SELECT w FROM WordSet w " +
                    "WHERE w.userDictionary.user.username = :username AND w.id = :id", WordSet.class)
                                          .setParameter("id", id)
                                          .setParameter("username", username)
                                          .getSingleResult());
}

@Override
public void deleteByIdAndUsername(long id, String username) {
    entityManager.createQuery(
            "DELETE FROM WordSet w WHERE w.userDictionary.user.username = :username AND w.id = :id")
                 .setParameter("id", id)
                 .setParameter("username", username)
                 .executeUpdate();
}

findByIdAndUsername works fine, but delete throws following exception:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [/* delete FKs in join table */ delete from word_set_studied_words where (word_set_id) in (select id from word_set where username=? and id=?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
    at org.springframework.web.servlet.FrameworkServlet.doDelete(FrameworkServlet.java:894)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:654)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:65)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:167)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:134)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:115)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:169)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at io.github.solomkinmv.glossary.web.security.auth.JwtTokenAuthenticationProcessingFilter.successfulAuthentication(JwtTokenAuthenticationProcessingFilter.java:58)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:240)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:121)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:134)
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:155)
    at io.github.solomkinmv.glossary.web.controller.WordSetControllerTest.deleteWordSet(WordSetControllerTest.java:236)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.restdocs.JUnitRestDocumentation$1.evaluate(JUnitRestDocumentation.java:55)
    at org.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [/* delete FKs in join table */ delete from word_set_studied_words where (word_set_id) in (select id from word_set where username=? and id=?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
    at io.github.solomkinmv.glossary.persistence.dao.impl.WordSetJpaDao$$EnhancerBySpringCGLIB$$48825a47.deleteByIdAndUsername(<generated>)
    at io.github.solomkinmv.glossary.service.domain.impl.WordSetServiceImpl.deleteByIdAndUsername(WordSetServiceImpl.java:128)
    at io.github.solomkinmv.glossary.service.domain.impl.WordSetServiceImpl$$FastClassBySpringCGLIB$$c3da160a.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
    at io.github.solomkinmv.glossary.service.domain.impl.WordSetServiceImpl$$EnhancerBySpringCGLIB$$4b4a0f55.deleteByIdAndUsername(<generated>)
    at io.github.solomkinmv.glossary.web.controller.WordSetController.deleteWordSet(WordSetController.java:79)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:220)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:134)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    ... 70 more
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:78)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:78)
    at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:107)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:429)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:374)
    at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1348)
    at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:102)
    at org.hibernate.jpa.internal.QueryImpl.internalExecuteUpdate(QueryImpl.java:405)
    at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:61)
    at io.github.solomkinmv.glossary.persistence.dao.impl.WordSetJpaDao.deleteByIdAndUsername(WordSetJpaDao.java:50)
    at io.github.solomkinmv.glossary.persistence.dao.impl.WordSetJpaDao$$FastClassBySpringCGLIB$$3577673c.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 98 more
Caused by: org.h2.jdbc.JdbcSQLException: Column "USERNAME" not found; SQL statement:
/* delete FKs in join table */ delete from word_set_studied_words where (word_set_id) in (select id from word_set where username=? and id=?) [42122-193]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:147)
    at org.h2.expression.Comparison.optimize(Comparison.java:178)
    at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:130)
    at org.h2.command.dml.Select.prepare(Select.java:856)
    at org.h2.engine.Session.optimizeQueryExpression(Session.java:233)
    at org.h2.expression.ConditionInSelect.optimize(ConditionInSelect.java:117)
    at org.h2.command.dml.Delete.prepare(Delete.java:131)
    at org.h2.command.Parser.prepareCommand(Parser.java:259)
    at org.h2.engine.Session.prepareLocal(Session.java:561)
    at org.h2.engine.Session.prepareCommand(Session.java:502)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1203)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:287)
    at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
    at com.sun.proxy.$Proxy87.prepareStatement(Unknown Source)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:87)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
    ... 113 more

Also, instead of

DELETE FROM WordSet w WHERE w.userDictionary.user.username = :username AND w.id = :id

I've tried following query:

DELETE FROM WordSet w 
WHERE w IN 
    (SELECT ws FROM UserDictionary u 
     JOIN u.wordSets ws 
     WHERE u.user.username = :username AND ws.id = :id)

And it works on my H2 database, but fails on MySQL with following exception:

java.sql.SQLException: You can't specify target table 'word_set' for update in FROM clause
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.40.jar!/:5.1.40]
    ...

I've googled about subqueries and MySQL (one, two), but couldn't find out how to make such trick with JPA.

So, how to delete wordset? Is there something wrong with my entity mapping?

Here is my DB schema, it looks correct:

Answer 1

There are some many things that are wrong in this mapping:

1. Using EAGER fetching for every collection type is even more of an issue than your current problem. You are practically fetching the whole DB with any given query. Switching to FetchType.LAZY is better.

2. You said it works for H2, but that's not true:

    Caused by: org.h2.jdbc.JdbcSQLException: Column "USERNAME" not found; SQL statement:
    /* delete FKs in join table */ delete from word_set_studied_words where (word_set_id) in (select id from word_set where username=? and id=?) [42122-193]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
   

3. This type of query is not supported by MySQL:

    DELETE FROM WordSet w 
    WHERE w IN 
    (SELECT ws FROM UserDictionary u 
     JOIN u.wordSets ws 
     WHERE u.user.username = :username AND ws.id = :id)
   

4. Joins are prohibited in bulk statements as explained in the Hibernate User Guide.

5. Now, depending on the number of entries you want to delete, a batch delete might be a better approach since you can prevent lost update anomaly.

6. You have a graph of interconnected entities here, so bulk delete might not help you anyway because you can't delete a parent record without deleting the child associations first.

Answer 2

Have you attempted a regular join in the delete? There is a similar question here: Delete with Join in MySQL

...although not via JPA, but there is an accepted answer which shows and example of a join based delete, but it seems MySQL specific.

The trick is to specify the actual table in the delete clause itself, not just the from clause when you have other tables referenced via JOINs.

I'm not setup to test JPA against mysql at the moment. JQL might not be able to accommodate this, but you could probably do it with a native query.