JPA and @OneToMany – How to Optimize?

In this article I want to explain an issue concerning the JPA OneToMany relationship which I was faced with in one of my own projects. Working with object -orientated languages like java we often model relationships between object in various cases. One of such relationships is the OnToMany relationship. For example an object ‘Server’ may have a relationship to an object ‘Configuration’. To make the ‘Configuration’ object generic we can model the JPA object in Java like this:

@javax.persistence.Entity
public class Configuration implements java.io.Serializable {
 private static final long serialVersionUID = 1L;

 @Id
 @GeneratedValue
 private BigInteger id;

 public String itemValue;
 public String itemName;

 @SuppressWarnings("unused")
 private Configuration() {
 }

 public Configuration(String name, String value) {
 itemValue = value;
 itemName = name;
 }
}

Continue reading “JPA and @OneToMany – How to Optimize?”

JPA – EclipseLink – MySQL – @OneToMany

Today I was faced with a really strange problem concerning JPA, EclipseLink an MySQL. I have Service EJB (3.1) with some JPA Entity Beans. They are joined with @OneToMany constraints. I am deploying my application typically under GlassFish 3.1 with MySQL Database. Everything works fine so far. But in some really strange cases – long complex transactions with a lot of updates and inserts I got a JPA Exception like this:

[#|2014-09-25T17:21:01.989+0200|WARNING|glassfish3.1.2|org.eclipse.persistence.session.file:/opt/glassfish-3.1.2/glassfish/domains/domain2/applications/office/office-ejb-1.0.0_jar/_org.imixs.workflow.jee.jpa|_ThreadID=160;_ThreadName=Thread-2;|Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`mydatabase`.`ENTITY_WRITEACCESS`, CONSTRAINT `FK_ENTITY_WRITEACCESS_writeAccessList_ID` FOREIGN KEY (`writeAccessList_ID`) REFERENCES `WRITEACCESS` (`ID`))
Error Code: 1451
Call: DELETE FROM WRITEACCESS WHERE (ID = ?)
    bind => [1 parameter bound]
Query: DeleteObjectQuery(org.imixs.workflow.jee.jpa.WriteAccess@5aa4670a)

I was confronted with such error messages in the past and solved them by changing the transaction boundaries in my code by encapsulating some of the jpa stuff in separate methods with new transaction context.

But The real reason for this kind of error is an inaccurate mapping of the database schema in MySQL made by EclipseLink. My table ‘ENTITY_WRITEACCESS’ is a crosstable needed for a @OneToMany relationship of my JPA entity beans.  I never looked at the schema in detail, because in my opinion that is stuff of the OR-Mapper.
The problem was that per default no CascadeType is defined for a @OneToMany relationship. But even when you define a CascadeType like CascadeType.DELETE – which is also implied by CascadeType.ALL – to your entity beans this will not necessarily be reflected to the database schema generated from the OR-Mapper. When you look at the generated schema you’ll notice that ON DELETE CASCADE is not added to the constraint. When you alter your schema and Add ON DELETE CASCADE to actual tables this will fix the problem above.

Note that this all depends on the JPA implementors how the constrains are created. So take a look at your generated table schemas.