SpringBoot整合H2时GenerationType.IDENTITY结合schema.sql主键冲突问题

问题场景 #

• 通过schema.sql创建嵌入式H2数据库表结构并预加载初始化数据
• 调用POST接口提交新增用户请求时触发主键约束冲突，接口返回500错误
• 实体类主键已配置@GeneratedValue(strategy = GenerationType.IDENTITY)自增策略，初步判断自增起始值与预加载数据主键冲突

错误核心日志 #

org.springframework.dao.DataIntegrityViolationException: 请求处理失败，无法执行SQL语句，触发PUBLIC.USERS表USER_ID字段主键约束冲突
根因: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: 唯一索引或主键冲突
执行语句: insert into users (user_id, email, user_name, password) values (default, ?, ?, ?) [23505-214]

相关代码与配置 #

接口请求与响应 #

请求体

{
    "name" : "Arunkumar",
    "email" : "arunkavi@gmail.com",
    "password" : "arun1234" 
}

响应体

{
    "timestamp": "2022-07-04T18:05:53.184+00:00",
    "status": 500,
    "error": "Internal Server Error",
    "path": "/post"
}

application.properties配置 #

spring.jpa.hibernate.ddl-auto=none
spring.datasource.initialization-mode=always
spring.h2.console.enabled=true
spring.h2.console.path=/h2
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name= org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

schema.sql初始化脚本 #

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);
INSERT INTO `users` VALUES (1,'mrxxx', 'mrxxx@gmail.com', '123456');

Users实体类 #

@Entity
public class Users {    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="USER_ID")
    private int id;
    @Column(name="USER_NAME")
    private String name;
    @Column(name="PASSWORD")
    private String password;
    @Column(name="EMAIL")
    private String email;
    // 省略getter、setter方法
}

DbController控制器 #

@RestController
public class DbController {
    @Autowired
    private DbRepo userRepo;
    
    @PostMapping("/post")
    public ResponseEntity<?> saveUser(@RequestBody Users user){
        userRepo.save(user);
        return new ResponseEntity<>(user, HttpStatus.CREATED);
    }
}

根因说明 #

推测完全正确。手动插入主键为1的预加载数据后，H2的AUTO_INCREMENT自增计数器不会自动同步更新，默认仍从1开始生成下一个主键值，插入时与已存在的id=1数据触发冲突。
H2自增计数器仅在插入数据时不手动指定主键值的场景下自动递增，初始化脚本中硬编码插入user_id=1的记录后，自增序列当前值仍停留在1，后续JPA通过default生成主键时会拿到重复值1，触发唯一约束报错。

修复方案 #

任选一种方案即可解决问题：

• 方案1：插入预加载数据后手动重置自增起始值
  在schema.sql的插入语句末尾新增自增重置命令，将下一个自增值设置为比预加载数据最大主键大1即可：

  -- 原有预加载插入语句
  INSERT INTO `users` VALUES (1,'mrxxx', 'mrxxx@gmail.com', '123456');
  -- 新增：重置自增序列，下一个生成的主键从2开始
  ALTER TABLE users ALTER COLUMN user_id RESTART WITH 2;
  

  如果预加载多条数据，比如预加载数据的最大id为5，就将RESTART WITH后的值改为6。

• 方案2：插入预加载数据时不手动指定主键，由数据库自增生成
  修改初始化脚本的插入语句，不指定user_id字段，让AUTO_INCREMENT自动生成主键，计数器会自动同步更新：

  -- 不指定user_id，由数据库自增赋值
  INSERT INTO `users`(user_name, email, password) VALUES ('mrxxx', 'mrxxx@gmail.com', '123456');
  

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