Tuesday, February 24, 2009

openjpa set SupportsSubSelect

<property name="openjpa.jdbc.DBDictionary" value="mysql(SupportsSubselect=true)"/>

My jpql is like this:
DELETE FROM ChangeSetImpl cs WHERE cs.objectGroupId = ?1 AND 
(NOT EXISTS(SELECT gm FROM BusinessObjectGroupMemberImpl gm WHERE gm.groupId = ?1))


I hope it could generate the sql like this:
DELETE FROM TCHANGESET 
WHERE OBJECT_GROUP_ID = '0B89B168-3D84-AC82-0A49-C1044499C467' AND NOT (EXISTS (SELECT
t1.UIDPK FROM TOBJECTGROUPMEMBER t1 WHERE t1.OBJECT_GROUP_ID = '0B89B168-3D84-AC82-0A49-C1044499C467')) ;


But openjpa generate a complicated sql which does not work on MySql. We may need wait for the next version of openjpa.
Here is the sql generated by openjpa.
DELETE FROM TCHANGESET 
WHERE UIDPK IN (SELECT DISTINCT t2.UIDPK FROM TCHANGESET t2 WHERE ((SELECT COUNT(t1.UIDPK) FROM TOBJECTGROUPMEMBER t0,
TOBJECTGROUPMEMBER t1 WHERE (t0.OBJECT_GROUP_ID = ?)) = ? AND t2.OBJECT_GROUP_ID = ?))

No comments:

Post a Comment