mysql - 将 @jointable 与所有非唯一列一起使用

Question

我正在尝试创建一个调查。该调查由一系列问题组成。每个问题又由答案值的集合组成。

我已将问题与调查联系起来，如下所示:

@OneToMany()
@JoinTable(
        name = "survey_question_list",
        joinColumns = @JoinColumn(name = "survey_id"),
        inverseJoinColumns = @JoinColumn(name = "question_list_id"))
private Collection<Question> questionList = new ArrayList<Question>();

以同样的方式，我将答案值与每个问题联系起来:

@OneToMany()
@JoinTable(
        name = "question_answer_value_list",
        joinColumns = @JoinColumn(name = "question_id"),
        inverseJoinColumns = @JoinColumn(name = "answer_value_list_id"))
private Collection<AnswerValue> answerValueList = new ArrayList<AnswerValue>();

表格按照我的需要在数据库中创建:调查 ID 与问题 ID 关联，问题 ID 与答案值 ID 关联。

我的问题是我收到以下错误:

enteorg.mariadb.jdbc.internal.util.dao.QueryException: Duplicate entry '1' for key 'UK_eky8oymsfv24p3r4by54m15ta' 
Query is: insert into question_answer_value_list (question_id, answer_value_list_id) values (?, ?), parameters [2,1]

我看到的问题是其中一个 ID 是唯一的，这不允许多个相同的 ID。然而，我需要将每个问题映射到一个可能的答案，这意味着问题 ID 和答案 ID 必须出现多次。例子:如果问题 1 和问题 2 有 3 个可能的答案，那么映射将如下所示:

Question_Id  Answer_Value_Id
1            1
1            2
1            3
2            1
2            2
2            3
etc.         etc.

如何更改表格以实现此目的？

编辑:这是我的调查构建的逻辑:

@RequestMapping("/createsurvey2")
public Survey createSurvey2() {
    Survey survey = new Survey();
    survey.setName("My First Survey");
    // surveyRepository.save(survey);

    AnswerValue answerValue1 = new AnswerValue(1);

    answerValueRepository.save(answerValue1);

    Question question1 = new Question();
    question1.setQuestionContent("Was the Meeting Helpful?");
    question1.addAnswerValue(answerValue1);

    questionRepository.save(question1);

    survey.addQuestion(question1);

    Question question2 = new Question();
    question2.setQuestionContent("Was the Meeting Horrible?");
    questionRepository.save(question2);
    question2.addAnswerValue(answerValue1);

    questionRepository.save(question2);

    survey.addQuestion(question2);

    Question question3 = new Question();
    question3.setQuestionContent("Was the Meeting Funny?");
    questionRepository.save(question3);
    question3.addAnswerValue(answerValue1);

    questionRepository.save(question3);

    survey.addQuestion(question3);


    surveyRepository.save(survey);

    return survey;
}

Answer 1

也许可以尝试以下方法:

em.getTransaction().begin();

Answer a1 = new Answer(1, "Federal");
Answer a2 = new Answer(2, "State");
Answer a3 = new Answer(3, "Local");

List<Answer> q1_answers = Arrays.asList(a1, a2, a3);
Question q1 = new Question(1, q1_answers);

List<Answer> q2_answers = Arrays.asList(a1, a2, a3);
Question q2 = new Question(2, q2_answers);

List<Question> questions = Arrays.asList(q1, q2);
Survey s = new Survey(1, questions);

em.persist(s);

em.getTransaction().commit();

在简单的 Junit 测试中使用它，我能够很好地创建下表:

QUESTION_ID ANSWER_VALUE_LIST_ID
1                   1
1                   2
1                   3
2                   1
2                   2
2                   3

注意:我在@OneToMany 关系上使用了cascade=CascadeType.PERSIST:

@OneToMany(cascade = CascadeType.PERSIST)