Re: safe to "reuse" table for mapping of separate entities/relations?

[Andy Schlaikjer]

Hi Patrick,

Thanks for your response. I did see in documentation the note on 
specifying the mapping of a bidirectional relation on only one side of 
the relation, but in my case the nature of the bidirectional 
relationship is a bit more constrained: the A to B relation is ordered, 
whereas the B to A relation is unordered. I'm not aware of any mechanism 
in JPA by which I can specify an "orderBy" column within a join table, 
so I'm at a loss here.

Thanks,
Andy

Patrick Linskey wrote:
Hi,

If you're trying to model a bidirectional relationship, you should
only specify mapping info for one side and use the mappedBy annotation
property on the other side of the relation.

-Patrick

On 1/25/08, Andy Schlaikjer <[EMAIL PROTECTED]> wrote:
Hi all,

I was curious if/how I might get into trouble by reusing a table to
support mapping of an @Entity as well as a @JoinTable. Here's an example:

I have the following entities A, BRef, and B where a unidirectional
many-to-many relation between A and B is defined via BRef (acting here
as an explicit join table):

@Entity
class A {
   @Id
   protected long id;

   @OneToMany(mappedBy = "a")
   @OrderBy("idx")
   protected List<BRef> brefs;
}

@Entity
@Table(uniqueConstraints = @UniqueConstraint(columnNames = {"a_id",
"b_id"}))
class BRef {
   @Id
   protected long id;

   @ManyToOne(optional = false)
   protected A a;

   @ManyToOne(optional = false)
   protected B b;

   @Basic
   protected int idx;
}

@Entity
class B {
   @Id
   protected long id;
}

With this mapping (using the mysql DBDictionary), the following CREATE
TABLE statement reflects the BRef table:

CREATE TABLE `BRef` (
   `id` bigint(20) NOT NULL,
   `idx` int(11) default NULL,
   `a_id` bigint(20) default NULL,
   `b_id` bigint(20) default NULL,
   PRIMARY KEY  (`id`),
   UNIQUE KEY `a_id` (`a_id`,`b_id`),
   KEY `I_BREF_A` (`a_id`),
   KEY `I_BREF_B` (`b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Now I'd like to extend the mapping to include the reverse many-to-many
relation from B to A:

@Entity
class B {
   @Id
   protected long id;

   @OneToMany
   @JoinTable(
     name = "BRef",
     joinColumns = @JoinColumn(name = "b_id"),
     inverseJoinColumns = @JoinColumn(name = "a_id"),
     uniqueConstraints = @UniqueConstraint(columnNames = {"a_id", "b_id"})
   )
   protected Collection<A> as;
}

When I do this, the CREATE TABLE statement for table "BRef" changes
slightly (ordering of columns, names of indices):

CREATE TABLE `BRef` (
   `id` bigint(20) NOT NULL,
   `idx` int(11) default NULL,
   `b_id` bigint(20) default NULL,
   `a_id` bigint(20) default NULL,
   PRIMARY KEY  (`id`),
   UNIQUE KEY `a_id` (`a_id`,`b_id`),
   KEY `I_BREF_B_ID` (`b_id`),
   KEY `I_BREF_ELEMENT` (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So it is clear that the two separate mappings for table "BRef" are
competing with one another. How else might the OpenJPA runtime be
affected by this?

Thanks,
Andy