I have a spring boot project with an h2 database.

I have an entity class from which the schema is to be generated:

@NoArgsConstructor
@Entity
@Table(name = "NAMES")
public class Name {

    @Id
    @GeneratedValue
    public Long id;

    @Column(nullable = false)
    public String name;

    public Name(String name) {
        this.name = name;
    }

}

And I have a data.sql file:

insert into names (id, name) values (1, 'Alex');
insert into names (id, name) values (2, 'Bob');

my application.properties is:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.defer-datasource-initialization=true
spring.jpa.show-sql=true

The application starts up just fine, I can confirm with localhost:8080/h2-console that the data is loaded into the database. But I cannot save new data into the table with

//public interface NameRepository extends CrudRepository<Name,Long> {}

@RestController
@Service
public class MyController {

  @Autowired
  private final NameRepository nameRepository;

  @PostMapping("/triggerError")
  public ResponseEntity<Void> trigger() {

    Name newName = new Name("Chris");
    nameRepository.save(newName);
    return ResponseEntity.ok().build();
  }
}

The error message is:

could not execute statement; SQL [n/a]; constraint [\"PRIMARY KEY ON PUBLIC.NAMES(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'Alex')\"; SQL statement:
insert into names (name, id) values (?, ?) [23505-210]];

I assume that this means that spring wants to insert the new name at id=1, not realizing that ids 1 and 2 are already in use. I guess the right parameters to @GeneratedValue could fix it but I do not understand what they mean and which one to choose.

Trial and error:

@GeneratedValue(strategy = GenerationType.AUTO) is the default, see above.
@GeneratedValue(strategy = GenerationType.TABLE) same error
@GeneratedValue(strategy = GenerationType.SEQUENCE) same error
@GeneratedValue(strategy = GenerationType.IDENTITY) different error:

...
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column \"ID\"; SQL statement:\ninsert into names (id, name) values (null, ?) [23502-210]
...
could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

So apparently it is not the annotation, but something else.

I'm giving up, heres my MRE: https://github.com/timo-a/duckpond-spring-backend/tree/debug/saving

CodePudding user response：

That seems to be an h2 issue (among other vulnerabilities) on older versions . Update to 2.1.210

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>2.1.210</version>
  <scope>runtime</scope>
</dependency>

and use strategy = GenerationType.IDENTITY for your id

By the way, your repository uses Integer as your PK whereas your entity uses Long. And since the question is tagged as JPA, use :

public interface NameRepository extends JpaRepository<Name,Long> {}

And you insert data in a table named "scores" but I assume it's a typo.

CodePudding user response：

This is a bug on hibernate and specific to h2 database. https://hibernate.atlassian.net/browse/HHH-14985

you can use id BIGINT GENERATED BY DEFAULT AS IDENTITY DEFAULT ON NULL PRIMARY KEY column definition as a fix.

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(columnDefinition = "id BIGINT GENERATED BY DEFAULT AS IDENTITY DEFAULT ON NULL PRIMARY KEY")
private Long id;