Issue using Spring Boot with Inserting into h2database with a JUnit4 test

Question

Was following an online tutorial on how to setup a Spring Boot project and write unit tests against an h2 database...

Am new to Spring Boot when it comes to writing JUnit tests and also am new to using the in-memory h2 database.

Wrote a test and now it doesn't work!

pom.xml

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

<properties>
    <java.version>1.8</java.version>
</properties>

<dependencies>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>

</dependencies>

code structure

demo
 ├── mvnw
 ├── mvnw.cmd
 ├── pom.xml
 └── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── example
    │   │           └── demo
    │   │               ├── Booking.java
    │   │               ├── DemoApplication.java
    │   │               └── ReservationRepository.java
    │   └── resources
    │       ├── application.properties
    │       ├── static
    │       └── templates
    └── test
        ├── java
        │   └── com
        │       └── example
        │           └── demo
        │               └── DemoApplicationTests.java
        └── resources
            └── data.sql

Booking.java

@Entity
public class Booking {

    @Id
    @GeneratedValue
    private Long id;

    // booking_name
    private String bookingName;

    // group_size
    private int groupSize;

    Booking(){ }

    public Booking(String bookingName, int groupSize) {
        this.bookingName = bookingName;
        this.groupSize = groupSize;
    }

    // Getters & setters omitted for brevity
}

ReservationRepository

import org.springframework.data.jpa.repository.JpaRepository;

public interface ReservationRepository extends JpaRepository<Booking, Long> {
}

DemoApplicationTests

@RunWith(SpringRunner.class)
@SpringBootTest
public class DemoApplicationTests {

    @Autowired
    ReservationRepository reservationRepository;

    @Test
    public void contextLoads() {
        Assert.assertNotNull("The reservation repository should be non-null", this.reservationRepository);
    }

    @Test
    public void testLoadingResultsInDatabase() {
        List<Booking> bookings = this.reservationRepository.findAll();
        Assert.assertNotNull("There must be a response", bookings);
        Assert.assertTrue("There should be at least one booking", bookings.size() > 0);
    }
}

data.sql

insert into booking ( booking_name, group_size) values ('LiveLessons', 1000);
insert into booking ( booking_name, group_size) values ('John Doe', 1);

When I run this JUnit test:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/home/work/demo/target/test-classes/data.sql]: insert into booking ( booking_name, group_size) values ('LiveLessons', 1000); nested exception is org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
insert into booking ( booking_name, group_size) values ('LiveLessons', 1000) [23502-199]

Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/home/work/demo/target/test-classes/data.sql]: insert into booking ( booking_name, group_size) values ('LiveLessons', 1000); nested exception is org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
insert into booking ( booking_name, group_size) values ('LiveLessons', 1000) [23502-199]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:509) ~[spring-jdbc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:238) ~[spring-jdbc-5.1.6.RELEASE.jar:5.1.6.RELEASE]
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
insert into booking ( booking_name, group_size) values ('LiveLessons', 1000) [23502-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:457) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.199.jar:1.4.199]

What am I possibly doing wrong?

what part of *nested exception is org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: **NULL not allowed for column "ID"**; SQL statement:* is unclear to you? — , Apr 23 '19 at 16:18
https://stackoverflow.com/questions/39094649/h2-database-null-not-allowed-for-column-id-when-inserting-record-using-jdbcte — Planck Constant, Apr 23 '19 at 16:21
@Uata - yes, thanks the URL for similar problem! @GeneratedValue(strategy=GenerationType.IDENTITY) solved the problem! — PacificNW_Lover, Apr 23 '19 at 16:29

score 0 · Answer 1 · answered Apr 23 '19 at 16:19

0

You are missing ID in your sql.

answered Apr 23 '19 at 16:19

admly

319
2
10

admly - doesn't it auto-increment though? How would that look like in the SQL? When I tried doing this insert into booking (1, booking_name, group_size) values (1, 'LiveLessons', 1000); it still broke? – PacificNW_Lover Apr 23 '19 at 16:25
it would generate an id if you use JPA, but you should state it explicitly in init sql script. – admly Apr 23 '19 at 16:31
Checked by myself, insert into booking ( id, booking_name, group_size) values (1, 'LiveLessons', 1000); insert into booking ( id, booking_name, group_size) values (2, 'John Doe', 1); works. Of course you can fix it by declaring a generation strategy. – admly Apr 23 '19 at 17:03

Issue using Spring Boot with Inserting into h2database with a JUnit4 test

1 Answers1