Re: [eclipselink-users] Creation of temporary tables for MySQL database

[Andrei Ilitchev <andrei.ilitchev@xxxxxxxxxx>]

>Like for other databases, is it possibe to use update all query for MySQL with a single sql query.
Unfortunately not - MySQL (unlike most other databases) can't handle the multiple table case, like:
UPDATE EMPLOYEE SET VERSION = (VERSION + ?), ADDR_ID = ?, MANAGER_EMP_ID = ? WHERE EXISTS(SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) AND t0.EMP_ID = EMPLOYEE.EMP_ID)

If you never use this case you can override in MySQLPlatform:
public boolean shouldAlwaysUseTempStorageForModifyAll() {
    return false;
}
and will get the same behavior in UpdateAll/DeleteAll as on other database platforms..

Thanks,
Andrei
On 21/07/2010 3:51 AM, ravikirann@xxxxxxxxxxx wrote:

Hi,

 

   I have a onetomany bidirectional relationship between Customer and Order beans(each customer has multiple orders).

I get an unexpected sql queries generated from a JPQL query in Eclipselink 1.1.1 for MySQL db only.

 

 

My JPQL query,

 

update orders_bidirectional o set o.name = 'ravi' where o.customer.id = :custid

 

 

 

SQL query generated for Oracle db,

 

UPDATE orders_bidirectional SET NAME = ? WHERE EXISTS(SELECT t1.ID FROM customer_bidirectional t0, orders_bidirectional t1 WHERE ((t0.ID = ?) AND (t0.ID = t1.CUSTOMER_ID)) AND t1.ID = orders_bidirectional.ID)

 

 

 

SQL queries generated for 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))
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

 

 

Like for other databases, is it possibe to use update all query for MySQL with a single sql query. Generating so many sql queries is

scalability issue for us.

 

 

regards,

ravi.

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users