Persisting superclass and update it later in a subclass using Spring Boot Data JPA inheritance with no duplication

Question

I was trying to use spring boot data JPA inheritance to achieve the following:

• Create and persist a object from a Superclass Entity (Staff)
• Based on an existing staff made with the Superclass Entity, create a Subclass Entity (TeachingStaff) object, update it and persist it without duplication in the staff table.

Here's an example code:

SQL:

CREATE TABLE Staff (
    staff_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

CREATE TABLE TeachingStaff (
    staff_id INT PRIMARY KEY,
    department VARCHAR(50) NOT NULL,
    FOREIGN KEY (staff_id) REFERENCES Staff(staff_id)
);

Java:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Staff {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "staff_id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "email")
    private String email;

    @Column(name = "salary")
    private Double salary;

    // constructors, getters and setters
}

@Entity
public class TeachingStaff extends Staff {

    @Column(name = "department")
    private String department;

    @Column(name = "subject_taught")
    private String subjectTaught;

    // constructors, getters and setters
}

Staff repository:

@Repository
public interface StaffRepo extends JpaRepository<Staff, Long> {
    Optional<Staff> findByEmail(String email);
}

TeachingStaff repository:

@Repository
public interface TeachingStaffRepo extends JpaRepository<TeachingStaff, Long> {}

Adding and updating a new staff (I'm omitting the service layer for the sake of brevity):

@Bean
CommandLineRunner run(StaffRepo staffRepo, TeachingStaffRepo teachingStaffRepo) {
    return args -> {
        // Adding a new staff
        Staff newStaff = new Staff("John Doe", "[email protected]", 2000.0);
        staffRepo.save(newStaff);

        // Retrieving and updating an existing staff
        Staff existingStaff = staffRepo.findByEmail("[email protected]").orElseThrow();
        TeachingStaff newTeachingStaff = new TeachingStaff();
        newTeachingStaff.setId(existingStaff.getId());
        newTeachingStaff.setName(existingStaff.getName());
        newTeachingStaff.setEmail(existingStaff.getEmail());
        newTeachingStaff.setSalary(existingStaff.getSalary());
        newTeachingStaff.setDepartment("Mathematics");
        newTeachingStaff.setSubjectTaught("Calculus");
        teachingStaffRepo.save(newTeachingStaff);
    };
}

I've been trying different methods for a few days now but no luck yet. I tried all strategies in @Inheritance and @MappedSuperclass but they didn't work as expected.

The only method that creates the same SQL schema as the above one is by using InheritanceType.JOINED. The problem though is that when I want to update a staff and make him a teaching staff, a duplicate record is inserted in the staff table.

I read that this can be achieved by detaching the retrieved object (existingStaff), but this didn't resolve the duplication problem.

Is there a solution to this problem in Spring Boot Data JPA?

Answer 1

After days of searching and testing different methods I reached the conclusion that composition is the only built-in solution in Hibernate to my OOP-to-Relational-DB design.

Another solution is to add a custom method and implement a custom SQL (or JPQL) query in the Subclass' repository as follows:

@Modifying
@Query(value = "INSERT INTO teaching_staff " +
        "(staff_id, department, subject_taught)" +
        "   VALUES (:#{#t.id}, :#{#t.department}, :#{#t.subjectTaught})", nativeQuery = true)
@Transactional
void makeTeachingStaff(@Param("t") TeachingStaff teachingStaff);