Sunday, March 4, 2007

Many-to-Many mapping in Oracle TopLink

This kind of mapping in relational world is constructed using three tables. However, in object world only two classes are used for many-to-many. I have developed sample application -, that shows how many-to-many is supported in Oracle TopLink. For this sample I'm using Readers/Blogs mapping - one reader can read many blogs and one blog can be read by many readers.

SQL scripts for database schema are available in sample application lt.andrejusb.utils package under Model project.

Many-to-many mapping in Oracle TopLink is enabled by adding attribute with ValueHolderInterface type and corresponding accessors to the one of two classes involved into relationship. In developed sample I have added ValueHolderInterface type attribute - blogs, into Reader class. When accessors are created, it's time to enable many-to-many mapping using Oracle TopLink wizard in JDeveloper. To do this expand descriptor for Reader and double-click blogs element, this element is unmapped. When wizard dialog will be opened, set values for Reference Descriptor and Relation Table. Also, select - Private Owned and choose source and target references. General tab in mapping wizard should look like this:

If many-to-many will be successfully configured, JDeveloper will show appropriate icon for blogs attribue:

So, mapping is enabled, now we can test it. I have developed JUnit test suite, where insert and select is performed. Many-to-many test method code:

JUnit shows green light - there is no errors:


Tammer Salem said...

Hi Andrejus,
this is an excellent example of managing many-to-many realtionships using toplink. I just have one comment:
Wouldn't it be a good idea to also show how to map between existing relational objects (i.e. in the sample you create two readers, one blog, then create them all - caouldn't you also show if the objects already existed with no mapping, how you would create the mapping).

Andrej Baranovskij said...

Hi Tammar,

Thanks for interesting suggestion. Just to clarify - your idea is to load already existent data from DB and map it with TopLink?