I have a convenient relation set up in which an entity has a one-to-many relationship with another, and that has a many-to-one with another. So, a LISTING has many LISTING_LINE_ITEMS, and those LISTING_LINE_ITEMS have one SERVICE_PERIOD, but a SERVICE_PERIOD has many LISTING_LINE_ITEMS. I have attempted to describe this relationship using JPA's @JoinTable as follows:
@JoinTable (name = "LISTING_LINE_ITEM", joinColumns = @JoinColumn (name = "listing_id"), inverseJoinColumns = @JoinColumn (name = "service_period_id"))
@ManyToOne (fetch = FetchType.EAGER)
@JoinColumn (name = "listing_id", nullable = false)
@ManyToOne (fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
@JoinColumn (name = "service_period_id")
@JoinTable (name = "LISTING_LINE_ITEM", joinColumns = @JoinColumn (name = "service_period_id"), inverseJoinColumns = @JoinColumn (name = "listing_id"))
org.hibernate.HibernateException: More than one row with the given identifier was found: 361951, for class: com.gonfind.entity.ServicePeriod
You do appear to have some problems there. On the technical / JPA side:
you cannot use
LISTING_LINE_ITEM both as a join table and as an entity table. There are several reasons for this, but the main reason is that you will confuse JPA: it will try to use that table in different, incompatible ways for those two purposes.
in JPA, a bidirectional relationship is owned by exactly one side; the other side uses the
mappedBy attribute of its relationship annotation to reference the owning side.
But you also have data design problems. Your constraint that line items' service periods be restricted to one of those separately associated with the same listing constitutes either
a functional dependency between non-key fields, if the listing id is not part of the line item key, or otherwise
a functional dependency on a subset of a key.
In the first case, your data fail to be in third normal form; in the second case they fail to be even in second normal form. Your trouble modeling this with JPA arises in part from the low level of normalization.
Normalizing your data properly would make things a lot easier on multiple levels. To do that, you need to remove the direct association between listings and line items, and instead associate them through service periods. You then would have:
Listing <-- one to many -->
ServicePeriod <-- one to many -->
Of course, that would have implications on the structure of your application, but it's likely to be a long-term development and maintenance win, and maybe even a usability win, for the application to be aligned with the natural structure of your data like that. If you wish, you could put methods on your
Listing entity to allow
ListingLineItems to be managed to some extent as if they belonged directly to
Listings, and vise versa.
That data organization would look something like this:
@OneToMany(mappedBy = "listing", fetch = FetchType.EAGER, cascade = CascadeType.PERSIST) Set<ServicePeriod> servicePeriods;
@ManyToOne(fetch = FetchType.EAGER) @JoinColumn(name = "listing_id") Listing listing; @OneToMany(mappedBy = "servicePeriod", fetch = FetchType.EAGER, cascade = CascadeType.PERSIST) Set<ListingLineItem> lineItems;
@ManyToOne(fetch = FetchType.EAGER) @JoinColumn(name = "service_period_id") ServicePeriod servicePeriod;
If you cannot restructure your data more or less that way, then you're stuck jerry-rigging something that cannot fully be described to JPA. I'm imagining a separate join table for
ServicePeriod, a non-JPA FK constraint to that table from the entity table for line items, and, of course, proper form for the various bidirectional relationships.