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.

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]

This exception is caused, because in MySQL you can not modify/delete the same table which you use in the SELECT part. Here documented this behaviour https://dev.mysql.com/doc/refman/5.6/en/update.html. The solutions 1) nesting the subquery deeper into a from clause or 2) join the table to itself