CODEWITHSUNDEEP
jpadatabase-designmodelingpolymorphic-associations
Hanno Fietz
Hanno Fietz

Reputation: 31370

How do I express a polymorphic association in JPA?

A polymorphic association is similar to a foreign key or many-to-one relationship, with the difference being that the target might be one of a number of types (classes in the language, tables in the db).

I'm porting a database design I've been using for some years from PHP to Java. In the old code, I had rolled my own ORM, which wasn't optimal for a number of reasons. Although I might start to tweak things later, and maybe end up implementing things myself again, for now I'd like to use an off-the-shelf ORM and JPA on my entity classes.

Now, there's one thing about the database layout that I don't know how to express in JPA:

I have a Node and an Edge table storing a graph (a DAG, if it matters). Each node may optionally reference one other entity from the database. These entites may be refrenced multiple times throughout the graph and there may also be "orphaned" entites, which wouldn't be accesible for the user, but which may make sense to keep at least for a while.

These objects are not at all related in terms of inheritance etc. but have a natural hierarchy, similar to Customer->Site->Floor->Room. In fact, years ago, I started out with just foreign key fields pointing to the "parent" objects. However, this hierarchy isn't flexible enough and started falling apart.

For example, I want to allow users to group objects in folders, some objects can have multiple "parents" and also the relations change over time. I need to keep track of how the relations used to be, so the edegs of the graph have a timespan associated with them, that states from when to when that edge was valid.

The link from a node to an object is stored in two columns of the node table, one carries the id in the foreign table, one carries its name. For example (some columns omitted):

table Node:
+--------+-------+----------+
| ixNode | ixRef | sRefType |
+--------+-------+----------+
|    1   |  NULL |   NULL   |  <-- this is what a "folder" would look like
|    2   |   17  |  Source  |
|    3   |   58  |  Series  |  <-- there's seven types of related objects so far
+--------+-------+----------+

table Source (excerpt):
+----------+--------------------+
| ixSource |        sName       |
+----------+--------------------+
|    16    | 4th floor breaker  |
|    17    | 5th floor breaker  |
|    18    | 6th floor breaker  |
+----------+--------------------+

There might be a different solution than using JPA. I could change something about the table layout or introduce a new table etc. However, I have thought about this a lot already and the table structure seems OK to me. Maybe there's also a third way that I didn't think of.

Upvotes: 5

Views: 10849

Answers (4)

Grzegorz Oledzki
Grzegorz Oledzki

Reputation: 24261

The answer would be:

  • inheritance (as suggested already by Mike)
  • plus @DiscriminatorColumn to provide information which column stores the information about which subclass should be used: sxRef. The only doubt I see is the "sxRef" being a nullable column. I guess that it's forbidden.

Upvotes: 5

mtpettyp
mtpettyp

Reputation: 5569

Have you looked at the @Any annotation? It's not part of JPA but is a Hibernate Annotation extension to it.

Upvotes: 2

Mike
Mike

Reputation: 18974

I think you've already hit on an answer. Create an abstract class (either @Entity or @MappedSuperclass) and have the different types extend it.

Something like this might work

@MappedSuperclass
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public abstract class Edge { 
    // . . .
    @OneToMany
    Collection<Node> nodes; 
}

@Entity 
public class Source extends Edge { 
}

@Entity public class Series extends Edge { 
}

@Entity
public class Node { 
    // . . .
    @ManyToOne
    Edge edge; 
}

I understand you might not want to imply a relationship between the Source and Series, but extending a common abstract (table-less) class is the only way I can think of to do what you want.

InheritanceType.TABLE_PER_CLASS will keep Source and Series in separate tables (you could use SINGLE_TABLE to do something like the previous answer).

If this isn't what you're looking for, many JPA providers provide a tool that creates mappings based on an existing set of tables. In OpenJPA it's called the ReverseMappingTool [1]. The tool will generate Java source files that you can use as a starting point for your mappings. I suspect Hibernate or EclipseLink have something similar, but you could just use the OpenJPA one and use the entity definitions with a different provider (the tool doesn't generate any OpenJPA specific code as far as I know).

[1] http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_pc_reverse

Upvotes: 5

Peter Recore
Peter Recore

Reputation: 14187

How much information is stored in the Source and Series tables? Is it just a name? If so, you could combine them into one table, and add a "type" column. Your Node table would lose its sRefType, and you would have a new table that looks like this:

ixSource        sName                  sType
  16            4th floor breaker      SOURCE
  17            5th floor breaker      SOURCE
  18            6th floor breaker      SOURCE
  19            1st floor widget       SERIES
  20            2nd floor widget       SERIES

This table would replace the Source and Series tables. Do Source and Series both belong to a superclass? That would be a natural name for this table.

Upvotes: 0

Related Questions