Soft delete cascade with Eclipselink DescriptorCustomizer

Question

Dear fellow programmers I have come to you for asistance with the following:

I am using EclipseLink as the ORM for my application and am trying to implement Soft Delete on one of my entities (Article) and cascade delete these articles when a Catagory with a OneToMany relationship is deleted.

When I delete an Article everything works as expected but when I delete a Category which has n Articles I get an Exception. I am using a DescriptorCustomizer to accomplish the Soft Deletes.

The DescriptorCustomizer (see org.eclipse.persistence.config.DescriptorCustomizer) with the following implementation:

public class ArticleCustomizer implements DescriptorCustomizer {

    @Override
    public void customize(ClassDescriptor classDescriptor) throws Exception {
       classDescriptor.getQueryManager().setDeleteSQLString("UPDATE article SET ACTIVE = '0' WHERE id = #id");
    }
}

The entities I'm trying to save:

AbstractItem

@Entity
@Table(name = "item")
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class AbstractItem extends BaseEntity {
    @ManyToOne
protected Category category;
}

Article

@Entity
@DiscriminatorValue("article")
@Customizer(value=ArticleCustomizer.class)
public class Article extends AbstractItem {

}

Category

@Entity
@Customizer(value=CategoryCustomizer.class)
public class Category extends BaseEntity {

    @OneToMany(cascade = CascadeType.REMOVE)
    @JoinTable(name = "category_items", joinColumns = @JoinColumn(name = "category_id"), inverseJoinColumns = @JoinColumn(name = "item_id"))
    protected List<AbstractItem> items = new ArrayList<AbstractItem>();

    @OneToMany(cascade = CascadeType.REMOVE)
    @JoinTable(name = "category_subcategories", joinColumns = @JoinColumn(name = "category_id"), inverseJoinColumns = @JoinColumn(name = "parent_category_id"))
    protected List<Category> categories = new ArrayList<Category>();
}

The error when I try to cascade delete a Category which has n Articles:

Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'ACTIVE' in 'field list'
Error Code: 1054
Call: UPDATE article SET ACTIVE = '0' WHERE id = ?
bind => [null]

I can see the id was bound to null, however I do not understand why. Can someone please guide me in the right direction?

1. Eclipselink version: 2.4.0
2. Spring: 3.1.2RELEASE

Thank you!

Answer 1

I'm sorry to have waisted your time. The solution was right in front of me:

classDescriptor.getQueryManager().setDeleteSQLString("UPDATE item SET ACTIVE = '0' WHERE id = #ID");

The correct table for the customizer should have been ITEM instead of ARTICLE. Strangely enough it worked for single article deletes and did not work for cascades.

Second problem was:

#id instead of #ID

it's case sensitive.

Thanks for helping me!