Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate join table contents confusion

 
Les Hartzman
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm looking at an example that has 2 tables. One table holds events (it contains 2 entries) and one table contains speakers for the events (3 speakers for each event). The many-to-many relationship produces a join table that has double entries with 6 rows for each event id (2 rows/speaker).

---------------
| 1 | A |
| 1 | B |
| 1 | C |
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | D |
| 2 | E |
| 2 | F |
| 2 | D |
| 2 | E |
| 2 | F |
---------------

So my question is why are there 2 rows for each speaker associated with the event as opposed to a single row?

I'm sure this is due to lack of understanding on my part, but any explanation would be appreciated.

Thanks.

Les
 
lavnish lalchandani
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can you give more details , class structure , also let us know if you are executing using HQL or Criteria or some other way ?
did you try checking the SQL logs

ALSO in your config files
Add lines

hibernate.show_sql=true
hibernate.format_sql=true

and tell us what is the Exact SQL query is generated
 
Les Hartzman
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the pointers on the SQL logging and the Hibernate logging. After figuring out how to turn on logging for MySQL, I saw that the entries into the join table were simple inserts - not the product (forgive the pun) of a join. Here is the basic class structure for the 2 classes involved:

public class Event {

private Long id;
private String name;
private Set speakers;

public void setId(Long id) {
this.id = id;
}

public Long getId() {
return id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public void setSpeakers(Set speakers) {
this.speakers = speakers;
}

public Set getSpeakers() {
return speakers;
}


}

public class Speaker {

private Long id;
private String firstName;
private String lastName;
private Set events;

public Speaker() {
}

public SpeakerManyToMany(String firstName, String lastName) {
setFirstName(firstName);
setLastName(lastName);
}

public SpeakerManyToMany(String firstName, String lastName, Event event) {
this(firstName, lastName);
addEvent(event);
}

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getFirstName() {
return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public Set getEvents() {
return this.events;
}

public void setEvents(Set events) {
this.events = events;
}

private void addEvent(Event event) {
if (events == null) {
events = new HashSet();
}
events.add(event);
}
}

The object creation looks like the following:

Event event = new Event();
event.setName("Event1");
event.setSpeakers(new HashSet());
event.getSpeakers().add(new SpeakerManyToMany("Jane, "Jones", event));
event.getSpeakers().add(new Speaker("Joe", "Smith", event));
event.getSpeakers().add(new Speaker("John", "Smith", event));

// Save event object
session.save(event);

// second event with multiple speakers
Event event2 = new Event();
event2.setName("Event2");
event2.setSpeakers(new HashSet());
event2.getSpeakers().add(new Speaker("John", "Smith", event2));
event2.getSpeakers().add(new Speaker("Jane", "Doe", event2));
event2.getSpeakers().add(new Speaker("Dave", "Wood", event2));


The mapping files contain set statements such as:

<set name="speakers" table="event_speakers" cascade="all">
<key column="event_id"/>
<many-to-many column="speaker_id" class="Speaker"/>
</set>

The SQL being generated for the join table " insert into event_speakers (event_id, speaker_id) values (1, 1)". The values go from (1, 1) to (1, 3) (3 speakers - same event_id). Then the SQL changes to "insert into event_speakers (speaker_id, event_id) values (1, 1) to (3, 1)".

These SQL statements alternate back to the pair (event_id, speaker_id) then alternate again back to (speaker_id, event_id) for the 2nd event. So the insert values are as follows:

(1,1), (1,2), (1,3), (1,1), (2,1), (3,1), (2, 4), (2, 5), (2, 6), (4, 2), (5, 2), (6, 2) .


Les

 
lavnish lalchandani
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
one issue i see is ... you are using SETs but not implementing equals() and hashcode() in your POJOs
may be if you do that then you may not see double inserts
 
Les Hartzman
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I figured it out. I added 'inverse="true"' to the 'set' statement in the Event.hbm.xml file. This produced SQL inserts that were geared to the column id pair of (speaker_id, event_id) with values of (1,1), (1,2), (1,3), (2,4), (2,5), (2,6).

Les
 
Les Hartzman
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just read up more on inverse=true and found out that by doing that I'm stating that if the Speaker table is updated there won't be a corresponding update to the event. Setting inverse=true on Event says that it is the owner of the relationship. So I think that the duplication of information is necessary in a many-to-many relationship to maintain consistency.

Les
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic