many-to-many list doesn't poplulate association table

cbare · **Joined:** Sun Sep 19, 2004 12:22 am **Posts:** 6

I'm trying to start learning hibernate with a simple example, but have managed to get myself tangled up in my own underwear already.

I wanted to map books and authors as a many to many relationship. The authors have order, so each Book has a List of Authors. An author has a Set of Books. I create some books, then open a hibernate session and call session.saveOrUpdate(book); on them.

What happens is my book table is populated nicely, as is the author table, but book_authors (the association table) is left totally empty.

Any clues to a confuzzled beginner would be greatly appreciated. Thanks!

-Chris

Hibernate version:version 2.1.6, 9.8.2004

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC 
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="cbare.books">

   <class name="Book">
      <id name="id" unsaved-value="null">
         <generator class="native"/>
      </id>
      <property name="title" not-null="true"/>
      <property name="isbn"/>
      <list name="authors" table="book_authors" cascade="save-update">
            <key>
                <column name="book_id" not-null="true"/>
            </key>
         <index column="author_order" type="integer"/>
            <many-to-many class="Author">
                <column name="author_id" not-null="true"/>
            </many-to-many>
      </list>
   </class>

</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC 
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="cbare.books">

   <class name="Author">
      <id name="id" unsaved-value="null">
         <generator class="native"/>
      </id>
      <property name="firstName"/>
      <property name="lastName"/>
      <set name="books" lazy="true" table="book_authors" inverse="true">
            <key>
                <column name="author_id" not-null="true"/>
            </key>
            <many-to-many class="Book">
                <column name="book_id" not-null="true"/>
            </many-to-many>
      </set>
   </class>
      
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

        Set books = new HashSet();

        // ...
        // create books with one or more authors here
        // and add them to the Set books

        Configuration cfg = new Configuration()
           .addClass(Book.class)
           .addClass(Author.class);
        SessionFactory factory = cfg.buildSessionFactory();

        Session session = null;
        try {
            session = factory.openSession();
            Iterator iterator = books.iterator();
            while (iterator.hasNext()) {
                Book book = (Book)iterator.next();
                session.saveOrUpdate(book);
            }
        }
        finally {
            if (session!=null) session.close();
        }

Name and version of the database you are using:
MySql 4.0.15-nt driver: mysql-connector-java-3.0.9-stable-bin.jar

The generated SQL (show_sql=true):

Code:

create table book_authors (author_id bigint not null, book_id bigint not null, author_order integer not null, primary key (book_id, author_order))
create table Author (id bigint not null auto_increment, firstName varchar(255), lastName varchar(255), primary key (id))
create table Book (id bigint not null auto_increment, title varchar(255) not null, isbn varchar(255), primary key (id))
alter table book_authors add index FKCBA3F8F23DAE231 (book_id), add constraint FKCBA3F8F23DAE231 foreign key (book_id) references Book (id)
alter table book_authors add index FKCBA3F8F257F3E04F (author_id), add constraint FKCBA3F8F257F3E04F foreign key (author_id) references Author (id)

Debug level Hibernate log excerpt:

Quote:

...
21:38:03,812 INFO Configuration:350 - Mapping resource: cbare/books/Book.hbm.xml
21:38:04,796 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd in classpath under net/sf/hibernate/
21:38:04,812 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd in classpath
21:38:05,171 INFO Binder:229 - Mapping class: cbare.books.Book -> Book
21:38:05,343 DEBUG Binder:486 - Mapped property: id -> id, type: long
21:38:05,375 DEBUG Binder:486 - Mapped property: title -> title, type: string
21:38:05,375 DEBUG Binder:486 - Mapped property: isbn -> isbn, type: string
21:38:05,375 INFO Binder:571 - Mapping collection: cbare.books.Book.authors -> book_authors
21:38:05,406 DEBUG Binder:486 - Mapped property: authors, type: java.util.List
21:38:05,406 INFO Configuration:350 - Mapping resource: cbare/books/Author.hbm.xml
21:38:05,421 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd in classpath under net/sf/hibernate/
21:38:05,421 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd in classpath
21:38:05,484 INFO Binder:229 - Mapping class: cbare.books.Author -> Author
21:38:05,484 DEBUG Binder:486 - Mapped property: id -> id, type: long
21:38:05,484 DEBUG Binder:486 - Mapped property: firstName -> firstName, type: string
21:38:05,484 DEBUG Binder:486 - Mapped property: lastName -> lastName, type: string
21:38:05,484 INFO Binder:571 - Mapping collection: cbare.books.Author.books -> book_authors
21:38:05,500 DEBUG Binder:486 - Mapped property: books, type: java.util.Set
21:38:05,500 INFO Configuration:627 - processing one-to-many association mappings
21:38:05,500 DEBUG Binder:1353 - Second pass for collection: cbare.books.Book.authors
21:38:05,515 DEBUG Binder:1368 - Mapped collection key: book_id, index: author_order, element: author_id, type: cbare.books.Author
21:38:05,515 DEBUG Binder:1353 - Second pass for collection: cbare.books.Author.books
21:38:05,515 DEBUG Binder:1368 - Mapped collection key: author_id, element: book_id, type: cbare.books.Book
21:38:05,515 INFO Configuration:636 - processing one-to-one association property references
21:38:05,515 INFO Configuration:661 - processing foreign key constraints
21:38:05,515 DEBUG Configuration:678 - resolving reference to class: cbare.books.Book
21:38:05,515 DEBUG Configuration:678 - resolving reference to class: cbare.books.Author
...
21:38:07,453 DEBUG SessionImpl:555 - opened session
21:38:07,453 DEBUG SessionImpl:1387 - saveOrUpdate() unsaved instance
21:38:07,453 DEBUG SessionImpl:825 - saving [cbare.books.Book#<null>]
21:38:07,468 DEBUG SessionImpl:2309 - executing insertions
21:38:07,468 DEBUG Cascades:497 - processing cascades for: cbare.books.Book
21:38:07,484 DEBUG Cascades:506 - done processing cascades for: cbare.books.Book
21:38:07,500 DEBUG WrapVisitor:81 - Wrapped collection in role: cbare.books.Book.authors
21:38:07,515 DEBUG EntityPersister:490 - Inserting entity: cbare.books.Book (native id)
21:38:07,515 DEBUG BatcherImpl:200 - about to open: 0 open PreparedStatements, 0 open ResultSets
21:38:07,515 DEBUG DriverManagerConnectionProvider:84 - total checked-out connections: 0
21:38:07,515 DEBUG DriverManagerConnectionProvider:90 - using pooled JDBC connection, pool size: 0
21:38:07,515 DEBUG SQL:226 - insert into Book (title, isbn) values (?, ?)
Hibernate: insert into Book (title, isbn) values (?, ?)
21:38:07,531 DEBUG BatcherImpl:249 - preparing statement
21:38:07,562 DEBUG EntityPersister:388 - Dehydrating entity: [cbare.books.Book#<null>]
21:38:07,578 DEBUG AbstractEntityPersister:1236 - Natively generated identity: 1
21:38:07,578 DEBUG BatcherImpl:207 - done closing: 0 open PreparedStatements, 0 open ResultSets
21:38:07,578 DEBUG BatcherImpl:269 - closing statement
21:38:07,593 DEBUG Cascades:497 - processing cascades for: cbare.books.Book
21:38:07,593 DEBUG Cascades:524 - cascading to collection: cbare.books.Book.authors
21:38:07,593 DEBUG Cascades:113 - cascading to saveOrUpdate()
21:38:07,609 DEBUG SessionImpl:1387 - saveOrUpdate() unsaved instance
21:38:07,609 DEBUG SessionImpl:825 - saving [cbare.books.Author#<null>]
21:38:07,609 DEBUG SessionImpl:2309 - executing insertions
21:38:07,609 DEBUG EntityPersister:490 - Inserting entity: cbare.books.Author (native id)
21:38:07,609 DEBUG BatcherImpl:200 - about to open: 0 open PreparedStatements, 0 open ResultSets
21:38:07,625 DEBUG SQL:226 - insert into Author (firstName, lastName) values (?, ?)
Hibernate: insert into Author (firstName, lastName) values (?, ?)
21:38:07,625 DEBUG BatcherImpl:249 - preparing statement
21:38:07,625 DEBUG EntityPersister:388 - Dehydrating entity: [cbare.books.Author#<null>]
21:38:07,625 DEBUG AbstractEntityPersister:1236 - Natively generated identity: 1
21:38:07,625 DEBUG BatcherImpl:207 - done closing: 0 open PreparedStatements, 0 open ResultSets
21:38:07,625 DEBUG BatcherImpl:269 - closing statement
21:38:07,640 DEBUG Cascades:113 - cascading to saveOrUpdate()
21:38:07,640 DEBUG SessionImpl:1387 - saveOrUpdate() unsaved instance
21:38:07,640 DEBUG SessionImpl:825 - saving [cbare.books.Author#<null>]
21:38:07,640 DEBUG SessionImpl:2309 - executing insertions
21:38:07,640 DEBUG EntityPersister:490 - Inserting entity: cbare.books.Author (native id)
21:38:07,640 DEBUG BatcherImpl:200 - about to open: 0 open PreparedStatements, 0 open ResultSets
21:38:07,640 DEBUG SQL:226 - insert into Author (firstName, lastName) values (?, ?)
Hibernate: insert into Author (firstName, lastName) values (?, ?)
21:38:07,640 DEBUG BatcherImpl:249 - preparing statement
21:38:07,640 DEBUG EntityPersister:388 - Dehydrating entity: [cbare.books.Author#<null>]
21:38:07,656 DEBUG AbstractEntityPersister:1236 - Natively generated identity: 2
21:38:07,656 DEBUG BatcherImpl:207 - done closing: 0 open PreparedStatements, 0 open ResultSets
21:38:07,656 DEBUG BatcherImpl:269 - closing statement
21:38:07,656 DEBUG Cascades:506 - done processing cascades for: cbare.books.Book
21:38:07,656 DEBUG SessionImpl:1387 - saveOrUpdate() unsaved instance
21:38:07,656 DEBUG SessionImpl:825 - saving [cbare.books.Book#<null>]
21:38:07,656 DEBUG SessionImpl:2309 - executing insertions
21:38:07,656 DEBUG Cascades:497 - processing cascades for: cbare.books.Book
21:38:07,656 DEBUG Cascades:506 - done processing cascades for: cbare.books.Book
21:38:07,656 DEBUG WrapVisitor:81 - Wrapped collection in role: cbare.books.Book.authors
21:38:07,656 DEBUG EntityPersister:490 - Inserting entity: cbare.books.Book (native id)
21:38:07,656 DEBUG BatcherImpl:200 - about to open: 0 open PreparedStatements, 0 open ResultSets
21:38:07,656 DEBUG SQL:226 - insert into Book (title, isbn) values (?, ?)
Hibernate: insert into Book (title, isbn) values (?, ?)
...

cbare · **Joined:** Sun Sep 19, 2004 12:22 am **Posts:** 6

I changed the List of Authors out for a Set, just to see if that would help.

For the record, I got the same result. Books and Authors are persisted to the DB as expected, but the associations never get created. (the book_authors table ends up empty).

Thanks for any clues. I'm just starting out w/ hibernate, so it's probably something obvious.

-Chris

nordmann · **Posted:** Tue Sep 21, 2004 12:46 pm

I'm somewhat of a beginner, too, but whenever I've used relation tables to get many-to-many relationships that preserve the order of one side of the relationship, I've set up a list on one side that has a one-to-many relationship, and a set on the side that is being ordered with the index, and that also has a one-to-many relationship.

Hope this helps.

cbare · **Joined:** Sun Sep 19, 2004 12:22 am **Posts:** 6

I tried simplifying my little problem from a many-to-many to a one-to-many relation and oddly enough, I still have the same problem. The foreign key never gets populated. I must either have some misconfiguration or I just totally don't understand how hibernate is supposed to work. (or C all of the above).

Am I right to expect that the foreign key field in my authors table would be filled in automatically?

Thanks for any help!

(and thanks to nordman for the previous reply)

-Chris

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC 
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="cbare.books">

   <class name="Book" table="books">
      <id name="id" unsaved-value="null">
         <generator class="native"/>
      </id>
      <property name="title" not-null="true"/>
      <property name="isbn"/>
      <set name="authors" cascade="save-update">
         <key column="book_id"/>
         <one-to-many class="cbare.books.Author"/>
      </set>
   </class>

</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC 
   "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="cbare.books">

   <class name="Author" table="authors">
      <id name="id" unsaved-value="null">
         <generator class="native"/>
      </id>
      <property name="firstName"/>
      <property name="lastName"/>
   </class>
      
</hibernate-mapping>

java code:

Code:

    public static void main(String[] args)
    throws Exception {
        Configuration cfg = new Configuration()
           .addClass(Book.class)
           .addClass(Author.class);
        SessionFactory factory = cfg.buildSessionFactory();

        Session session = null;
        try {
            Set books = testData();
            
            session = factory.openSession();
            Iterator iterator = books.iterator();
            while (iterator.hasNext()) {
                Book book = (Book)iterator.next();
                session.saveOrUpdate(book);
            }
        }
        finally {
            if (session!=null) session.close();
        }
   }

debugging output:

Code:

...
00:27:26,687  INFO Configuration:350 - Mapping resource: cbare/books/Book.hbm.xml
00:27:28,046  INFO Binder:229 - Mapping class: cbare.books.Book -> books
00:27:28,281  INFO Configuration:350 - Mapping resource: cbare/books/Author.hbm.xml
00:27:28,343  INFO Binder:229 - Mapping class: cbare.books.Author -> authors
00:27:28,359  INFO Configuration:627 - processing one-to-many association mappings
00:27:28,359  INFO Binder:1181 - Mapping collection: cbare.books.Book.authors -> authors
00:27:28,359  INFO Configuration:636 - processing one-to-one association property references
00:27:28,375  INFO Configuration:661 - processing foreign key constraints
00:27:28,437  INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.MySQLDialect
00:27:28,437  INFO SettingsFactory:59 - Maximim outer join fetch depth: 1
00:27:28,437  INFO SettingsFactory:63 - Use outer join fetching: true
...
00:27:28,906  INFO SettingsFactory:103 - Use scrollable result sets: true
00:27:28,906  INFO SettingsFactory:106 - Use JDBC3 getGeneratedKeys(): true
00:27:28,906  INFO SettingsFactory:109 - Optimize cache for minimal puts: false
00:27:28,906  INFO SettingsFactory:115 - echoing all SQL to stdout
00:27:28,906  INFO SettingsFactory:118 - Query language substitutions: {no='N', true=1, yes='Y', false=0}
...
Hibernate: insert into books (title, isbn) values (?, ?)
Hibernate: insert into authors (firstName, lastName) values (?, ?)
Hibernate: insert into authors (firstName, lastName) values (?, ?)
Hibernate: insert into authors (firstName, lastName) values (?, ?)
Hibernate: insert into authors (firstName, lastName) values (?, ?)
Hibernate: insert into books (title, isbn) values (?, ?)
Hibernate: insert into authors (firstName, lastName) values (?, ?)
Hibernate: insert into books (title, isbn) values (?, ?)
Hibernate: insert into authors (firstName, lastName) values (?, ?)
...

Code:

mysql> select * from books;
+----+--------------------------------------------------------------------+------------+
| id | title                                                              | isbn       |
+----+--------------------------------------------------------------------+------------+
|  1 | Introduction to Algorithms, Second Edition                         | 0262032937 |
|  2 | Algorithms on Strings, Trees, and Sequences                        | 0521585198 |
|  3 | Modern C++ Design: Generic Programming and Design Patterns Applied | 0201704315 |
|  4 | Structure and Interpretation of Computer Programs - 2nd Edition    | 0262011530 |
|  5 | Accelerated C++: Practical Programming by Example                  | 020170353X |
|  6 | Statistical Methods in Bioinformatics                              | 0387952292 |
|  7 | Generative Programming: Methods, Tools, and Applications           | 0201309777 |
+----+--------------------------------------------------------------------+------------+
7 rows in set (0.00 sec)

mysql> select * from authors;
+----+------------+--------------+---------+
| id | firstName  | lastName     | book_id |
+----+------------+--------------+---------+
|  1 | Ronald L.  | Rivest       |    NULL |
|  2 | Charles E. | Leiserson    |    NULL |
|  3 | Clifford   | Stein        |    NULL |
|  4 | Thomas H.  | Cormen       |    NULL |
|  5 | Dan        | Gusfield     |    NULL |
|  6 | Andrei     | Alexandrescu |    NULL |
|  7 | Gerald Jay | Sussman      |    NULL |
|  8 | Harold     | Abelson      |    NULL |
|  9 | Andrew     | Koenig       |    NULL |
| 10 | Barbara E. | Moo          |    NULL |
| 11 | Gregory R. | Grant        |    NULL |
| 12 | Warren J.  | Ewens        |    NULL |
| 13 | Krzysztof  | Czarnecki    |    NULL |
| 14 | Ulrich     | Eisenecker   |    NULL |
+----+------------+--------------+---------+
14 rows in set (0.00 sec)