@OneToMany relationship using a single @JoinColumn?

Question

I have the following tables (most essential columns shown only, A & B are not the real names btw):

table A {
  ...
}

table B {
  ...
}

table METADATA {
  KEY
  VALUE
  REF_A
  REF_B
}

METADATA holds additional key/value meta data for both table A & B. The key/value is needed as we have to handle dynamic data for which we cannot up front create columns for in A and B.

The entities are setup as (JPA using hibernate as provider):

interface Entity {
  ...
  getId()
  ...
}

class A implements Entity {
  ...
  @OneToMany(cascade = {ALL}, mappedBy = "a", orphanRemoval = true, fetch = LAZY)
  private List<MetaData> metaData;
  ...
  @Override
  public List<MetaData> getMetaData() {
    return metaData;
  }
  ...
}

class B implements Entity {
  ...   
  @OneToMany(cascade = {ALL}, mappedBy = "b", orphanRemoval = true, fetch = LAZY)
  private List<MetaData> metaData;
  ...
  @Override
  public List<MetaData> getMetaData() {
    return metaData;
  }
  ...
}

class MetaData implements Entity {
  ...
  @ManyToOne
  @JoinColumn(name = "REF_A", nullable = true)
  private A a;

  @ManyToOne
  @JoinColumn(name = "REF_B", nullable = true)
  private B b;
  ...
}

This setup works fine. However we have run into issues on some databases (for instance DB2) with a unique index we create (to ensure a meta key is only used once for a given row in A or B):

CREATE UNIQUE INDEX METADATA_UNIQUE_KEY ON METADATA (METAKEY, REF_A, REF_B)

as creating the index requires requires that all columns are non-null. This is not the case for use with the above design as the domain logic will either be that the meta data is set on A or B, hence one of these will always be null.

Possible solutions of course are to split the METADATA into two tables, one for A and one for B. However I would prefer to keep one table and instead just have one "REF" column which would either be an A or B as well as a TYPE column to say whether it's a meta data for an A or B. The TYPE would be needed as we have separate sequences for id for each table and a A and B could get the same technical id and hence get mixed up data otherwise.

My question is - is there any way to set this up with JPA?

For one-table based inheritance there is a @DiscriminatorValue which can be used to distinguish the specific stored sub-class, can this be used here as well? I am looking for something like:

table A {
  ...
}

table B {
  ...
}

table METADATA {
  KEY
  VALUE
  REF
  TYPE
}


@DiscriminatorValue("A")
class A implements Entity {
  ...
  @OneToMany(cascade = {ALL}, mappedBy = "entity", orphanRemoval = true, fetch = LAZY)
  private List<MetaData> metaData;
  ...
  @Override
  public List<MetaData> getMetaData() {
    return metaData;
  }
  ...
}

@DiscriminatorValue("B")
class B implements Entity {
  ...   
  @OneToMany(cascade = {ALL}, mappedBy = "entity", orphanRemoval = true, fetch = LAZY)
  private List<MetaData> metaData;
  ...
  @Override
  public List<MetaData> getMetaData() {
    return metaData;
  }
  ...
}

class MetaData implements Entity {
  ...
  @ManyToOne
  @JoinColumn(name = "REF", nullable = true)
  private Entity entity;

  @DiscriminatorColumn(name="TYPE", discriminatorType=STRING, length=20)      
  private String type;
  ...
}

so basically when a meta data is inserted for A this SQL would be used:

INSERT INTO METADATA (KEY, VALUE, REF, TYPE) VALUES ("metaKey", "metaValue", 1, "A")

Any suggestion are welcomed.

Rgs,

-Martin

Answer 1

I'm not sure why you need to create a key (metakey) in the metadata table, given thet the rows are already tied either to Table A or Table B.

However I think the problem is in considering the MetaData table an entity, given that its only purpose is to save some extra information of an existing entity, it means that you cannot have a row in MetaData without a row in TableA or TableB.

Instead of using relationship mapping one option is to use element collections to directly have the Map of key/value pairs in the corresponding entities:

@Entity
@Table(name="TableA")
public class TableA
{
    @Id
    @GeneratedValue(strategy= GenerationType.TABLE)
    private int id;

    @ElementCollection
    @CollectionTable(name="MetaData", joinColumns={@JoinColumn(name="TableA_id")})
    @MapKeyColumn(name="metaKey")
    @Column(name="metaValue")
    private Map<String, String> metadata;
}

@Entity
@Table(name="TableB")
public class TableB
{
    @Id
    @GeneratedValue(strategy= GenerationType.TABLE)
    private int id;

    @ElementCollection
    @CollectionTable(name="MetaData", joinColumns={@JoinColumn(name="TableB_id")})
    @MapKeyColumn(name="metaKey")
    @Column(name="metaValue")
    private Map<String, String> metadata;
}

Note that there is no java class for a "MetaData" table or entity, the table is automatically mapped from the @ElementCollection and @CollectionTable annotations.

The above mappings correspond to the following MetaData table:

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| TableA_id | int(11)      | YES  | MUL | NULL    |       |
| metaValue | varchar(255) | YES  |     | NULL    |       |
| metaKey   | varchar(255) | YES  |     | NULL    |       |
| TableB_id | int(11)      | YES  | MUL | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

If you prefer to keep a separate java class for MetaData to keep using a List instead of a Map, it can also be done with @ElementCollection, you just need to annotate the MetaData class with @Embeddable instead of @Entity. In that way it doesn't need an Id column like a regular entity.