Java 11、Spring、Hibernate 和 MySQL 在這里。我有一些桌子：
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),
);
以及對它們進行建模的 JPA 物體類：
@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
以及用于持久化它們的存盤庫：
@Repository
public interface ExamRepository extends JpaRepository<Exam, Long> {
Optional<Exam> findByName(String name);
}
這是我的情況：
- 在任何給定時間點，資料庫中都會有 0
exam條記錄- 顯然，如果它有 0個
exam物體，它也不會有任何section或question物體，因此，所有 3 個表都將是完全空的（在這種情況下） - 或者，可能有數百條
exam記錄，每條記錄都有自己的多個sections，并且每個部分都有大量自己的question記錄（例如，表格中充滿了資料）
- 顯然，如果它有 0個
- my server will be receiving a new
Examinstance (with its own "subtree" ofSectionandQuestioninstances) 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
Examinstances
- it really shouldn't matter but basically files will be FTP'd into a folder and an asynchronous job deserializes these files into
- if one of these Imported
Exams has anamevalue that matches any of theExamentities in the MySQL DB, I want the importedExamand its entire subtree object graph (all its sections, and each section's questions) to completely overwrite the matching DBExamand 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
Examalso 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)
Examare deleted and overwritten/replaced by the sections and questions of the new imported exam
- 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
- but if the import Exam's name doesn't match any exam names in the DB, I want it inserted as a brand new
Examinstance, 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
maybeExistingExamis present and non-null); then importedExam.setId(existingExam.getId())executes and now the imported exam has the existing exam's ID, however its nestedSectioninstances still have anullExamreference (exam_id). Hence theExamis 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 parentExamis attached, theexam_idis 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).
uj5u.com熱心網友回復：
我無法復制您的確切例外情況，但經過一番修補后，我使它作業，至少在本地...
@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);
}
}
這就是我更改服務的方式——我洗掉了現有的考試，然后保存新的考試。老實說，考慮到您的唯一鍵是復合的并且會有新的ID，這應該沒有什么區別，但這是正確的邏輯順序，所以最好堅持下去。
您已經在級聯持久化和合并操作，所以保存應該沒問題。要使洗掉作業，您需要為部分和問題添加級聯洗掉操作。
@OneToMany(mappedBy = "exam", fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@OrderBy("order asc")
private SortedSet<Section> sections = new TreeSet<>();
在考試中級聯部分洗掉。
@OneToMany(mappedBy = "section", fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@OrderBy("order asc")
private SortedSet<Question> questions = new TreeSet<>();
并在部分中洗掉級聯問題。
uj5u.com熱心網友回復：
我最終不得不手動將考試、其部分和每個部分的問題雙向鏈接在一起。在保存之前，這解決了所有問題。
例子：
exam.getSections().forEach(section -> {
section.setExam(exam);
section.getQuestions().forEach(question -> {
question.setSection(section);
});
});
examRepository.save(exam);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/435815.html