Community
Participate
Working Groups
Build Identifier: 1.1.1 When we try to use JPQL update all query in a one to many bidirectional relationship, we see temporary table being created and deleted and some inserts happening with specifically for MySQL database, whereas it seems to work fine with other databases with a single query. Is is it possible to avoid such creation of temporary tables and with a single sql query using JPQL. Creation of a temporary table is scalability issue. We get the queries something like this when we use MySQL db, CREATE TEMPORARY TABLE IF NOT EXISTS TL_orders_bidirectional LIKE orders_bidirectional INSERT INTO TL_orders_bidirectional (ID, NAME) SELECT t1.ID, ? FROM customer_bidirectional t0, orders_bidirectional t1 WHERE ((t0.ID = ?) AND (t0.ID = t1.CUSTOMER_ID)) bind => [9, 1] UPDATE orders_bidirectional, TL_orders_bidirectional SET orders_bidirectional.NAME = TL_orders_bidirectional.NAME WHERE orders_bidirectional.ID = TL_orders_bidirectional.ID DELETE FROM TL_orders_bidirectional Instead why can't we generate something like this, UPDATE orders_bidirectional o ,customer_bidirectional c set o.Name='12' where c.ID='1' and c.id = o.customer_id; Reproducible: Always Steps to Reproduce: 1.Create a one to many bidirectional relationship. 2.Fire an update all JPQL query on MySQL database 3.Test case is attached
Created attachment 173770 [details] MySQL temporary table test case.
Any updates regarding this issue, we have an urgent requirement in our application. Is there a way to avoid creation of temporary tables and use update all JPQL query.
Certain types of update and delete all statements (for instance for Entities mapped to multiple tables) require SQL that is more complex than a standard Update or Delete SQL statement - since multiple tables have to be somehow referenced and Update and Delete SQL statements only allow one table. We address this issue in one of two ways. 1. We use a Subquery in the Update or Delete statement. We do this for all databases that allowed the subquery at the time we wrote the feature. 2. If Subqueries are not allowed in Update or Delete statements, but temporary tables are allowed, we use temporary tables. That is what is happening with MySQL. In your example the reason multiple tables are needed is because your where clause navigates from Order to Customer. I am closing this bug as Invalid, but feel free to reopen if you can suggest a single line Update statement that works on MySQL.
As already mentioned why can't we generate a sql query something like below, instead of using temporary tables. This works fine on MySQL platform. Using temporary tables is not an option as it will lead to performance issues. UPDATE CUSTOMER_BIDIRECTIONAL t0, ORDERS_BIDIRECTIONAL t1 SET t1.NAME='3' WHERE ((t0.ID = '1') AND (t0.ID = t1.CUSTOMER_ID))
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
