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:
- COMPANY
- COMPANY_HISTORY
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:
- Creates an EntityState object and sets the xmlState property to an XML representation of the unmodified entity.
- Creates an EntityEvent object containing the above EntityState.
- Adds the EntityEvent to the Company history.
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:
- Hibernate - @ElementCollection - Strange delete/insert behavior
- http://eclipse.1072660.n5.nabble.com/Customizing-delete-calls-before-updating-a-ElementCollection-td7312.html
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:
- Without @CascadeOnDelete
- ALTER TABLE COMPANY_HISTORY ADD CONSTRAINT CMPNYHISTORYCMPNYD FOREIGN KEY (COMPANY_ID) REFERENCES COMPANY (ID);
- With @CascadeOnDelete
- ALTER TABLE COMPANY_HISTORY ADD CONSTRAINT CMPNYHISTORYCMPNYD FOREIGN KEY (COMPANY_ID) REFERENCES COMPANY (ID) ON DELETE CASCADE;
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?
