[eclipselink-users] Odd use case

[Laird Nelson <ljnelson@xxxxxxxxx>]

Here's an odd use case that I thought might be food for thought for future releases (or maybe it's already handled).  Or it might be such an outlier that I should take my marbles and go home. :-)

I have a 30 year old schema that I'm mapping to JPA.

There is a particularly odd case where a Document, let's say, conceptually refers to many GLAccounts.  But in practical usage terms it only refers to one.

That is, structurally, in the database, a GLAccount has no knowledge of a Document (no foreign key, or implied foreign key, pointing back to it).  Similarly, there is no join table setting up a one-or-many-to-many relationship between these two entities.

What there is instead is in the Document table there are 4/5 of a foreign key back to the GLAccount table, whose primary key is defined of 5 components.

The 5th part of the foreign key is a Date that is supplied from outside.

In JPA terms, what I would like to be able to do is designate a @ManyToOne relationship, but allow the associated @JoinColumns machinery to take in that fifth component from something else (like a @Transient (not transient) property).

So, hypothetically, in Document:

@Transient
private Date effectiveDate;

@ManyToOne
@JoinColumns(
  @JoinColumn(name = "f1", referencedColumnName = "pk1"),
  @JoinColumn(name = "f2", referencedColumnName = "pk2"),
  @JoinColumn(name = "f3", referencedColumnName = "pk3"),
  @JoinColumn(name = "f4", referencedColumnName = "pk4")
)
@some.eclipselink.annotation.here.AdditionalJoinCriteria(field = "effectiveDate", referencedColumnName = "pk5")
private GLAccount account;

The idea here is that I set the @Transient effectiveDate property, and, since it is @Transient, it does not get mapped to a column.  But since it's not Java-language transient, it does get serialized, so it might be available at retrieval time.

With that there, I would hope that I could then simply say getGLAccount() and have the @ManyToOne relationship perform the SELECT query.

Any alternatives to handle this case are appreciated.  I obviously know that I can store the four pk components as JPA fields in Document, and then run a query myself; I was hoping for an ability to make this simpler and more transparent.

THanks,
Laird