max-keys-in-delete

since 3.2.8SP1, 4.0.4GA

This optional element was added to type-mapping element of jbosscmp-jdbc.xml as a fix for 'JBAS-1190 Problem deleting relations'.

When a many-to-many relationship is destroyed and removed from the database a DELETE statement of the following form is issued:

DELETE FROM relation_table WHERE (key1=? AND key2=?) [OR (key1=? AND key2=?)]

Where the (key1=? AND key2=?) is repeated as many times as many relationships where destroyed.

Sometimes the number of relationships destroyed is too large and some databases cannot handle the long DELETE statement. In this case, max-keys-in-delete can be used to limit the number of key pairs in the statement.

E.g.

      <type-mapping>
         <name>myTypeMapping</name>
         ...
         <max-keys-in-delete>2</max-keys-in-delete>

Having max-keys-in-delete 2 and 5 relationships to delete, JBossCMP will issue the following 3 statements

14:43:09,488 DEBUG [A] Executing SQL: DELETE FROM AB WHERE (a_id=? AND b_id=?) OR (a_id=? AND b_id=?)
14:43:09,488 DEBUG [A] Rows affected = 2
14:43:09,488 DEBUG [A] Executing SQL: DELETE FROM AB WHERE (a_id=? AND b_id=?) OR (a_id=? AND b_id=?)
14:43:09,488 DEBUG [A] Rows affected = 2
14:43:09,488 DEBUG [A] Executing SQL: DELETE FROM AB WHERE (a_id=? AND b_id=?)
14:43:09,488 DEBUG [A] Rows affected = 1

Note, the element is optional and its absence or value of zero will not set any limit on the number of key pairs in the statement.