Key column does not cascade multiple joins.

Problem: Parent keys will not cascade beyond one generation. Example: Company --> Department --> Employee: Company key will not cascade to Employee.

The following example further describes this issue.

A COMPANY table contains two columns, COMP_ID and NAME. The COMP_ID column is the primary key.

A DEPARTMENT table contains three columns, COMP_ID, DEPT_ID, and NAME. The COMP_ID and DEPT_ID columns are the primary key columns. The COMP_ID column is a foreign key column which references the COMP_ID column in the COMPANY table.

An EMPLOYEE table contains four columns, COMP_ID, DEPT_ID, EMP_ID, and NAME. The COMP_ID, DEPT_ID, and EMP_ID columns are the primary key columns. The COMP_ID and DEPT_ID columns are foreign key columns which reference the COMP_ID and DEPT_ID columns respectively in the DEPARTMENT table.

An entity exists for each table, Company, Department, and Employee. The Company entity has three attributes, compId, name, and departments. The compId attribute maps to the COMP_ID column. The name attribute maps to the NAME column. The departments attribute maps a one-to-many relationship to the Department entity.

The Department entity has five attributes, compId, deptId, name, employees, and company. The compId attribute maps to the COMP_ID column. The deptId attribute maps to the DEPT_ID column. The name attribute maps to the NAME column. The employees attribute maps a one-to-many relationship to the Employee entity. The company attribute maps a many-to-one relationship to the Company entity, joined by the COMP_ID column.

The Employee entity has five attributes, compId, deptId, empId, name, and department. The compId attribute maps to the COMP_ID column. The deptId attribute maps to the DEPT_ID column. The empId attribute maps to the EMP_ID column. The name attribute maps to the NAME column. The department attribute maps a many-to-one relationship to the Department entity, joined by the COMP_ID and DEPT_ID columns.

Below are the eleven steps to re-create this problem.

1. A new Company entity is instantiated.
2. The name attribute on the Company instance is set to "company".
3. A new Department entity is instantiated.
4. The name attribute on the Department instance is set to "department".
5. The department attribute on the Company instance is set to the Department instance.
6. A new Employee entity is instantiated.
7. The name attribute on the Employee instance is set to "Frank".
8. The employee attribute on the Department instance is set to the Employee instance.
9. The Employee instance is added to the employees attribute on the Department instance.
10. The Department instance is added to the departments attribute on the Company instance.
11. The Company instance is persisted.

The following SQL statements are executed.

INSERT INTO COMPANY (COMP_ID, NAME)
VALUES (?, ?)
[params=(long) 1, (String) company]

INSERT INTO DEPARTMENT (COMP_ID, DEPT_ID, NAME)
VALUES (?, ?, ?)
[params=(long) 1, (long) 1, (String) department]

INSERT INTO EMPLOYEE (DEPT_ID, EMP_ID, NAME)
VALUES (?, ?, ?)
[params=(long) 1, (long) 1, (String) Frank]

A PersistenceException is thrown because of the DB2 error noted below. The COMP_ID column cascades to the DEPARTMENT table, but does not cascade to the EMPLOYEE table.

DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=4, COLNO=0

{prepstmnt 1256737512 INSERT INTO EMPLOYEE (DEPT_ID, EMP_ID, NAME) VALUES (?, ?, ?) [params=(long) 1, (long) 1, (String) Frank]}

[code=-407, state=23502]SQLCA OUTPUT[Errp=SQLDFMT1, Errd=[-2146041828, 28, 0, 0, 0, 0]]

An application is provided to demonstrate the issue. To setup and unsuccessfully run demonstration with OpenJPA:

1. Add DB provider JAR(s) to the FunInheritanceJava project's build path.
2. Modify FunInheritanceJava/src/META-INF/applicationcontext-jpa.xml to set the correct dataSource class, user, password, and currentSchema.
3. Modify FunInheritanceJava/database/createtables.sql to set current schema to match value set in step 1.
4. Setup database by running SQL in FunInheritanceJava/database/createtables.sql.
5. Run testCreate_WithDepartmentAndEmployee Junit test in FunInheritanceJava/test/service/dao/CompanyDaoTest.java.
6. Results should indicate an AssertionFailedError caused by the PersistenceException displayed above.

To successfully run demonstration with TopLink:

1. Change "org.springframework.orm.jpa.vendor.OpenJpaVendorAdapter" to "org.springframework.orm.jpa.vendor.TopLinkJpaVendorAdapter" in FunInheritanceJava/src/META-INF/applicationcontext-jpa.xml.
2. Run testCreate_WithDepartmentAndEmployee Junit test in FunInheritanceJava/test/service/dao/CompanyDaoTest.java.
3. Results should indicate a successful Junit execution.