H2 In-memory database. JdbcSQLSyntaxErrorException: Table not found (this database is empty)

Question

I'm trying to run a simple Spring application that adds an item to a database. I want to use the H2 in-memory database without defining a schema.sql.

However, when I add a data.sql file inside the resources folder and start the application, I get the error: Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "ITEMS" not found (this database is empty); INSERT INTO ITEMS(id, value) VALUES(1, "EXAMPLE") [42104-214]

Even when I don't have a data.sql file and just add an item to the database using the api I receive the same error. I don't understand what's wrong. I've searched everywhere trying to find a fix, but nothing worked.

Can someone please help me?

The entity:

import javax.persistence.*;

@Entity
@Table(name = "ITEMS")
public class Item {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    private String value;
}

application.properties:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=sa
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.defer-datasource-initialization=true

data.sql:

INSERT INTO ITEMS(id, value) VALUES(1, "EXAMPLE");

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.5</version>
        <relativePath/>
    </parent>
    <groupId>com.example</groupId>
    <artifactId>simple-project</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>simple-project</name>
    <description>simple-project</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Answer 1

I've reproduced your problem:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "ITEMS" not found (this database is empty); INSERT INTO ITEMS(id, value) VALUES(1, "EXAMPLE") [42104-214]

Option #1:

And to resolve it, you should create two files in src/main/resources folder as data.sql file:

insert into ITEMS(id, designation)
values (1, 'EXAMPLE');

in this file you need to add some insert statements.

and accordingly schema.sql file as:

create table items
(
    id                    int not null auto_increment,
    designation           varchar(50) not null,
    primary key (id)
);

(or schema-h2.sql) as well to create your schema there.

After creating it in your project and configuring your Data Sources and Drivers section in database settings, you can run manually schema.sql with right-click Run '...sql':

And then data.sql by the same principle to run populating this H2 database.

Option #2:

You can run it automatically by writting as:

Item.java

@Entity(name = "ITEM_ENTITY")
@Table(name = "items")
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String designation;
}

MainApplication.java

@SpringBootApplication
public class MainApplication {

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }
}

application.properties

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.session.jdbc.initialize-schema=always
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.defer-datasource-initialization=true
spring.sql.init.continue-on-error=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.sql.init.mode=always

schema.sql

create table items
(
    id                    int not null auto_increment,
    designation           varchar(50) not null,
    primary key (id)
);

data.sql

insert into ITEMS(id, designation)
values (1, 'EXAMPLE');

Answer 2

I would add this line in application.properties as well.

spring.jpa.hibernate.ddl-auto=update

p.s.

Not sure that you need this line

spring.jpa.defer-datasource-initialization=true

because this is used if we want to have both Hibernate automatic schema generation in conjugation with script-based schema creation and data population, but you said you are not using schema.sql. I would advise you to remove this line.

EDIT:

My sample config:

#general settings
server:
  port: 8090
  servlet:
    context-path: /wallet
#h2
spring:
  h2:
    console:
      enabled: true
      path: /h2
  datasource:
    url: jdbc:h2:file:./wallet-db
    username: sa
    password:
    driver-class-name: org.h2.Driver
  jpa:
    hibernate:
      ddl-auto: update
  application:
    name: wallet-service