lmerry213 lmerry213 - 3 months ago 41
Java Question

JPA Inverse Join with OneToMany causing More than one row with the given identifier

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:

LISTING

@OneToMany
@JoinTable (name = "LISTING_LINE_ITEM", joinColumns = @JoinColumn (name = "listing_id"), inverseJoinColumns = @JoinColumn (name = "service_period_id"))
Set<ServicePeriod> servicePeriods;


LISTING_LINE_ITEM

@ManyToOne (fetch = FetchType.EAGER)
@JoinColumn (name = "listing_id", nullable = false)
Listing listing;

@ManyToOne (fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
@JoinColumn (name = "service_period_id")
ServicePeriod servicePeriod;


SERVICE_PERIOD

@ManyToOne
@JoinTable (name = "LISTING_LINE_ITEM", joinColumns = @JoinColumn (name = "service_period_id"), inverseJoinColumns = @JoinColumn (name = "listing_id"))
Listing listing;


The obvious goal is to be able to easily obtain a list of
ServicePeriod
s for a
Listing
or a single
Listing
for a
ServicePeriod
. Currently the way this is set up I'm getting an exception:

org.hibernate.HibernateException: More than one row with the given identifier was found: 361951, for class: com.gonfind.entity.ServicePeriod


I believe this is because a listing has ListingLineItems that refer to the same ServicePeriod. I'm sure that there is a way to accomplish what I'm after but I don't know what it is.

Answer

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 --> LineItem

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:

LISTING

@OneToMany(mappedBy = "listing",
        fetch = FetchType.EAGER,
        cascade = CascadeType.PERSIST)
Set<ServicePeriod> servicePeriods;

SERVICE_PERIOD

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "listing_id")
Listing listing;

@OneToMany(mappedBy = "servicePeriod",
        fetch = FetchType.EAGER,
        cascade = CascadeType.PERSIST)
Set<ListingLineItem> lineItems;

LISTING_LINE_ITEM

@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 Listing <-> 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.

Comments