[FIXED] How do I get H2 and SpringBoot to play nice together using the in memory database?

Issue

I'm working my way through a tutorial video on unit testing with JUnit and Mockito and have run across some issues where something isn't playing nice and I'm trying to figure out the problem. The tutorial is creating an in memory database using H2 which is where I am having issues (the database has no data). I have created a new project with the sole purpose of creating the same database which works to a point and then fails with no data. After a couple days of research I'm hoping someone will see something glaringly obvious that I'm missing. I'm using IntelliJ Idea 21.1.2 as my IDE, Linux Mint OS. H2 is new to me.

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.5.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>TestH2</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>TestH2</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <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-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

</project>

application.properties

spring.datasource.url=jdbc:h2:mem:item;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1
spring.h2.console.enabled=true

schema.sql

DROP TABLE IF EXISTS ITEM;

CREATE TABLE ITEM (
    id INT,
    name VARCHAR(255),
    price INT,
    quantity INT
);

data.sql

INSERT INTO ITEM(id, name, price, quantity)
VALUES
       (10001,'Item1',10,20),
       (10002,'Item2',5,10),
       (10003,'Item3',15,2);

With the above and my application file the project builds and when I open the h2-console I get the expected result.

However. . .

If I try to add an Item.java file like this (note if I comment out the @Entity line it works as shown above)

package com.example.testh2.model;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Transient;

@Entity
public class Item {

    @Id
    private int id;
    private String name;
    private int price;
    private int quantity;

    @Transient
    private int value;

    public Item() {

    }

    public Item(int id, String name, int price, int quantity) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.quantity = quantity;

    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public int getPrice() {
        return price;
    }
    public void setPrice(int price) {
        this.price = price;
    }

    public int getQuantity() {
        return quantity;
    }
    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }

    public int getValue() { return value; }
    public void setValue(int value) { this.value = value; }


    public String toString() {
        return String.format("Item[%d, %s, %d, %d]", id, name, price, quantity);
    }


}

Then there is no data

I'm guessing that I'm missing something simple, I just haven't Google'd the right question yet. I have tried the default as well as the current datasource.url, I've tried embedded H2 with a file (I see the items.mv.db file appear where it should but the console didn't find it nor did the IntelliJ database tools). Any help would be greatly appreciated.

Solution

in the application.properties file, add this line to turn off the conflicting automatic schema creation:

spring.jpa.hibernate.ddl-auto=none

you can read more about it here: https://docs.spring.io/spring-boot/docs/1.1.0.M1/reference/html/howto-database-initialization.html

Answered By - user12711752