一对多关系下，如何让新Poll的Choice ID从1开始自增？

实体类代码 #

Poll.java #

public class Poll {
    @Id
    @Column(name = "poll_id")
    String pollId;
    String question;
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm")
    String endDateTime;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "poll_id_fk", referencedColumnName = "poll_id")
    List<Choice> choices;
}

Choice.java #

public class Choice {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "choice_id")
    Long choiceId;
    String text;
    Integer votes;
}

问题说明 #

当前创建新Poll时，关联的Choice的choiceId会全局递增（比如上一个Poll的Choice用了1、2，新Poll的Choice就从3开始）。希望每个Poll的Choice的choiceId能从1开始重新计数，修改GenerationType策略没有效果。

期望返回结果 #

[
    {
        "pollId": "MvWRj",
        "question": "poll 2?",
        "endDateTime": "2023-07-09 19:50",
        "choices": [
            {
                "choiceId": 1,
                "text": "choice 1",
                "votes": 1
            },
            {
                "choiceId": 2,
                "text": "choice 2",
                "votes": 0
            }
        ]
    },
    {
        "pollId": "V838D",
        "question": "poll 1?",
        "endDateTime": "2023-07-09 19:50",
        "choices": [
            {
                "choiceId": 1,
                "text": "choice 1",
                "votes": 1
            },
            {
                "choiceId": 2,
                "text": "choice 2",
                "votes": 0
            }
        ]
    }
]

实际返回结果 #

[
    {
        "pollId": "MvWRj",
        "question": "poll 2?",
        "endDateTime": "2023-07-09 19:50",
        "choices": [
            {
                "choiceId": 3,
                "text": "choice 1",
                "votes": 1
            },
            {
                "choiceId": 4,
                "text": "choice 2",
                "votes": 0
            }
        ]
    },
    {
        "pollId": "V838D",
        "question": "poll 1?",
        "endDateTime": "2023-07-09 19:50",
        "choices": [
            {
                "choiceId": 1,
                "text": "choice 1",
                "votes": 1
            },
            {
                "choiceId": 2,
                "text": "choice 2",
                "votes": 0
            }
        ]
    }
]

解决方案 #

方案1：业务层手动赋值（简单直接） #

这种方式不需要修改数据库结构，只需要调整实体类和业务逻辑：

1. 修改Choice实体类，去掉@GeneratedValue注解，不再由数据库自动生成choiceId：

public class Choice {
    @Id
    @Column(name = "choice_id")
    Long choiceId;
    String text;
    Integer votes;
    
    // 补充getter、setter方法
}

2. 在保存Poll的业务逻辑中，遍历Choice列表，手动设置从1开始的序号：

public Poll savePoll(Poll poll) {
    List<Choice> choices = poll.getChoices();
    if (choices != null && !choices.isEmpty()) {
        for (int i = 0; i < choices.size(); i++) {
            choices.get(i).setChoiceId((long) (i + 1));
        }
    }
    return pollRepository.save(poll);
}

方案2：使用复合主键（数据库层面保证唯一性） #

如果需要数据库层面保证每个Poll下的choiceId唯一，可以采用复合主键（poll_id_fk + choice_id）：

1. 创建复合主键类ChoicePK，需实现Serializable接口，并重写equals和hashCode：

import java.util.Objects;
import javax.persistence.Column;
import javax.persistence.Embeddable;

@Embeddable
public class ChoicePK implements Serializable {
    @Column(name = "poll_id_fk")
    private String pollId;
    
    @Column(name = "choice_id")
    private Long choiceId;
    
    public ChoicePK() {}
    
    public ChoicePK(String pollId, Long choiceId) {
        this.pollId = pollId;
        this.choiceId = choiceId;
    }
    
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        ChoicePK choicePK = (ChoicePK) o;
        return Objects.equals(pollId, choicePK.pollId) && Objects.equals(choiceId, choicePK.choiceId);
    }
    
    @Override
    public int hashCode() {
        return Objects.hash(pollId, choiceId);
    }
    
    // 补充getter、setter方法
}

2. 修改Choice实体类，使用复合主键并关联Poll：

import javax.persistence.EmbeddedId;
import javax.persistence.ManyToOne;
import javax.persistence.MapsId;
import javax.persistence.JoinColumn;

public class Choice {
    @EmbeddedId
    private ChoicePK id;
    
    String text;
    Integer votes;
    
    @MapsId("pollId")
    @ManyToOne
    @JoinColumn(name = "poll_id_fk")
    private Poll poll;
    
    // 补充getter、setter方法
}

3. 修改Poll实体类的关联关系：

import javax.persistence.OneToMany;
import java.util.List;

public class Poll {
    @Id
    @Column(name = "poll_id")
    String pollId;
    String question;
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm")
    String endDateTime;

    @OneToMany(mappedBy = "poll", cascade = CascadeType.ALL, orphanRemoval = true)
    List<Choice> choices;
    
    // 补充getter、setter方法
}

4. 在保存Poll的业务逻辑中，给每个Choice设置复合主键：

public Poll savePoll(Poll poll) {
    List<Choice> choices = poll.getChoices();
    if (choices != null && !choices.isEmpty()) {
        for (int i = 0; i < choices.size(); i++) {
            Choice choice = choices.get(i);
            choice.setPoll(poll);
            choice.setId(new ChoicePK(poll.getPollId(), (long)(i + 1)));
        }
    }
    return pollRepository.save(poll);
}

内容的提问来源于stack exchange，提问作者binarystrength