Cascade delete problem on **custom** unidirectional @OneToMany relationship (JPA eclipselink)

Question

I'm having problem deleting an entity having an Unidirectional @OneToMany custom relationship. Here the relationship on the "base" entity (relevant columns only):

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "Id", updatable = false)
    protected Integer id;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "ObjectId", referencedColumnName = "Id")
    protected Collection<Attachment> attachmentsCollection;

Here the relevant columns on the "child" entity:

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "Id", updatable = false)
    protected Integer id;


    @NotNull
    @Basic(optional = false)
    @Size(min = 1, max = 64)
    @Column(name = "ObjectTable", updatable = false)
    protected String objectTable;


    @Basic(optional = false)
    @Column(name = "ObjectId", updatable = false)
    protected Integer objectId;


    @NotNull
    @Basic(optional = false)
    @Lob
    @Column(name = "Data")
    protected byte[] data;


    @NotNull
    @Basic(optional = false)
    @Column(name = "SizeInBytes")
    protected Long sizeInBytes;


    @NotNull
    @Basic(optional = false)
    @Size(min = 1, max = 128)
    @Column(name = "Name")
    protected String name;

Here explanation on why this is a custom relationship: The base object is the super class of all entities. It has, additional to its own id, the opportunity to associate any number of attachments via the attachments collection. Since Ids are not unique between tables (entities), it's needed to add an additional column in the child table (the attachment table). This additional column (ObjectTable) identifies the entity-kind owning the attachment. Adding this column to the entity'id (ObjectId) column, the relation is complete:

Suppose record 99 of entity Invoice has 2 attachments (attachment 'Z' and attachment 'Y'):

Table Invoice
-------------
  Id    ColumnA    ColumnB      ColumnC...
  99    'xyz'      '2343'       'zyx'
  .
  .


Table Attachment
----------------
  Id    ObjectTable  ObjectId   Data       SizeInBytes      Name
  43542 'Invoice'    99         11100110   437834           'Z.pdf'
  43543 'Invoice'    99         101110     867454           'Y.pdf'

I managed to load the relation with a mapping customizer:

    public    static final  String              TABLENAME                   = "TECAttachment";
    public    static final  String              OBJECTIDFIELDNAME           = TABLENAME + ".ObjectId";
    public    static final  String              OBJECTTABLEFIELDNAME        = TABLENAME + ".ObjectTable";


    // Customize how records are selecting inside entity's attachments collection: include the entities table name
    @Override
    public void customize(ClassDescriptor descriptor) {
        OneToManyMapping    mapping     = (OneToManyMapping)descriptor.getMappingForAttributeName(AttachmentEntitySessionCustomizer.ATTACHMENTSCOLLECTIONNAME);

        ExpressionBuilder   eb          = new ExpressionBuilder();
        Expression          eObjectIdNotNull    = eb.getField(AttachmentEntitySessionCustomizer.OBJECTIDFIELDNAME).notNull();
        Expression          eObjectId           = eb.getField(AttachmentEntitySessionCustomizer.OBJECTIDFIELDNAME).equal(eb.getParameter(descriptor.getPrimaryKeyFields().get(0)));
        Expression          eObjectTable        = eb.getField(AttachmentEntitySessionCustomizer.OBJECTTABLEFIELDNAME).equalsIgnoreCase(descriptor.getTableName());
        mapping.setSelectionCriteria(eObjectIdNotNull.and(eObjectId.and(eObjectTable)));
    }

... but I'm having problems during the delete operation of any entity. For a reason that I still don't understand, JPA is executing an update statement over the Attachment table without taking in consideration the ObjectTable column. Here is what's going on when I delete a record from the table PRHTABidParticipationItem:

Finest: Execute query DeleteObjectQuery(com.tec.uportal.prhta.model.bid.participation.PRHTABidParticipationItem[ id=24 ])
Finest: Execute query DataModifyQuery()
Fine: UPDATE TECAttachment SET ObjectId = ? WHERE (ObjectId = ?)
    bind => [null, 24]
Fine: DELETE FROM TECPRHTABidParticipationItem WHERE (Id = ?)
    bind => [24]
Finer: end unit of work flush
Finer: resume unit of work
Finer: begin unit of work commit
Finer: commit transaction

My problem is that the UPDATE statement over the table TECAttachment updates all records with the given Id and not only those related to the Entity TECPRHTABidParticipationItem. I think I must override the sql statements DeleteObjectQuery or DataModifyQuery but don't know how.

Any help will be really appreciated. I'm working with eclipselink-2.7.4

Thanks in advanace!

Answer 1

After a lot or searching, digging and reading I managed to solve my problem. Basically:

• My attachments collection is an unidirectional custom @OneToMany relationship.
• I was in the correct path trying to achieve my goal with a collection customizer (a class associated to my collection via annotation and implementing the DescriptorCustomizer interface).
• My customizer not only needs to customize the way the "child" records are selected, also it's needed to customize what to do when the parent record is deleted.
• The way to do this is to override the default removeAllTargetsQuery property providing a new custom query through the method mapping.setCustomRemoveAllTargetsQuery(DataModifyQuery).
• The most difficult part was to understand how the underlying eclipselink implementation sends parameters (which, order, type, etc.) to the custom DataModifyQuery. I had to download the source code of EclipseLink's JPA implementation and figure out how things are done...

Finally, everything is working good and ok thanks to the following simple DescriptorCustomizer:

package com.tec.uportal.model.customizer;

import org.eclipse.persistence.config.DescriptorCustomizer;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.expressions.Expression;
import org.eclipse.persistence.expressions.ExpressionBuilder;
import org.eclipse.persistence.internal.helper.DatabaseField;
import org.eclipse.persistence.mappings.OneToManyMapping;
import org.eclipse.persistence.queries.DataModifyQuery;



/**
 *
 * @author MarcB
 */
public class AttachmenstCollectionAttributeCustomizer implements DescriptorCustomizer {
    public    static final  String              ATTACHMENTS__COLLECTION_NAME                = "attachmentsCollection";
    public    static final  String              ATTACHMENTS_TABLE_OBJECT_TABLE__FIELD_NAME  = "ObjectTable";




    // Customize attachments collection mapping
    @Override
    public void customize(ClassDescriptor descriptor) {
        // Customize how records are selected inside parent entity's attachments collection: include in the WHERE clause the column ObjectTable
        OneToManyMapping    mapping     = (OneToManyMapping)descriptor.getMappingForAttributeName(ATTACHMENTS__COLLECTION_NAME);
        ExpressionBuilder   eb          = new ExpressionBuilder();
        Expression          eObjectId           = eb.getField(mapping.getTargetForeignKeyFields().get(0).getQualifiedName()).equal(eb.getParameter(descriptor.getPrimaryKeyFields().get(0)));
        Expression          eObjectTable        = eb.getField(mapping.getTargetForeignKeyFields().get(0).getTable().getQualifiedName() + "." + ATTACHMENTS_TABLE_OBJECT_TABLE__FIELD_NAME).equalsIgnoreCase(descriptor.getTable(descriptor.getTableName()).getQualifiedName());
        mapping.setSelectionCriteria(eObjectId.and(eObjectTable));


        // Customize what must be done (delete childs) when parent entity is deleted
        DataModifyQuery     dmf                 = new DataModifyQuery("DELETE " + mapping.getTargetForeignKeyFields().get(0).getTable().getQualifiedName() + " WHERE " + mapping.getTargetForeignKeyFields().get(0).getName() + " = #" + mapping.getTargetForeignKeyFields().get(0).getName() + " AND " + ATTACHMENTS_TABLE_OBJECT_TABLE__FIELD_NAME + " = '" + descriptor.getTableName() + "'");
        mapping.setCustomRemoveAllTargetsQuery(dmf);
    }
}