I am trying to get a many to many relationship working, but am getting an error to do with the additional columns in the mapping table. The error is:

Foreign key (FKAB2F951E28DE5539:actors [actorid])) must have same number of columns as the referenced primary key (actors [movieid,actorid])

I have three db tables as follows (note I am using an existing database, so I am unlikely to be able to change the schema):

CREATE TABLE `imdb`.`movies` (
`movieid` mediumint(8) unsigned NOT NULL auto_increment,
`title` varchar(400) NOT NULL,
`year` varchar(100) default NULL,
PRIMARY KEY (`movieid`),
KEY `title` (`title`(15))
)


CREATE TABLE `imdb`.`movies2actors` (
`movieid` mediumint(8) unsigned NOT NULL,
`actorid` mediumint(8) unsigned NOT NULL,
`as_character` varchar(1000) default NULL
)

CREATE TABLE `imdb`.`actors` (
`actorid` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(250) NOT NULL,
`sex` enum('M','F') default NULL,
`imdbid` mediumint(9) default NULL,
PRIMARY KEY (`actorid`),
KEY `name` (`name`(10))
)

Mapping documents:
movies mapping:
<hibernate-mapping
package="com.drew.entities">

<class name="Movie" table="movies">
<id name="id" column="movieid">
<generator class="native"/>
</id>
<property name="title"/>
<property name="year"/>

<set name="actors" table="actors">
<key column="movieid"/>
<many-to-many column="actorid"
class="Actor"/>
</set>
</class>
</hibernate-mapping>

Actors mapping:
<hibernate-mapping
package="com.drew.entities">

<class name="Actor" table="actors">
<id name="id" column="actorid">
<generator class="native"/>
</id>
<property name="name"/>
<property name="sex"/>

<set name="movies" table="movies" inverse="true">
<key column="actorid"/>
<many-to-many column="movieid"
class="Movie"/>
</set>
</class>
</hibernate-mapping>


The corresponding classes are:


public class Movie {
private Long id;
private String title;
private String year;

private Collection actors = new HashSet();

// getters/setters removed but all standard as generated by Eclipse
}

public class Actor {
private Long id;
private String name;
private String as_character;
private char sex;

private Collection movies = new HashSet();

// getters/setters removed but all standard as generated by Eclipse
}

I've seen a number of threads with the same message, but none of the changes seem to work. This seems to be a relatively common way of mapping data in the db world (having the additional columns int he mapping table), so I'm guessing there is a way of doing this, but I'm having real problems finding the solution. I'd be grateful for any help.

Rich.

Full stack trace of any exception that occurs:
log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Initial SessionFactory creation failed.org.hibernate.MappingException: Foreign key (FKAB2F951E28DE5539:actors [actorid])) must have same number of columns as the referenced primary key (actors [movieid,actorid])
Exception in thread "main" java.lang.ExceptionInInitializerError
at com.drew.util.HibernateUtil.<clinit>(HibernateUtil.java:17)
at com.drew.test.MovieDetails.getMovieById(MovieDetails.java:25)
at com.drew.test.MovieDetails.main(MovieDetails.java:17)
Caused by: org.hibernate.MappingException: Foreign key (FKAB2F951E28DE5539:actors [actorid])) must have same number of columns as the referenced primary key (actors [movieid,actorid])
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:90)
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:73)
at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:1263)
at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1170)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1286)
at com.drew.util.HibernateUtil.<clinit>(HibernateUtil.java:13)
... 2 more

The problem here is that you are not using the relationship table at all. The table specified on sets should be movies2actors for both of the mappings.



Farzad-

Hi,

The way Hibernate handles many-to-many to associations is by creating a middle table implicitly without you having to explicitly define one in the mapping file. This has the downside that you can’t define additional properties/columns in that middle table as you don’t have a handle at a POJO level.

If you would like to define additional columns in the middle table (like in your case) then you have to use two one-to-many relationships for both sides and explicitly map that in an .hbm file. This will give you a handle at a POJO level to play with.

I believe in your case the middle table’s schema must have been manually modified somehow.


mosa

Thanks for this. I have now created a class for the mapping table as follows:

public class MovieActor implements Serializable{

public static class Id implements Serializable{
public Long movieId;
public Long actorId;
}
private Id id;

private String character;
private Movie movie;
private Actor actor;

public Id getId() {
return id;
}
public void setId(Id id) {
this.id = id;
}
// ......
}

<hibernate-mapping package="com.drew.entities">

<class name="MovieActor" table="movies2actors">
<composite-id name="id" class="MovieActor$Id">
<key-property name="movieId" column="movieid" access="field" />
<key-property name="actorId" column="actorid" access="field" />
</composite-id>

<!-- Relationships Begin here -->
<many-to-one name="movie" class="Movie" insert="false" update="false" />
<many-to-one name="actor" class="Actor" insert="false" update="false" />
<!-- Relationships End here -->
</class>
</hibernate-mapping>

and changed the movie (and similarly for actor) mapping to:

<hibernate-mapping
package="com.drew.entities">

<class name="Movie" table="movies">
<id name="id" column="movieid">
<generator class="native"/>
</id>
<property name="title"/>
<property name="year"/>

<set name="actors" inverse="true" cascade="save-update">
<key column="movieid"/>
<one-to-many class="MovieActor" />
</set>
</class>
</hibernate-mapping>

Where previously it never got as far as to starting my app, it now does. However, when running this test:


Session session = HibernateUtil.getSessionFactory().getCurrentSession();

session.beginTransaction();

Movie movie = (Movie)session.createQuery(
"from Movie where movieid=" + movieId)
.uniqueResult();

System.out.println("Movie: " + movie.getTitle());

Collection<Actor> actors = movie.getActors();

Iterator<Actor> actor = actors.iterator();
while (actor.hasNext()) {
Actor a = actor.next();
System.out.println("\t" + a.getName() + "(" + a.getSex() + ")");
}
session.getTransaction().commit();

I get the SQL below which errors as the columns 'movie' and 'actor' are not in the movies2actor table.

Hibernate:
select
movie0_.movieid as movieid0_,
movie0_.title as title0_,
movie0_.year as year0_
from
movies movie0_
where
movieid=1074158
Movie: Star Wars (1977)
Hibernate:
select
actors0_.movieid as movieid1_,
actors0_.actorid as actorid1_,
actors0_.movieid as movieid2_0_,
actors0_.actorid as actorid2_0_,
actors0_.movie as movie2_0_,
actors0_.actor as actor2_0_
from
movies2actors actors0_
where
actors0_.movieid=?

I am obviously getting confused as to what it should be doing - should I not be able to use the Iterator above to get the actors for the movie?

Ok - I see the problem now. I used the hibernate tools to reverse engineer the java and mapping files and I was basically not using the mapping class correctly. Now I have it working - I can recommend the reverse engineering tools as a way of learning how things work. Much easier to test scenarios from that.

ok cool,

that sounds like a logical cause of your problem. I am also interested in reverse engineering but haven’t managed to set it up.

Would you mind explaining that in brief ?


cheers

mosa