[eclipselink-users] Inheritance and FK relations?

[Bart Kummel <bart@xxxxxxxxxxxx>]

Hi,

At my current client, we have a database with a set of tables for 
storing invoice information. For some reasons, they have chosen to 
duplicate the set of tables. After an incoming invoice is paid, it is 
moved to another table. So we have two identical sets of tables: one for
 unpaid invoices and one for paid invoices. A simplified version of our 
database structure is given in the "picture" below.
+---------------+      +---------------+
| UNP_INVOICE   |      | PAID_INVOICE  |
|               |      |               |
| INV_NUMBER    |      | INV_NUMBER    |
| TOTAL_AMOUNT  |      | TOTAL_AMOUNT  |
| ...           |      | ...           |
+---------------+      +---------------+
        |                      |
        |fk                    |fk
+---------------+      +---------------+
| UNP_INV_LINE  |      | PAID_INV_LINE |
|               |      |               |
| LINE_NUMBER   |      | LINE_NUMBER   |
| DESCRIPTION   |      | DESCRIPTION   |
| AMOUNT        |      | AMOUNT        |
+---------------+      +---------------+


(Note that this is a simplified version. In reality we have 2 times 8 
tables with lots of foreign key relations.)

My job is to create a JPA ORM layer on top of this database design, 
using TopLink 11.1.1. Needless to say that this calls for some smart 
inheritance. However, I have some difficulties to get things working. My
 idea was to create an abstract super class for each pair of tables and 
then create concrete Entity classes for each table, extending the 
appropriate abstract super class. So I created abstract superclasses 
with all needed JPA annotations. I annotated the abstract classes as 
@MappedSuperclass. Then I created the concrete sub classes. The concrete
 classes are rather minimal, since there are no differences between two 
concrete classes, apart from the table name. So for the "INVOICE" table,
 I have three classes:
@MappedSuperclass
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public abstract Invoice{
    @Column(name="INV_NUMBER")
    private invoiceNumber;
    @Column(name="TOTAL_AMOUNT")
    private totalAmount;
 
    // Accessors go here...
}

@Entity
@Table(name="UNP_INVOICE")
public UnpaidInvoice extends Invoice {
}

@Entity
@Table(name="PAID_INVOICE")
public PaidInvoice extends Invoice {
}

This works as long as there are no foreign key relations between the 
tables. But when I start adding the relations, the problems start. I 
added a one-to-many relation to Invoice, as follows:
@MappedSuperclass
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public abstract Invoice{
    @Column(name="INV_NUMBER")
    private invoiceNumber;
    @Column(name="TOTAL_AMOUNT")
    private totalAmount;
 
    @OneToMany(mappedBy = "invoice", fetch=FetchType.EAGER, cascade={CascadeType.REFRESH, CascadeType.MERGE})
    private List<? extends InvoiceLine> invoiceLineList;
 
    // Accessors go here...
}

The I tried overriding the relation in the concrete classes:
@Entity
@Table(name="UNP_INVOICE")
@AssociationOverride(name = "invoiceLineList", joinColumns = @JoinColumn(table = "UNP_INV_LINE"))
public UnpaidInvoice extends Invoice {
}

When I deploy this to WebLogic, the WL deployer says:

[wldeploy] Local Exception Stack:
[wldeploy] Exception [EclipseLink-7250] (Eclipse Persistence Services - 1.2.0.v20091016-r5565): org.eclipse.persistence.exceptions.ValidationException
[wldeploy] Exception Description: [package.UnpaidInvoice] uses a non-entity [package.InvoiceLine] as target entity in the relationship attribute [field invoiceLineList].

A solution I can think of is to not declare the relationships in the 
super class. But that means I'll have to duplicate the declaration of 
the relationship in the concrete classes. Another solution I thought of 
was making the abstract classes @Entity instead of @MappedSuperclass. 
But then the @AssociationOverride does not work, and Toplink will search
 the database for a table called "INVOICELINE", but that does not exist.

Does anyone know of a smarter solution to this problem? Thanks in 
advance!

Best regards,
Bart Kummel