How to delete entity with JOINs using JPA and Hibernate

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.

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.