SpringBootTest - H2 - NULL not allowed for column "ID"

Question

I'm trying to insert some data on H2 in memory database to make some tests and simulate my real Oracle11g but I'm facing an error NULL not allowed for column "ID". I've tried many solutions like putting Id and default on insert query, changing to Mode=LEGACY on jdb-url, but it didn't work.

data.sql

INSERT INTO CSM_SECURITY.csm_person(EMAIL, PERSON_NAME, SYS_ADMIN, DEFAULT_LANGUAGE, WSO2_ID, SYS_ADM_COUNTRY_ID, SYS_ADMIN_COUNTRY) 
VALUES ('[email protected]','admin','Y','es-ar',NULL,NULL,NULL);

Person Entity

@Entity
@Table(name = "csm_person", uniqueConstraints = @UniqueConstraint(columnNames = "email"))
public class Person implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PERSON_SEQ")
    @SequenceGenerator(sequenceName = "CSM_PERSON_ID_SEQ", allocationSize = 1, name = "PERSON_SEQ")
    private Long id;
    @Column(name = "email")
    private String email;
    @Column(name = "person_name")
    private String personName;
    ...

src/test/resources/application.properties

# SQL properties
spring.jpa.defer-datasource-initialization=true
spring.jpa.database-platform = org.hibernate.dialect.H2Dialect
spring.jpa.show-sql = true
spring.jpa.properties.hibernate.format_sql = true
spring.jpa.properties.hibernate.default_schema = CSM_SECURITY
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.H2Dialect
spring.jpa.properties.hibernate.event.merge.entity_copy_observer = allow
spring.jpa.open-in-view = false
spring.datasource.driver-class-name = org.h2.Driver
spring.sql.init.platform = h2
spring.datasource.name = CSM_SECURITY
spring.datasource.url = jdbc:h2:mem:CSM_SECURITY;MODE=Oracle;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS CSM_SECURITY
spring.sql.init.mode = embedded
spring.datasource.username = sa

pom.xml

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.7.0</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

Error

Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: 
Failed to execute SQL script statement #1 of 
URL [file:/home/ainacio/Development/csm-security/target/test-classes/data.sql]: 
INSERT INTO CSM_SECURITY.csm_person(EMAIL, PERSON_NAME, SYS_ADMIN, DEFAULT_LANGUAGE, WSO2_ID, SYS_ADM_COUNTRY_ID, SYS_ADMIN_COUNTRY) 
VALUES ('[email protected]','admin','Y','es-ar',NULL,NULL,NULL); 
nested exception is org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:

Answer 1

For such insertions you need to use identity columns (GenerationType.IDENTITY), but they aren't available in Oracle 11g, only 12c and newer versions support them.

You need to provide a value for ID column but this value must not conflict with values produced by a sequence, for example, you can change start value of a sequence generator to a 100, and use smaller values in your initialization scripts.

Alternatively you can fetch value of a sequence by itself, but Oracle 11g doesn't support sequence value expressions inside insert values or inside subqueries. So if you need to execute the same SQL in H2 and that historic version of Oracle, you need to execute something like that:

INSERT INTO CSM_SECURITY.CSM_PERSON
(ID, EMAIL, PERSON_NAME, SYS_ADMIN, DEFAULT_LANGUAGE,
WSO2_ID, SYS_ADM_COUNTRY_ID, SYS_ADMIN_COUNTRY) 
SELECT CSM_SECURITY.CSM_PERSON_ID_SEQ.NEXTVAL,
'[email protected]', 'admin', 'Y', 'es-ar', NULL, NULL, NULL FROM DUAL;

Make sure you're using Oracle compatibility mode of H2, because new versions of H2 don't accept NEXTVAL in Regular mode.

If you use this initilalization script only with H2, that trick with insert from query isn't required:

INSERT INTO CSM_SECURITY.CSM_PERSON
(ID, EMAIL, PERSON_NAME, SYS_ADMIN, DEFAULT_LANGUAGE,
WSO2_ID, SYS_ADM_COUNTRY_ID, SYS_ADMIN_COUNTRY) 
VALUES (NEXT VALUE FOR CSM_SECURITY.CSM_PERSON_ID_SEQ,
'[email protected]', 'admin', 'Y', 'es-ar', NULL, NULL, NULL);