[FIXED] H2 Database doesn't create table from schema.sql

Issue

I'm kind of new to Spring and started following EazyBytes' Udemy tutorial. As he started talking about how to implement an H2 Database inside a Spring Boot Project, I tried putting it into practice.

At the beginning, after installing the Maven dependency of the H2 Database, I couldn't access it via the console, because it constantly told me that the database was not created. Reading some Stack Overflow answers, found out that the DB isn't automatically generated anymore, and I that I need to downgrade to an earlier version. That's what I did. So, my pom.xml looks like this now:

...

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.193</version>
    <scope>runtime</scope>
</dependency>

...

I've also created a schema.sql at the level of application.properties:

CREATE TABLE IF NOT EXISTS `contact_msg` (
  `contact_id` int AUTO_INCREMENT  PRIMARY KEY,
  `name` varchar(100) NOT NULL,
  `mobile_num` varchar(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `subject` varchar(100) NOT NULL,
  `message` varchar(500) NOT NULL,
  `status` varchar(10) NOT NULL,
  `created_at` TIMESTAMP NOT NULL,
  `created_by` varchar(50) NOT NULL,
  `updated_at` TIMESTAMP DEFAULT NULL,
  `updated_by` varchar(50) DEFAULT NULL
);

My application.properties looks like that:

# Some Basic Spring Boot Configurations
...

# H2 Database Config
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

When trying to access .../h2-console, everything works. Inside the "JDBC URL" section, I've inputted jdbc:h2:mem:testdb, but when I click on "Connect", this is what I get:

As you can see, I cannot see my contact_msg table, but in the tutorial I watch, the lecturer can see and expand it. I thought this may be a problem with Spring Security, but I literally tried copy-pasting his own configurations, and no changes happened.

What might be the problem here?

Solution

I think you will have to modify the ddl-auto property as following :

spring.jpa.hibernate.ddl-auto=create-drop

You can do this when starting the application for the first time so it creates your schema, then for the next time you can put it back to 'update'

Answered By - Anghel Ionut
Answer Checked By - Gilberto Lyons (JavaFixing Admin)