Recursive upserts with Hibernate/JPA-CodePudding

Java 11, Spring, Hibernate & MySQL here. I have some tables:

create table if not exists exam
(
    id           int(11)      not null auto_increment,
    name         varchar(100) not null,
    display_name varchar(250),
    constraint exam_pkey primary key (id),
);

create table if not exists section
(
    id           int(11)      not null auto_increment,
    exam_id      int(11)      not null,
    name         varchar(100) not null,
    display_name varchar(250),
    `order`      int(11)      not null,
    constraint section_pkey primary key (id),
    constraint section_exam_fkey foreign key (exam_id) references exam (id),
    constraint section_name_key unique (exam_id, name),
);

create table if not exists question
(
    id         int(11)      not null auto_increment,
    section_id int(11)      not null,
    name       varchar(100) not null,
    type       varchar(25)  not null,
    `order`    int(11)      not null,
    constraint question_pkey primary key (id),
    constraint question_exam_fkey foreign key (section_id) references section (id),
    constraint question_name_key unique (section_id, name),
);

And the JPA entity classes modeling them:

@Getter
@Setter
@Entity
@Table(name = "exam")
public class Exam {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Pattern(regexp = "\\w ")
    private String name;

    private String displayName;

    @OneToMany(mappedBy = "exam", fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @OrderBy("order asc")
    private SortedSet<Section> sections = new TreeSet<>();

}

@Getter
@Setter
@Entity
@Table(name = "section")
public class Section implements Comparable<Section> {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "exam_id")
    @JsonIgnore
    private Exam exam;

    @Pattern(regexp = "\\w ")
    private String name;

    private String displayName;

    @Column(name="`order`")
    private Long order;

    @OneToMany(mappedBy = "section", fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @OrderBy("order asc")
    private SortedSet<Question> questions = new TreeSet<>();

    @Override
    public int compareTo(Section other) {
        return ObjectUtils.compare(order, other.getOrder());
    }
}

// Question is a huge, complicated entity and I don't think I need to show it for
// someone to answer this question so I am omitting it for now, but I can add it
// in here if anyone thinks it makes a difference in providing the answer

And the repositories used for persisting them:

@Repository
public interface ExamRepository extends JpaRepository<Exam, Long> {
    Optional<Exam> findByName(String name);
}

Here is my situation:

at any given point in time, the database will have 0 exam records in it
- obviously if it has 0 exam entities in it, it will also not have any section or question entities in it either, and thus, all 3 tables would be completely empty (in this case)
- or, there could be hundreds of exam records, each with their own multiple sections, and each section with a multitude of its own question records (e.g. the tables are full of data)
my server will be receiving a new Exam instance (with its own "subtree" of Section and Question instances) from another source (not this MySQL DB), let us refer to these a "Imported Exams"
- it really shouldn't matter but basically files will be FTP'd into a folder and an asynchronous job deserializes these files into Exam instances
if one of these Imported Exams has a name value that matches any of the Exam entities in the MySQL DB, I want the imported Exam and its entire subtree object graph (all its sections, and each section's questions) to completely overwrite the matching DB Exam and its subtree/object graph
- so for example, if the DB has an Exam named "sally" and it has 1 section and that section has 4 questions, and then an imported Exam also has a name of "sally", I want it to completely overwrite the "DB sally" exam, and all of that exam's sections and questions, completely and recursively
- when this overwrite happens, all the sections and questions belonging to the "old" (existing) Exam are deleted and overwritten/replaced by the sections and questions of the new imported exam
but if the import Exam's name doesn't match any exam names in the DB, I want it inserted as a brand new Exam instance, with its whole entire subtree/object graph persisted to their respective tables

I have an ExamService for doing this:

@Service
public class ExamService {

  @Autowired
  private ExamRepository examRepository;

  public void upsertExamFromImport(Exam importedExam) {

    // for this demonstration, pretend importedExam.getName() is "sally" at runtime

    Optional<Exam> maybeExistingExam = examRepository.findByName(importedExam.getName());
    if (maybeExistingExam.isPresent()) {
      Exam existingExam = maybeExistingExam.get();

      // tell JPA/DB that the import IS the new matching exam
      importedExam.setId(existingExam.getId());
    }

    examRepository.save(importedExam);
 
  }

}

Currently my database does have an exam named "sally". So there will be a match.

When this code runs I get the following exception:

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

java.sql.SQLIntegrityConstraintViolationException: Column 'exam_id' cannot be null

So what I think is happening here is:

The code sees that there is an existing exam that matches the imported exam's name (hence maybeExistingExam is present and non-null); then
importedExam.setId(existingExam.getId()) executes and now the imported exam has the existing exam's ID, however its nested Section instances still have a null Exam reference (exam_id). Hence the Exam is considered "attached" yet its subtree is still considered (in JPA parlance) to be "detached".
When Hibernate goes to persist the imported exam's Sections, they are detached but since the parent Exam is attached, the exam_id is expected to be non-null

Even if that theory isn't completely accurate, I think I'm close enough. Regardless, what's the fix here? How do I tell Hibernate/JPA "hey pal, this imported exam matches an existing, so it needs to completely (recursively) overwrite the match in the DB"?

Update

If I try changing the service code to:

@Service
public class ExamService {

  @Autowired
  private ExamRepository examRepository;

  public void upsertExamFromImport(Exam importedExam) {

    // for this demonstration, pretend importedExam.getName() is "sally" at runtime

    Optional<Exam> maybeExistingExam = examRepository.findByName(importedExam.getName());
    examRepository.save(importedExam);
    if (maybeExistingExam.isPresent()) {
      Exam existingExam = maybeExistingExam.get();
      examRepository.delete(existingExam);
    }

  }

}

I get a ConstraintViolationException: Column 'exam_id' cannot be null exception when it executes examRepository.save(importedExam).

CodePudding user response：

I wasn't able to replicate your exact exceptions, but after tinkering a bit, i made it work, locally at least...

@Service
public class ExamService {

    @Autowired
    private ExamRepository examRepository;

    public void upsertExamFromImport(Exam importedExam) {
        Optional<Exam> maybeExistingExam = examRepository.findByName(importedExam.getName());
        if (maybeExistingExam.isPresent()) {
            Exam existingExam = maybeExistingExam.get();
            this.examRepository.delete(existingExam);
        }
        this.examRepository.save(importedExam);
    }
}

That's how i changed the service - i delete the existing exam, and then save the new one. To be honest that should not make difference, considering that your unique keys are composite and there will be new ids, but this the correct logical order, so it's better to stick to it.

You are already cascading persist and merge operations, so save should be ok. For delete to work you would need to add cascading for removal operations both for sections and questions.

@OneToMany(mappedBy = "exam", fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@OrderBy("order asc")
private SortedSet<Section> sections = new TreeSet<>();

In exams cascade sections removal.

@OneToMany(mappedBy = "section", fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@OrderBy("order asc")
private SortedSet<Question> questions = new TreeSet<>();

And cascade questions removal in sections.

CodePudding user response：

I ended up having to manually link the exam, its sections, and each section's questions together, bi-directionally. Prior to the save, and that fixed everything.

Example:

exam.getSections().forEach(section -> {
    section.setExam(exam);
    section.getQuestions().forEach(question -> {
        question.setSection(section);
    });
});

examRepository.save(exam);