how to initialize H2 database in spring boot

Question

Reading spring boot 5 in action, chapter 3 -> working with H2 database, the structure of the project:

.
├── HELP.md
├── mvnw
├── mvnw.cmd
├── pom.xml
├── src
│   ├── main
│   │   ├── java
│   │   │   └── com
│   │   │       └── example
│   │   │           └── tacocloud
│   │   │               ├── controllers
│   │   │               │   ├── DesignTacoController.java
│   │   │               │   └── OrderController.java
│   │   │               ├── models
│   │   │               │   ├── Ingredient.java
│   │   │               │   ├── Order.java
│   │   │               │   └── Taco.java
│   │   │               ├── repositories
│   │   │               │   ├── interfaces
│   │   │               │   │   └── IngredientRepository.java
│   │   │               │   └── JdbcIngredientRepository.java
│   │   │               ├── TacoCloudApplication.java
│   │   │               └── WebConfig.java
│   │   └── resources
│   │       ├── application.properties
│   │       ├── data.sql
│   │       ├── schema.sql
│   │       ├── static
│   │       │   ├── images
│   │       │   │   └── TacoCloud.png
│   │       │   └── style.css
│   │       └── templates
│   │           ├── design.html
│   │           ├── home.html
│   │           └── orderForm.html
│   └── test
│       └── java
│           └── com
│               └── example
│                   └── tacocloud
│                       └── TacoCloudApplicationTests.java
└── taco-cloud.iml

When I start the project with maven plugin mvn spring-boot:run:

2021-07-31 12:41:30.065  INFO 4910 --- [           main] c.e.tacocloud.TacoCloudApplication       : Starting TacoCloudApplication using Java 11.0.11 on Shepherd with PID 4910 (/home/shepherd/Desktop/spring/taco-cloud/target/classes started by shepherd in /home/shepherd/Desktop/spring/taco-cloud)
2021-07-31 12:41:30.067  INFO 4910 --- [           main] c.e.tacocloud.TacoCloudApplication       : No active profile set, falling back to default profiles: default
2021-07-31 12:41:31.195  INFO 4910 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2021-07-31 12:41:31.205  INFO 4910 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2021-07-31 12:41:31.206  INFO 4910 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.50]
2021-07-31 12:41:31.281  INFO 4910 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2021-07-31 12:41:31.281  INFO 4910 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1163 ms
2021-07-31 12:41:31.371  INFO 4910 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-07-31 12:41:31.474  INFO 4910 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2021-07-31 12:41:31.999  INFO 4910 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2021-07-31 12:41:32.013  INFO 4910 --- [           main] c.e.tacocloud.TacoCloudApplication       : Started TacoCloudApplication in 2.413 seconds (JVM running for 2.812)

I can see there is no sql execution and thus the initial tables from main/resources/schema.sql and main/resources/data.sql are not created. (even though the sql files are located in right directory according to docs).

So when I try to GET this page from /design (main/java/com/example/tacocloud/controllers/DesignController.java):

@GetMapping
    public String showDesignForm(Model model){
        List<Ingredient> ingredients = new ArrayList<>();
        ingredientRepo.findAll().forEach(ingredients::add);

        Type[] types = Ingredient.Type.values();
        for(Type type: types){
            model.addAttribute(type.toString().toLowerCase(),
                    filterByType(ingredients, type));
        }

        return "design";
    }

which calls the repostiory function findAll()(main/java/com/example/tacocloud/repositories/JdbcIngredientRepository.java):

 @Override
    public Iterable<Ingredient> findAll() {
        return jdbc.query("SELECT id, name, type FROM Ingredient",
                this::mapRowToIngredient);
    }

I got thymeleaf error as it is unable to render the template, as the data needed are not available because the schema.sql with the table is not executed. How to enable the spring to execute the schema.sql in order to have those data available?

PS: the schema.sql:

create table if not exists Ingredient (
    id varchar(4) not null,
    name varchar(25) not null,
    type varchar(10) not null
);

create table if not exists Taco (
    id identity,
    name varchar(50) not null,
    createdAt timestamp not null
);

create table if not exists Taco_Ingredients(
    taco bigint not null,
    ingredient varchar(4) not null
);

alter table Taco_Ingredients
    add foreign key (taco) references Taco(id);
alter table Taco_Ingredients
    add foreign key (ingredient) references Ingredient(id);

create table if not exists Taco_Order (
    id identity,
    deliveryName varchar(50) not null,
    deliveryStreet varchar(50) not null,
    deliveryCity varchar(50) not null,
    deliveryState varchar(2) not null,
    deliveryZip varchar(10) not null,
    ccNumber varchar(16) not null,
    ccExpiration varchar(5) not null,
    ccCVV varchar(3) not null,
    placedAt timestamp not null
);

create table if not exists Taco_Order_Tacos(
    tacoOrder bigint not null,
    taco bigint not null
);

alter table Taco_Order_Tacos
    add foreign key (tacoOrder) references Taco_Order(id);
alter table Taco_Order_Tacos
    add foreign key (taco) references Taco(id);

application.properties:

spring.thymeleaf.cache=false
spring.sql.init.mode=always`

PSS: git repo: https://github.com/tacocloud/taco-cloud

Please include the contents of your application.properties file. — Blair Nangle
– Blair Nangle, Commented Jul 31, 2021 at 10:59
Please make a full working project on github or alike so someone can take a look at it... — khmarbaise
– khmarbaise, Commented Jul 31, 2021 at 11:18

Vitor Vidal · Accepted Answer · 2021-08-01 11:37:04Z

2

In Spring Boot 2.5, the initialization of data.sql and squema.sql is different when using Hibernate. Here is the upgrading guide: https://github.com/spring-projects/spring-boot/wiki/Spring-Boot-2.5-Release-Notes#upgrading-from-spring-boot-24.

The TLDR is that data.sql now runs before Hibernate is initialized. If you want to use data.sql to populate a schema created by Hibernate, set spring.jpa.defer-datasource-initialization to true in your properties file. Be aware that this is not a good practice.

answered Aug 1, 2021 at 11:37

Vitor Vidal

1161 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

milanHrabos Over a year ago

even setting that option in application.properties to true did not help (e.i the sql data are still not executed and the H2 is not populated)

Vitor Vidal Over a year ago

You have a typo spring.sql.init.mode=always`

Panagiotis Bougioukos · Accepted Answer · 2021-08-01 16:05:22Z

1

You need the following property also

spring.jpa.hibernate.ddl-auto=none

You must carefully read the documentation and you will understand.

Spring Boot chooses a default value for you based on whether it > thinks your database is embedded (default create-drop) or not (default none)

This means that for the embedded h2 database the default property that is applied is spring.jpa.hibernate.ddl-auto=create-drop

So even if you provide your scripts they are overriden by hibernate create-drop schema initialization. That is why you need to manualy set spring.jpa.hibernate.ddl-auto=none

answered Aug 1, 2021 at 16:05

Panagiotis Bougioukos

19.6k4 gold badges41 silver badges63 bronze badges

Comments

milanHrabos · Accepted Answer · 2021-08-01 18:26:43Z

0

As it found out, the problem was neither database nor any properties set. The problem was actually in the view. The project is using thymeleaf templating and it is using special attribute of form tag -> th:object. I have not specified which object should accept data submitted from this form and thus the template engine dump exception. The reason I though the exception was caused by H2 is I haven't seen any log statements of execution of initial schema (schema.sql) and its subsequent population (data.sql). So the real reason was in the view template.

answered Aug 1, 2021 at 18:26

milanHrabos

2,0174 gold badges25 silver badges70 bronze badges

Comments

Simon Martinelli · Accepted Answer · 2021-07-31 10:56:14Z

-1

I assume that you are using the latest Spring Boot version.

You have to set the following in your application.properties to activate this feature:

spring.sql.init.mode=always

answered Jul 31, 2021 at 10:56

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

Collectives™ on Stack Overflow

how to initialize H2 database in spring boot

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related