JPA一对多关联中子表ID未重置延续主表编号问题求助

问题描述 #

我有两个存在一对多（One-to-Many）关联关系的数据库表，目前出现异常：子表ExchangeRate的记录ID编号会延续主表Result的ID编号，而非重新从1开始编号。

实体类代码 #

Result 实体类 #

@Entity
public class Result {
    @Id
    @GeneratedValue
    private Long id;
    private String date;
    private String bank;
    private Integer baseCurrency;
    private String baseCurrencyLit;

    @OneToMany(mappedBy = "result", cascade = CascadeType.ALL)
    //@OrderColumn(name = "array_index")
    //private ExchangeRate[] exchangeRate;
    private List<ExchangeRate> exchangeRate;
}

ExchangeRate 实体类 #

@Entity
public class ExchangeRate {
    @Id
    @GeneratedValue
    private Integer id;
    private String baseCurrency;
    private String currency;
    private Float saleRateNB;
    private Float purchaseRateNB;
    private Float saleRate;
    private Float purchaseRate;

    @ManyToOne
    @JoinColumn(name= "result_id")
    private Result result;
}

补充信息 #

附上Result表的相关截图

解决方案 #

问题根源在于@GeneratedValue注解未指定独立的ID生成策略，默认情况下JPA可能让多个实体共享同一个序列/生成器，导致ID编号延续。针对不同数据库类型，可采用以下配置：

1. 适配MySQL等支持自增的数据库 #

直接使用IDENTITY策略，让数据库负责自增ID：

// Result 实体的ID配置
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

// ExchangeRate 实体的ID配置
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

2. 适配PostgreSQL等使用序列的数据库 #

为每个实体指定独立的序列生成器：

// Result 实体的ID配置
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "result_seq")
@SequenceGenerator(name = "result_seq", sequenceName = "result_sequence", allocationSize = 1)
private Long id;

// ExchangeRate 实体的ID配置
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "exchange_rate_seq")
@SequenceGenerator(name = "exchange_rate_seq", sequenceName = "exchange_rate_sequence", allocationSize = 1)
private Integer id;

3. 使用表生成策略（跨数据库通用） #

为每个实体指定独立的生成表条目：

// Result 实体的ID配置
@Id
@GeneratedValue(strategy = GenerationType.TABLE, generator = "result_table")
@TableGenerator(name = "result_table", table = "id_generator", pkColumnName = "gen_name", valueColumnName = "gen_value", pkColumnValue = "result_id", allocationSize = 1)
private Long id;

// ExchangeRate 实体的ID配置
@Id
@GeneratedValue(strategy = GenerationType.TABLE, generator = "exchange_rate_table")
@TableGenerator(name = "exchange_rate_table", table = "id_generator", pkColumnName = "gen_name", valueColumnName = "gen_value", pkColumnValue = "exchange_rate_id", allocationSize = 1)
private Integer id;

核心逻辑 #

为每个实体配置独立的ID生成机制，避免共享生成器/序列，就能保证子表ExchangeRate的ID从1开始独立自增。

内容的提问来源于stack exchange，提问作者Игорь