Home > Net >  How to annotate id field to work with schema generated from class and initial data loaded from file?
How to annotate id field to work with schema generated from class and initial data loaded from file?

Time:02-14

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

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;

CodePudding user response:

No need to add a strategy. Your issue is due to the fact you're missing the transaction boundaries.

Annotate the method that saves your entity with @Transactional . Like this:

@SpringBootTest
public class NameRepositoryTest {
  @Autowired
  private NameRepository nameRepository;

  @Test
  @Transactional
  void save_validName_saved() {
    Name name = new Name();
    name.setName("name 5");

    Long generatedId = assertDoesNotThrow(() -> nameRepository.save(name).getId());

    assertNotNull(nameRepository.findById(generatedId));
  }
}


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.

  • Related