Monday, July 21, 2008

Adding columns to join tables (in the context of JPA/Hibernate)

At some point in a @ManyToMany relationship I had to add some extra columns in the join table (the middle table).

Here's what Gavin King says in Java Persistence with Hibernate (a notable book on the subject):

Adding columns to join tables
You can use two common strategies to map such a structure to Java classes. The first strategy requires an intermediate entity class for the join table and is mapped with one-to-many associations. The second strategy utilizes a collection of components, with a value-type class for the join table.

Later in that chapter for the first approach (the extra entity for the middle table):

The primary advantage of this strategy is the possibility for bidirectional navigation: You can get all items in a category {...} and the also navigate from the opposite direction {...}. A disadvantage is the more complex code needed to manage {...} entity instances to create and remove associations—they have to be saved and deleted independently, and you need some infrastructure, such as the composite identifier. However, you can enable transitive persistence with cascading options on the
collections {...}, as explained {...}, “Transitive persistence.”

Later in that chapter for the second approach (the collection of components approach):

That’s it: You’ve mapped a ternary association with annotations. What looked
incredibly complex at the beginning has been reduced to a few lines of annota-
tion metadata, most of it optional.

Naively enough I chose the second approach. Who cares that there's a hibernate dependency in my JPA data access layer. I already have a few (a hibernate interceptor).

In this approach I had to use the @CollectionOfElements annotation. @CollectionOfElements works like that: it maps a collection (set, map, list) of something to a table. This table has no entity attached to it. It can work with value types, Strings and @Embeddables. In my case it had to be the @Embeddable.

Let me give you an example - it will clear things up: there are classes and there are students - two entities. There can be two classes with many students some of which are the same - so the relationship is @ManyToMany. The extra column in the join table would the grade of the student in that class.

So the approach with the @CollectionOfElements works like that: one of the entities holds the relationship - let it be the class entity - so it has something like that:

@Entity
public class Class {
    @Version
    private int version;
    ...
    @org.hibernate.annotations.CollectionOfElements
    private Set<GradedStudent> students;
    ...
}

Student is a simple entity, no code needed. Let's call the student with the grade an GradedStudent:

@Embeddable
public class GradedStudent {
    ...
    @OneToOne(..., cascade = {MERGE, PERSIST, REFRESH})
    private Student student;
    ...
    @Column( nullable = false, ... )
    private int grade;
    ...
}

That's pretty much it. Seems simple, you would think and straightforward.

BUT IT DOESN'T WORK.

Here's what gets wrong:

Everytime a class entity gets queried, it's version gets incremented. This makes updating a disconnected entity far more difficult and makes the @Version kind of obsolete.
Solution: none, I couldn't find anything remotely connected to this problem on the net.
The primary key in the join table (with a name like 'class_gradedstudent') is not the [class_id, student_id] but is [class_id, student_id, grade]. If you put extra columns in the join table and they are nullable = false, they would become part of the primary key.
Cascading fails. You have to create and persist a Student first in order it to become a part of a certain class entity. Even though a GradedStudent is said to cascade a Student.
Solution: none, I tried everything I could think of - no luck. I couldn't find anything remotely connected to this problem on the net.

Regarding 2: a quotation from the same book:

There is only
one change to the database tables: The {...} table now has a primary
key that is a composite of all columns, not only the ids of the two object, as in
the previous section. Hence, all properties should never be nullable—otherwise
you can’t identify a row in the join table.

Well, what if I don't want that? It doesn't say.

So, actually the second approach is not an option.

Sucks

Comments [2] | Trackback

Thursday, August 07, 2008 5:59:14 PM UTC

ty5eur7h79o9 ol;0 o;lio;yuilyu p;iup; up;ui;pyi

shgfdjas

Monday, August 11, 2008 12:13:16 PM UTC

absolutely

mihail.stoynov

OpenID
Please login with either your OpenID above, or your details below.

Name
E-mail	(will show your gravatar icon)
Home page

	Remember Me
Comment (Some html is allowed: `a@href@title, blockquote@cite, strike`) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.
Enter the code shown (prevents robots):
Live Comment Preview