Dear JPA 2 expert group,
I have something to share with you about a possible JPA feature that I'm pondering over for quite a long time.
A use case I frequently encounter is when trying to reference a (sub) table that has a super table (mapped via InheritanceType.JOINED). Due to the fact that JPA requires all sub entities to use the identifier of the inheritance root class results in the fact that *other* tables/entities (external to the inheritance tree) are supposed to / forced to reference the primary key columns of an inheritance entity (at least not without a performance hit).
Take, for example, geographic areas: continents, countries, states etc. These could be modeled and mapped as following (a parent_id is in GeoAreas for the parent-child relationship of geo areas):
CREATE TABLE GeoAreas
(
id INTEGER NOT NULL,
parent_id INTEGER NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES GeoAreas (id)
);
CREATE TABLE Continents
(
id INTEGER NOT NULL,
iso_code CHAR(2) NOT NULL,
PRIMARY KEY (id),
UNIQUE (iso_code),
FOREIGN KEY (id) REFERENCES GeoAreas (id)
)
CREATE TABLE Countries
(
id INTEGER NOT NULL,
iso_code CHAR(2) NOT NULL,
dial_code SMALLINT NULL,
PRIMARY KEY (id),
UNIQUE (iso_code),
FOREIGN KEY (id) REFERENCES GeoAreas (id)
)
CREATE TABLE States
(
id INTEGER NOT NULL,
iso_code VARCHAR(5) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES GeoAreas (id)
)
Note the two UNIQUE constraints for continents and countries, especially the latter one. The mapped entity classes then could look like the following:
@Entity
@Table(name = "GeoAreas")
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class GeoArea implements Serializable
{
@Id
@Column(name = "id")
protected Integer id;
@Column(name = "name")
protected String name;
@ManyToOne
@JoinColumn(name = "parent_id", referencedColumnName = "id")
protected GeoArea parent;
...
}
@Entity
@Table(name = "Continents")
public class Continent extends GeoArea
{
@Column(name = "iso_code")
private String isoCode;
...
}
@Entity
@Table(name = "Countries")
public class Country extends GeoArea
{
@Column(name = "iso_code")
private String isoCode;
@Column(name = "dial_code")
private Integer dialCode = null;
...
}
@Entity
@Table(name = "States")
public class State extends GeoArea
{
@Column(name = "iso_code")
private String isoCode;
...
}
Now there's basically no problem with the above. However, now imagine you wanted to use the well-known ISO country codes in another table to construct a composite primary key from them, e.g. you could construct the Zips table's primary key by using the UNIQUE NOT NULL column iso_code from the Countries table like this:
CREATE TABLE Zips
(
country_code CHAR(2) NOT NULL,
code VARCHAR(10) NOT NULL,
PRIMARY KEY (country_code, code),
FOREIGN KEY (country_code) REFERENCES Countries (iso_code)
)
According to the JPA however, relationships to non-primary keys aren't per se supported (it's up to the JPA provider to support such a mapping or not). The JPA portably only allows using the ID of the root class AFAIK:
CREATE TABLE Zips
(
country_id INTEGER NOT NULL,
code VARCHAR(10) NOT NULL,
PRIMARY KEY (country_id, code),
FOREIGN KEY (country_id) REFERENCES Countries (id)
)
The problem I have with the above is that the primary key actually isn't a natural one. Due to the fact that inheritance trees force the use of the root table's primary key almost always results in an artificial ID to be propagated into all sub tables and there's actually no portable/specified way to "return" to the sub tables' natural components, here the ISO country codes.
This is really limiting regarding database design (and often you're not allowed to make changes to a DB). There's no guarantee any JPA provider will support relationships to non-primary key columns and this might break with any next version of the JPA software or when changing the JPA provider.
Is there any chance for JPA to support some kind of way to "return" to the natural components as in the example given? For my part it would suffice to require the existence of an alternative key (UNIQUE, NOT NULL) for caching reasons and others I don't know/understand (Hibernate already has an annotation named @NaturalId which seems to be close to what I'm asking for).
--
Final note:
JPA providers do "kind of" support relationships to non-primary key columns, but they do this very inconsistently. All-new JPA 2 derived identities mostly fail in contrast to JPA 1-style mappings, effectively making the newer mappings *less* powerful. I put up a series of tests a while ago to see how EclipseLink 2.2.0 and Hibernate 3.6.0 fare using all possible relationship constellations in practice. The results can be found here (rather lengthy read):
http://kawoolutions.com/Technology/JPA,_Hibernate,_and_Co./Relationships_to_Non-Primary_Key_Columns
As you can see, the matrices near the bottom of that page show that JPA 1.0 mappings always work, whereas derived identies cause all sorts of mapping exceptions and others (the X's), which proves that for relationships to non-primary key columns JPA 1.0 style mappings are more powerful than JPA 2.0 style mappings.
--
Sorry for the long explanation, but it's rather hard for me to explain what I mean. You probably wouldn't have needed such a long use case as you already know the details.
So, to summarize my RFE:
1. add support for relationships to non-primary key columns, where relationships to alternative key columns would *probably* suffice
2. add support for alternative keys (optional)
Please let me know what you think.
Thank you very much and best regards
Karsten Wutzke
PS: all I want to do is return to the natural key columns, so if there's currently an (official!) way to do this please let me know.
___________________________________________________________
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192