Table Per Type - Making a Custom Join Formula in Hibernate / JPA?

Question

Consider the following SQL tables using a Table Per Type inheritance pattern:

CREATE TABLE person (
  name VARCHAR(50),
  address VARCHAR(50),
  is_faculty BOOLEAN,
  age SMALLINT NOT NULL,

  PRIMARY KEY(name, address, is_faculty)
);

CREATE TABLE student (
  name VARCHAR(50),
  address VARCHAR(50),
  current_grade SMALLINT,

  PRIMARY KEY(name, address)
);

CREATE TABLE faculty (
  name VARCHAR(50),
  address VARCHAR(50),
  department VARCHAR(2)

  PRIMARY KEY(name, address)
);

Obviously the idea here is that if, for example, I wanted to get a faculty member, I'd use the following join formula:

SELECT *
  FROM person
  INNER JOIN faculty
    ON person.name = faculty.name
    AND person.address = faculty.address
    AND person.is_faculty = true;

Now consider the following Hibernate entities representing said tables:

import javax.persistence.*;

@Entity
@Table(name = "person")
@IdClass(PersonId.class)
@Inheritance(strategy = InheritanceType.JOINED)
public class Person {
    @Id
    @Column(name = "name", length = 50)
    private String name;

    @Id
    @Column(name = "address", length = 50)
    private String address;

    @Id
    @Column(name = "is_faculty")
    private boolean isFaculty;

    @Column(name = "age", nullable = false)
    private int age;

    // Constructors, getters, setters, etc.
}

import java.io.Serializable;
import java.util.Objects;

public class PersonId implements Serializable {
    private String name;
    private String address;
    private boolean isFaculty;

    // Constructors, getters, setters, hashCode, equals, etc.
}

import javax.persistence.*;

@Entity
@Table(name = "student")
@PrimaryKeyJoinColumns({
    @PrimaryKeyJoinColumn(name = "name", referencedColumnName = "name"),
    @PrimaryKeyJoinColumn(name = "address", referencedColumnName = "address"),
    @PrimaryKeyJoinColumn(name = "is_faculty", referencedColumnName = "is_faculty"),
})
public class Student extends Person {
    @Column(name = "current_grade")
    private int currentGrade;

    // Constructors, getters, setters, etc.
}

import javax.persistence.*;

@Entity
@Table(name = "faculty")
@PrimaryKeyJoinColumns({
    @PrimaryKeyJoinColumn(name = "name", referencedColumnName = "name"),
    @PrimaryKeyJoinColumn(name = "address", referencedColumnName = "address"),
    @PrimaryKeyJoinColumn(name = "is_faculty", referencedColumnName = "is_faculty"),
})
public class Faculty extends Person {
    @Column(name = "department", length = 2)
    private String department;

    // Constructors, getters, setters, etc.
}

There's a clear problem here however; the faculty and student tables have no is_faculty column. But we still need to join on either true or false for is_faculty, as shown in the SELECT statement above.

Normally, this would be solved by using @JoinColumnsOrFormulas / @JoinColumnOrFormula with @JoinFormula and @JoinColumn if this weren't an inheritance based join. However, to my knowledge no such alternative exists, such as @PrimaryKeyJoinColumnsOrFormulas.

My workaround so far has been to just add an is_faculty column to the faculty and student tables, so that both tables share the exact same composite PK columns (letting Hibernate join them using PersonId).

2 question: *what exactly are you trying to do?* and *are you in control of the schema?* It seems that what you actually want is a `Person` base entity with 2 child entities, `Student` and `Faculty`. Do you really need to reflect this inheritance in the DB? If so and if you are in control of the schema, you can change the `is_faculty` to `person_type` and make it a discriminator column (see e.g. [this](https://stackoverflow.com/questions/4275517)). Also, I feel that `isFaculty` should not be part of the PK. — Nikos Paraskevopoulos, Jul 24 '23 at 15:52
@NikosParaskevopoulos Crap, that's right, `Student` and `Facility` are both child entities, I just forgot to add `extends Person` to `Faculty`. On that note, I am in control of the schema, but these tables are just simplified examples to reproduce my problem, and not my actual schema; in this case, `is_faculty` has to be part of the primary key. I think you may be onto something there with the discriminator + secondary table, I'm gonna have to check that out. Thanks. — Twisted Tea, Jul 24 '23 at 17:17
@NikosParaskevopoulos using `@SecondaryTable` and a discriminator column didn't work in this situation, since the discriminator column (`is_faculty`) is part of the composite key (`org.hibernate.AnnotationException: The '@JoinColumn' for a secondary table must reference the primary key`) — Twisted Tea, Aug 01 '23 at 16:59
@TwistedTea Are you able to change the primary key structure? I.e. just a single attribute like an Id? You still could use `is_faculty` as discriminator. E.g. `@Inheritance(strategy=InheritanceType.SINGLE_TABLE)` and `@DiscriminatorColumn(name="is_faculty", discriminatorType=DiscriminatorType.BOOLEAN)` on the `Person` entity. If that suits you, I might be able to write an answer :) — J. M. Arnold, Aug 07 '23 at 09:21
@J.M.Arnold sorry, a single primary key just doesn’t make sense in my exact situation. the discriminator needs to be part of the composite primary key in the parent table. — Twisted Tea, Aug 07 '23 at 12:11

Amira Bedhiafi · Answer 1 · 2023-08-12T17:07:16.803

If the discriminator must be part of the composite primary key, then your initial workaround is a reasonable approach given the constraints of Hibernate and SQL. You can add the is_faculty column to both the student and faculty tables, and ensure that they are set to true or false accordingly.

You may still encapsulate this logic within your application, ensuring that the column is managed transparently and the correct value is always used:

@Entity
@Table(name = "student")
@PrimaryKeyJoinColumns({
    @PrimaryKeyJoinColumn(name = "name", referencedColumnName = "name"),
    @PrimaryKeyJoinColumn(name = "address", referencedColumnName = "address"),
    @PrimaryKeyJoinColumn(name = "is_faculty", referencedColumnName = "is_faculty"),
})
public class Student extends Person {

    @Column(name = "current_grade")
    private int currentGrade;

    @Column(name = "is_faculty", nullable = false, insertable = false, updatable = false)
    private boolean isFaculty = false;

    // Constructors, getters, setters, etc.
}

@Entity
@Table(name = "faculty")
@PrimaryKeyJoinColumns({
    @PrimaryKeyJoinColumn(name = "name", referencedColumnName = "name"),
    @PrimaryKeyJoinColumn(name = "address", referencedColumnName = "address"),
    @PrimaryKeyJoinColumn(name = "is_faculty", referencedColumnName = "is_faculty"),
})
public class Faculty extends Person {

    @Column(name = "department", length = 2)
    private String department;

    @Column(name = "is_faculty", nullable = false, insertable = false, updatable = false)
    private boolean isFaculty = true;

    // Constructors, getters, setters, etc.
}

The insertable = false, updatable = false attributes make it clear that the application will not directly modify these values, and you can set the values in your constructors or setters, as needed.

I appreciate the acknowledgement that this is probably the best approach given Hibernates current state, but this belongs as a comment, not an answer. — Twisted Tea, Aug 14 '23 at 03:20

Table Per Type - Making a Custom Join Formula in Hibernate / JPA?

1 Answers1