permaculture playing cards*
The moose likes Object Relational Mapping and the fly likes Hibernate join table contents confusion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate join table contents confusion" Watch "Hibernate join table contents confusion" New topic
Author

Hibernate join table contents confusion

Les Hartzman
Greenhorn

Joined: Sep 19, 2012
Posts: 23
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

Joined: Feb 28, 2007
Posts: 79
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


lavnish.blogspot.com
Les Hartzman
Greenhorn

Joined: Sep 19, 2012
Posts: 23
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

Joined: Feb 28, 2007
Posts: 79
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
Greenhorn

Joined: Sep 19, 2012
Posts: 23
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
Greenhorn

Joined: Sep 19, 2012
Posts: 23
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
 
 
subject: Hibernate join table contents confusion
 
Similar Threads
update while doing save in hibernate
Hopefully an easy problem with Hibernate
MYSQL: a row with only one value the same in that field, extracted from table
Indexed Collections
CMR and EJBQL