Issue in H2 with GenerationType.IDENTITY & schema.sql -- SpringBoot-CodePudding

I am trying to create and preload embedded H2 Db with some data using schema.sql. But once I try to add new user using POST request, the following error occurs.

2022-07-04 23:35:53.153 ERROR 8180 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.USERS(USER_ID) ( /* key:1 */ 1, 'mrxxx', '[email protected]', '123456')"; SQL statement: insert into users (user_id, email, user_name, password) values (default, ?, ?, ?) [23505-214]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.USERS(USER_ID) ( /* key:1 */ 1, 'mrxxx', '[email protected]', '123456')"; SQL statement: insert into users (user_id, email, user_name, password) values (default, ?, ?, ?) [23505-214]

I guess this is happening because the @GeneratedValue(strategy = GenerationType.IDENTITY) is trying to create user_id = 1 but it already exists in Db as we have preloaded it. I dont know how to get rid of it

Request

{
    "name" : "Arunkumar",
    "email" : "[email protected]",
    "password" : "arun1234" 
}

Response

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

Please find the code snippets below

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', '[email protected]', '123456');

Users.java

@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;
    .......
}

DbCotroller.java

@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);
    }
}

CodePudding user response：

You can omit the id in the insert query:

INSERT INTO `users`(`USER_NAME`,`EMAIL`,`PASSWORD`) VALUES ('mrxxx', '[email protected]', '123456');