ManyToMany (with additional columns) using @ElementCollection and java.util.Map?

Question

I'm trying to optimize my @Entity classes. Let's take the common case of a User <-> Group relationship. I also want to store the date when the relation was established. The table layout:

 USER      GROUP_USER      GROUP
------    ------------    -------
 id        user_id         id
 name      group_id        name
           createdAt

With the following 3 classes:

1. User with @OneToMany List<GroupUser>
2. GroupUser with @ManyToOne User and @ManyToOne Group
3. Group with @OneToMany List<GroupUser>

It works, but it sucks for common use cases. For example: Give me all groups for User user:

List<Group> groups;
for(GroupUser gu : user.getGroupUsers) {
    groups.add(gu.getGroup());
}

---

Then the idea of using a Map came to my mind. So I created the following 3 classes:

@Entity
class User {
   @Column(name="id")
   UUID id;

   @Column(name="name")
   String name;

   @ElementCollection
   @CollectionTable(name="Group_User")
   Map<Group, GroupUserRelationData> groups;
}

@Entity
class Group {
   @Column(name="id")
   UUID id;

   @Column(name="name")
   String name;

   @ElementCollection
   @CollectionTable(name="Group_User")
   Map<User, GroupUserRelationData> users;
}

@Embeddable
class GroupUserRelationData {
  @Column(name="createdAt")
  DateTime createdAt;
}

It does create the 3 tables as expected, but the columns inside the Group_User table are weird:

 USER      GROUP_USER      GROUP
------    ------------    -------
 id        User_id         id
 name      users_KEY       name
           Group_id
           group_KEY
           createdAt

I'm sure that I'm missing some things... Looks like I have to specify the JoinColumn. But what's the right way?

What's the difference between @CollectionTable(joinColumns=...) and @MapKeyColumn and @MapKeyJoinColumn. Do I have to set all of those?

Or do I have to use @ManyToMany instead of @ElementCollection ?

I just want to get the same table layout as at the top using a java.util.Map.

---

EDIT #1: Additionally the database should afterwards have the right constraints for the table:

• Composite Primary Key (Group_id, User_id)

At the moment it creates a composite PK (Group_id, users_KEY) which seems pretty weird to me. And it also creates 4(!!) indexes, one for each column User_id, Group_id, users_KEY and groups_KEY.

---

EDIT #2: I found a website which tells when to use which annotations: http://kptek.wordpress.com/2012/06/26/collection-mapping/

The bad thing is: I still don't know WHY...

Solution:

After a bit of trying I came up with the following code, which does exactly what I want. I've got to use all of those annotations, else it creates additional columns:

@Entity
class User {
   @Column(name="id")
   UUID id;

   @Column(name="name")
   String name;

   @ElementCollection
   @CollectionTable(name="Group_User",
     joinColumns=@JoinColumn(name="User_Id"))
   @MapKeyJoinColumn(name="Group_Id")
   Map<Group, GroupUserRelationData> groups;
}

@Entity
class Group {
   @Column(name="id")
   UUID id;

   @Column(name="name")
   String name;

   @ElementCollection
   @CollectionTable(name="Group_User",
     joinColumns=@JoinColumn(name="Group_Id"))
   @MapKeyJoinColumn(name="User_Id")
   Map<User, GroupUserRelationData> users;
}

@Embeddable
class GroupUserRelationData {
  @Column(name="createdAt")
  DateTime createdAt;
}

I'd still love to see an explanation what those annotations exactly do, and why the default behavior created FOUR columns and a strange primary key.

Answer 1

Map required both the one-to-many key and the map-key.

In your first example, Hibernate figures out it needs a FK from Group.id to match the GroupUser.group_id to load the associated users. But because you don't specify a Map key it has to figure out where to take it from?

So, Hibernate assumed you wanted a @MapKeyColumn and because the column name was not specified, the name of the property followed by underscore followed by KEY is used (for example users_KEY).

That's why you had those two additional columns. When you supplied the @MapKeyJoinColumn(name="User_Id"), then the Map knew where to take the key from.