Many-To-Many Self-Reference

gederer · **Joined:** Wed Oct 25, 2006 8:25 pm **Posts:** 9

Hi,

I have a Category class. According to my model, a Category can have many parent Categorys and many child Categorys. I'm having trouble getting the (JPA) annotations right for this relationship. I'm sure that this must be trivially easy. What is the correct way to do this?

Thanks!

Greg

LaLiLuna · **Posted:** Thu Sep 06, 2007 6:36 pm

You will need a @JoinTable Annotation as well. Have a look in the Annotation reference, there should be examples.

gederer · **Joined:** Wed Oct 25, 2006 8:25 pm **Posts:** 9

I have:

Code:

@ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE,
         CascadeType.REFRESH })
   @JoinTable(name = "toy_category_toy_category", joinColumns = @JoinColumn(name = "parent_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "child_id", referencedColumnName = "id"))
   private List<ToyCategory> parentCategories = new ArrayList<ToyCategory>();

   @OneToMany(mappedBy = "parentCategories", cascade = { CascadeType.PERSIST,
         CascadeType.MERGE, CascadeType.REFRESH })
   private List<ToyCategory> childCategories = new ArrayList<ToyCategory>();

This creates the join table. My sample data look like:

Code:

  <table name="toy_category">
    <column>id</column>
    <column>name</column>
    <column>description</column>
    <row>
      <value>1</value>
      <value><![CDATA[Animals]]></value>
      <value><![CDATA[Root Category]]></value>
    </row>
    <row>
      <value>2</value>
      <value><![CDATA[Cats]]></value>
      <value><![CDATA[Cats]]></value>
    </row>
    <row>
      <value>3</value>
      <value><![CDATA[Cheshire Cats]]></value>
      <value><![CDATA[Cheshire Cats]]></value>
    </row>
  </table>
  <table name="toy_category_toy_category">
    <column>parent_id</column>
    <column>child_id</column>
    <row>
      <value description="parent_id">1</value>
      <value description="child_id">2</value>
    </row>
    <row>
      <value description="parent_id">2</value>
      <value description="child_id">3</value>
    </row>
  </table>

But, when I run my tests, 'Cats' (id=2) has parent 'Cheshire Cats', and zero children. 'Cats' should have parent 'Animals' and child 'Cheshire Cats'.

I think I'm close. What am I missing?

Thanks!

Greg

LaLiLuna wrote:

You will need a @JoinTable Annotation as well. Have a look in the Annotation reference, there should be examples.

gederer · **Joined:** Wed Oct 25, 2006 8:25 pm **Posts:** 9

Ooops! That should have been:

Code:

   @ManyToMany
   @JoinTable(
          name="parent_child_category",
          joinColumns=@JoinColumn(name="parent_id", referencedColumnName="id"),
          inverseJoinColumns=@JoinColumn(name="child_id", referencedColumnName="id")
   ) 
   private List<ToyCategory> parentCategories = new ArrayList<ToyCategory>();

   @ManyToMany(mappedBy = "parentCategories")
   private List<ToyCategory> childCategories = new ArrayList<ToyCategory>();

With a ManyToMany annotation on childCategories instead of a OneToMany.

Now, with test data:

Code:

  <table name="toy_category">
    <column>id</column>
    <column>name</column>
    <column>description</column>
    <row>
      <value>1</value>
      <value><![CDATA[Toy Vehicles]]></value>
      <value><![CDATA[Toy Vehicles]]></value>
    </row>
    <row>
      <value>2</value>
      <value><![CDATA[Toy Aircraft]]></value>
      <value><![CDATA[Toy Aircraft]]></value>
    </row>
    <row>
      <value>3</value>
      <value><![CDATA[Toy Planes]]></value>
      <value><![CDATA[Toy Planes]]></value>
    </row>
  </table>
  <table name="parent_child_category">
    <column>parent_id</column>
    <column>child_id</column>
    <row>
      <value description="parent_id">1</value>
      <value description="child_id">2</value>
    </row>
    <row>
      <value description="parent_id">2</value>
      <value description="child_id">3</value>
    </row>
  </table>

my tests give me:

Code:

INFO - ToyCategoryDaoTest.testGetToyCategory(20) | Got ToyCategory: Toy Aircraft
INFO - ToyCategoryDaoTest.testGetToyCategory(27) |   Parents: Toy Planes | 
INFO - ToyCategoryDaoTest.testGetToyCategory(34) |   Children: Toy Vehicles |

My parents and children seem to be swapped. Not sure what I'm doing wrong.

gederer wrote:

I have:

Code:

@ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE,
         CascadeType.REFRESH })
   @JoinTable(name = "toy_category_toy_category", joinColumns = @JoinColumn(name = "parent_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "child_id", referencedColumnName = "id"))
   private List<ToyCategory> parentCategories = new ArrayList<ToyCategory>();

   @OneToMany(mappedBy = "parentCategories", cascade = { CascadeType.PERSIST,
         CascadeType.MERGE, CascadeType.REFRESH })
   private List<ToyCategory> childCategories = new ArrayList<ToyCategory>();

This creates the join table. My sample data look like:

Code:

  <table name="toy_category">
    <column>id</column>
    <column>name</column>
    <column>description</column>
    <row>
      <value>1</value>
      <value><![CDATA[Animals]]></value>
      <value><![CDATA[Root Category]]></value>
    </row>
    <row>
      <value>2</value>
      <value><![CDATA[Cats]]></value>
      <value><![CDATA[Cats]]></value>
    </row>
    <row>
      <value>3</value>
      <value><![CDATA[Cheshire Cats]]></value>
      <value><![CDATA[Cheshire Cats]]></value>
    </row>
  </table>
  <table name="toy_category_toy_category">
    <column>parent_id</column>
    <column>child_id</column>
    <row>
      <value description="parent_id">1</value>
      <value description="child_id">2</value>
    </row>
    <row>
      <value description="parent_id">2</value>
      <value description="child_id">3</value>
    </row>
  </table>

But, when I run my tests, 'Cats' (id=2) has parent 'Cheshire Cats', and zero children. 'Cats' should have parent 'Animals' and child 'Cheshire Cats'.

I think I'm close. What am I missing?

Thanks!

Greg

LaLiLuna wrote:

You will need a @JoinTable Annotation as well. Have a look in the Annotation reference, there should be examples.

LaLiLuna · **Posted:** Fri Sep 07, 2007 3:01 am

There is one many to many and from the other side you use oneToMany. You will have to decide. Once side needs a "mappedBy" property as well.

gederer · **Joined:** Wed Oct 25, 2006 8:25 pm **Posts:** 9

Hi,

I figured this out with some help from the persistence-glassfish folks (who can make JPA do back flips, and are terrific helpers!)

Here's the solution, in case anyone's interested:

Code:

        @ManyToMany
        @JoinTable(
               name="parent_child_category",
               joinColumns=@JoinColumn(name="child_id", referencedColumnName="id"),
               inverseJoinColumns=@JoinColumn(name="parent_id", referencedColumnName="id")
        )
        private List<ToyCategory> parentCategories = new ArrayList<ToyCategory>();

        @ManyToMany(mappedBy = "parentCategories")
        private List<ToyCategory> childCategories = new ArrayList<ToyCategory>();

Now my categories can have multiple parents. Sweet!

Cheers,

Greg