Odi's astoundingly incomplete notes

Many-to-Many without join-table

EJB3 lets you define many-to-many associations. It requires that the two entities be joined via a join-table. Of course many-to-many relationships are also possible without a join table. This is the case for two tables joined on non-unique columns. Take the two entities Address and City for instance. (This model is not in 3rd normal form):

Address (id, street, zip, city)
City (id, zip, name)

In Switzerland there are cities with the same zip code. And in certain cases it makes sense to join Address with City just on the zip column. This is a many-to-many relation.

When you want to map such a relation to EJB you need a join table. In this case a join table can easily be created as a view:

CREATE VIEW
Address_City AS SELECT A.id AS address_id, C.id AS city_id FROM Address
A, City C WHERE A.zip = C.zip

This view can then be used in the EJB3 annotations:

@ManyToMany
@JoinTable(table=@Table(name="Address_City"),
joinColumns=@JoinColumn(
name="address_id",
referencedColumnName="id",
insertable=false,
updatable=false),
inverseJoinColumns=@JoinColumn(name="city_id",
referencedColumnName="id",
insertable=false,
updatable=false)
)

posted on 2005-09-08 12:04 UTC in Code | 0 comments | permalink

Add comment