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.