I have the following embeddable class that contains an @Lob:
@Embeddable
public class EntityState {
private Integer version;
@Lob
@XmlJavaTypeAdapter(CharArrayAdapter.class)
private char[] xmlState;
...
}
I also have the following embeddable class that contains the above embeddable:
@Embeddable
public class EntityEvent {
@NotNull
private String note;
private EntityState entityState;
...
}
Finally, I have many entity classes that contain a property called history that is a list of EntityEvents. The following is an example:
@Entity
public class Company {
@NotNull
@ElementCollection
private List<EntityEvent> history;
...
}
When I deploy my application in GlassFish 4.1, EclipseLink creates the following tables in my Derby 10.11.1.1 database:
When I create a new Company, my application creates an EntityEvent and adds the EntityEvent to the Company history.
When I modify a Company, my application does the following:
The problem is that when I try to delete an entity that has a history with multiple EntityEvents I receive the following error:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
Error Code: 20000 Call: DELETE FROM Company_HISTORY WHERE ((((((((((CHANGES = ?) AND (CLIENTTYPE = ?)) AND (CREATED = ?)) AND (IPADDRESS = ?)) AND (NOTE = ?)) AND (TYPE = ?)) AND (VERSION = ?)) AND (XMLSTATE = ?)) AND (CREATER_ID = ?)) AND (Company_ID = ?)) bind => [10 parameters bound]
I found a few references to the issue in the following links:
I tried the @OrderColumn technique described in the above referenced stackoverflow article but this did not work in EclipseLink.
The solution that work for me was to add the EclipseLink nonstandard @CascadeOnDelete annotation to my entity as shown below:
@Entity
public class Company {
@NotNull
@ElementCollection
@CascadeOnDelete
private List<EntityEvent> history;
...
}
After performing this change and rebuilding my database, my COMPANY_HISTORY table has a new definition:
The solution to my problem surprised me because it seems repetitive. My understanding is that JPA should delete all embeddables associated with an entity when the entity is deleted. The fact that EclipseLink has this nonstandard annotation as documented in the following link makes me think that EclipseLink has a bug and instead of fixing the bug created a new @CascadeOnDelete annotation so that the bug would be covered up by the databases cascading delete functionality.
So my question is why. Why does EclipseLink support the @CascadeOnDelete with @ElementCollection?
CascadeOnDelete is simply a feature that specifies that you have specified the "On Delete Cascade" option in your tables, so that JPA does not need to issue SQL to delete the corresponding references. This SQL can apply to any reference, which is why CascadeOnDelete works on an element collection mapping and any other referene mapping.
Your issue has to do with lob comparison limitation in your database, and since there isn't an ID field to uniquely identify element collection rows, this limitation interferes with the way EclipseLink tries to ensure it is only removing the required rows. If you were willing to add an order column to your table, why not just make the EntityEvent an Entity? Or you can customize EclipseLink as described here so that it uses the foreign key and an orderBy field or any combination of fields as a primary key to uniquely identify rows instead of including the lob field.