向H2表插入实体时触发DataIntegrityViolationException问题排查

环境信息 #

• Java 17.0.2
• Spring Boot 3.0.2（2.7.x版本同样存在该问题）

实体与Repository定义 #

Customer实体 #

@Entity
@Table(name = "customers")
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Customer {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Column(name = "name", unique = true, nullable = false, length = 255)
  private String name;

  @Column(name = "region")
  @Enumerated(EnumType.STRING)
  private Region region;

  public enum Region {
    US,
    EU
  }
}

CustomerRepository #

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer> {}

业务服务类CustomerService #

@Service
public class CustomerService {

  private final CustomerRepository customerRepository;

  public CustomerService(CustomerRepository customerRepository) {
    this.customerRepository = customerRepository;
  }

  @Transactional
  public Customer createCustomer(String name, Customer.Region region) {

    Customer customer = new Customer();
    customer.setName(name);
    customer.setRegion(region);

    return customerRepository.save(customer);
  }
}

测试用例 #

@SpringBootTest
@Transactional
class CustomerServiceTest {

  @Autowired
  private CustomerService customerService;

  @Test
  public void test_createCustomer() {
    Customer createdCustomer = customerService.createCustomer("The Crusher", Customer.Region.EU);

    Assertions.assertEquals(4, createdCustomer.getId(), "Id should match");
    Assertions.assertEquals("The Crusher", createdCustomer.getName(), "Name should match");
    Assertions.assertEquals(Customer.Region.EU, createdCustomer.getRegion(), "Region should match");
  }
}

数据初始化脚本（data.sql） #

INSERT INTO customers (id, name, region) VALUES (1, 'Dr. Carmack', 'EU'), (2, 'Pinky', 'EU'), (3, 'Revenant', 'EU');

配置信息 #

已在测试用application.yml中设置：

spring.jpa.defer-datasource-initialization: true

异常信息 #

运行测试时触发主键冲突异常：

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.CUSTOMERS(ID) ( /* key:1 */ 1, 'Dr. Carmack', 'EU')"; SQL statement:
insert into customers (id, name, region) values (default, ?, ?) [23505-214]]

Hibernate建表语句：

create table customers (
   id integer generated by default as identity,
    name varchar(255) not null,
    region varchar(255),
    primary key (id)
)

问题描述 #

通过data.sql插入了3条ID为1、2、3的记录，预期新增实体时ID自增为4，但实际Hibernate尝试分配ID=1，导致主键冲突，请问原因是什么？需要补充哪些配置？

原因分析 #

使用GenerationType.IDENTITY策略时，Hibernate完全依赖数据库的自增机制生成主键。但通过data.sql手动插入带ID的数据后，H2数据库的自增计数器不会自动同步更新，下一次自增仍会从初始值（通常是1）开始分配，最终与已存在的ID冲突。

解决方案 #

方案1：初始化时手动更新自增序列 #

修改data.sql，在插入数据后强制更新H2的自增序列起始值：

INSERT INTO customers (id, name, region) VALUES (1, 'Dr. Carmack', 'EU'), (2, 'Pinky', 'EU'), (3, 'Revenant', 'EU');
ALTER TABLE customers ALTER COLUMN id RESTART WITH 4;

方案2：切换主键生成策略 #

将IDENTITY改为SEQUENCE策略，让Hibernate管理自己的序列，不受手动插入数据的影响：

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "customer_seq")
@SequenceGenerator(name = "customer_seq", sequenceName = "customers_id_seq", allocationSize = 1)
private Integer id;

也可以使用GenerationType.AUTO，让Hibernate根据数据库自动选择合适的主键生成策略。

方案3：使用Hibernate初始化脚本替代data.sql #

改用import.sql作为Hibernate的初始化脚本（仅在spring.jpa.hibernate.ddl-auto设为create或create-drop时生效），Hibernate会在初始化实体时自动同步序列状态。

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