5

I have the following JPA 2.0 Entities

@Entity
@Inheritance(strategy= InheritanceType.JOINED)
public abstract class BookKeepingParent implements Serializable {
    @Id
    protected Long Id;
    ...
}

@Entity
public class Employee extends BookKeepingParent {
    private String name;

    @ManyToOne
    private Role role;
    ...
}

@Entity
public class Role extends BookKeepingParent {
    private String name;
    ...
}

I want to let JPA generate tables for me, since it makes it easier to install at multiple locations. I would usually expected it to generate this:

CREATE TABLE bookkeepingparent (
  id bigint NOT NULL,
  dtype character varying(31),
  CONSTRAINT bookkeepingparent_pkey PRIMARY KEY (id )
)

CREATE TABLE role (
  id bigint NOT NULL,
  name character varying(255),
  CONSTRAINT role_pkey PRIMARY KEY (id ),
  CONSTRAINT fk_role_id FOREIGN KEY (id) REFERENCES bookkeepingparent (id)
)

CREATE TABLE employee (
  id bigint NOT NULL,
  name character varying(255),
  role_id bigint,
  CONSTRAINT employee_pkey PRIMARY KEY (id ),
  CONSTRAINT fk_employee_id FOREIGN KEY (id) REFERENCES bookkeepingparent (id),
  CONSTRAINT fk_employee_role_id FOREIGN KEY (role_id) REFERENCES role (id)
)

First two tables where the same, but it generated the employee table this way:

CREATE TABLE employee (
  id bigint NOT NULL,
  name character varying(255),
  role_id bigint,
  CONSTRAINT employee_pkey PRIMARY KEY (id ),
  CONSTRAINT fk_employee_id FOREIGN KEY (id) REFERENCES bookkeepingparent (id),
  CONSTRAINT fk_employee_role_id FOREIGN KEY (role_id) REFERENCES bookkeepingparent (id)
)

You can notice that the fk_employee_role_id references the bookkeepingparent table, instead of the role table. I have a large heirarchy of JPA entities, and I want the bookkeepingparent to be the superclass of most of them. This is primarily because of some very specific Id generation strategies and other bookkeeping activities. This design, helps keep all this book keeping code separate from the functional code, and let programmers working on the functional code not worry about it.

All this worked alright till the the number of tables grew. Now we see that, for all ManyToOne and OneToOne relationships, JPA is generating foreign keys referring to the parent table. With 200 odd tables, the inserts are already slow because all foreign key constraints refer to the bookekeepingparent, and every entity, when persisted for the first time, inserts into the bookkeeping parent table. Which I guess is checking some 150 odd constraints.

So, following are my questions: why is JPA doing it? Is it a standard JPA behaviour? (I'm using EclipseLink) If I manually change the DB schema, are their any pitfalls to expect?

This is my first question on StackOverflow, I tried my best to search for any existing answers. Apologies if I missed any. Thanks.

Alex
  • 21,273
  • 10
  • 61
  • 73
Nikhilesh
  • 51
  • 2
  • I agree with you that the FK should reference subclasses. It seems to as a bug in EclipseLink, since such mapping is apparently wrong and doesn't reflects relations correctly. – Ondrej Bozek Oct 18 '13 at 13:24

1 Answers1

1

You are using joined inheritance, which means that for every class, the bookkeepingparenttable is the main table and any subclass table is secondary. The primary key for the subclasses are inherited from the parent, and foreign keys must reference the id, so the will all reference the id in bookkeepingparenttable by design. Different providers allow referencing non-pk fields, but it can cause problems as resolving references can require database hits instead of using the cache.

The database constraints are not JPA related, so you can change them as required and not affect the app as long as inserts updates and deletes will still conform.

Chris
  • 20,138
  • 2
  • 29
  • 43
  • Let's say, JPA needs caches for operations like EntityManager.find(). As the ids of superclass and subclass are the same, it doesn't matter which one you use as the 'key' of the cache. Also, about the DB constraints - the ones EclipseLink is creating is sort of 'inaccurate' - having a foreign key referring to the superclass allows ids of tables other than Role to be added in the role column of Employee. So my inserts and updates are surely going to conform to the constraint, because mine are narrower than the ones EclipseLink puts. So I still don't get why they do it. Thanks anyways. – Nikhilesh Jun 10 '13 at 06:45
  • As mentioned, the main table is the bookkeepingparent, and so the defaults will always point to this table in EclipseLink. You can change it by defining the joincolumn on the mapping to go to the secondary table, which will change the constraint created for DDL- but it likely would have the draw back of not hitting the cache. So its probably better to change your DDL scripts than the mappings – Chris Jun 10 '13 at 13:31
  • If I change DDL scripts so that FK references subclass table I receive constraint violation error. So it's not a solution. To me it seems as a bug in EclipseLink since it doesn't represent object relations acurately. – Ondrej Bozek Oct 18 '13 at 13:28
  • JPA specifies that children use the parent's primary key, and that references must always be to the primary key, so it works according to the specification. That said, it comes up occasionally as shown here https://bugs.eclipse.org/bugs/show_bug.cgi?id=333100 . – Chris Oct 18 '13 at 15:02
  • The children table has a primary key too. So it can be easily referenced by a foreign key. – Denis Dec 06 '19 at 11:20
  • Is that a comment or a question? Yes, the DB table design shown is not a problem for the database. It doesn't match the java model though, as while the subclass has its own ID field, the value with in it for JPA/caching etc is the parent table ID field. There is no point in having another constraint/fk to a subtable, so EclipseLink won't generate it and instead treat it like any parent class instance - and go to the parent. It is a simplification that still works. You should be writing your own DDL based on your app requirements anyway. – Chris Dec 11 '19 at 16:41