Configure H2 DB with Spring Boot and Generate table from Entity classes

Prerequisite

Below tools should be installed on your machine

Java
Maven
STS/Eclipse

Add dependency as

H2 Database
Spring Web
Spring Data JPA

NOTE: Kindly select java version according to your project need ..I am using java 1.8 else you will get below exception when you run mvn clean install

Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.1:compile (default-compile) on project Maven: Compilation failure: Compilation failure

Dependencies will look like below

Kindly add required dependencies as shown in above screenshot and down load project by clicking on “GENERATE” button. ZIP will get downloaded.

NOTE: if you are downloading on office laptop then you might need to unblock zip before unzipping. for doing this right click on zip file and go to properties and unblock it. so you will able to unzip it completely.

After unzip you will see folder generate like below screenshot

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.3.3.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
	
		
	</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>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>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

Go to your editor and import project.

For eclipse or STS Users..

1 GO to file

2 click on import

3 type maven and select Existing maven project

4 It will show pop up to select folder of your project. Kindly select folder which you unzipped.

5 Select pom.xml and click on Finish button

6. Now your project will get created in workspace and all dependencies will get downloaded by maven.Make sure maven is installed on your machine.

In below screenshot and in highlighted part you can see its showing “boot” project in bracket because spring tool suit will automatically detect it as spring project. For this in eclipse you need to download plugins.

Your project will be visible in workspace now

7. Right click on project and go to run and do maven install as shown in below screen shot

On console you will see below logs

8. Once build is successful then add below code in DemoApplication.java and add below configurations to application.properties file as well

DemoApplication.java

package com.example.demo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class DemoApplication {
	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
		
	System.out.println("Welcome to spring boot");
	}
}

application.properties

spring.datasource.url=jdbc:h2:file:C:/data/demo
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
server.port=9000
spring.h2.console.path=/h2-console
spring.h2.console.settings.trace=false
spring.h2.console.settings.web-allow-others=false
spring.jpa.hibernate.ddl-auto=none

Above code will have datasource related to H2 DB and it will tell spring boot on which path database whould be accessible in browser. Your application will run on 8888 port

Add below files in resources folder

Add data.sql file as shown below

data.sql

DROP TABLE IF EXISTS employee;
 
CREATE TABLE employee (
  id INT AUTO_INCREMENT  PRIMARY KEY,
  first_name VARCHAR(250) NOT NULL,
  last_name VARCHAR(250) NOT NULL
  
);
 
INSERT INTO employee (first_name, last_name) VALUES
  ('James', 'mill')

schema.sql

DROP TABLE IF EXISTS NATION;
CREATE TABLE NATION (
    id   INTEGER      NOT NULL AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (id)
);

9. Now Run application as spring boot app shown in below screenshot

10. Congrats now your application is running successfully and your H2 Db is running

11. Now go to http://localhost:9000/h2-console

you will see below screen and please enter jdbc url and pasword used in applicaiton.properties file

Enter password and click on connect and you will able to see DB console

Now you can able to use this DB for configuration with JPA library or any other purpose

Now you have ran your application with spring and h2 DB and generated tables using data.sql and schema.sql.

Now we are going to see instead of using data.sql and shema.sql how we can generate table using Entity classes.

Create Entity class

We are going to create country class customer and some more entity classes

customer.java

package com.example.demo;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Customer {

  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Long id;
  private String firstName;
  private String lastName;

  protected Customer() {}

  public Customer(String firstName, String lastName) {
    this.firstName = firstName;
    this.lastName = lastName;
  }

  @Override
  public String toString() {
    return String.format(
        "Customer[id=%d, firstName='%s', lastName='%s']",
        id, firstName, lastName);
  }

  public Long getId() {
    return id;
  }

  public String getFirstName() {
    return firstName;
  }

  public String getLastName() {
    return lastName;
  }
}

Country.java

package com.example.demo;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Table(name="country")
@Entity
public class Country {
 
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Integer id;
    
    @Column(nullable = false)
    private String name;
 
    //...
}

Here in both classes as you see we have used annotation like @Table(name=”country”) ,@Entity,@Id and @Column ,They will help spring JPA to understand that its entity class and with which table its associated

@Id marks a field in a model class as the primary key:

@Table: Specifies the primary table for the annotated entity

@Column :Specifies the mapped column for a persistent property or field. If no Column annotation is specified, the default values apply.

@Entity:Specifies that the class is an entity. This annotation is applied to the entity class.

@GeneratedValue :Provides for the specification of generation strategies for the values of primary keys

Edit Application.properties

Add below entries in application.properties file


spring.datasource.url=jdbc:h2:file:C:/data/dbtest
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
server.port=8080


spring.jpa.generate-ddl=true
spring.jpa.show-sql = true

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.properties.hibernate.format_sql=true


spring.jpa.properties.hibernate.generate_statistics=true

spring.h2.console.path=/h2-console
spring.h2.console.settings.trace=false
spring.h2.console.settings.web-allow-others=false

Spring provide spring.jpa.hibernate.ddl-auto property for automatic schema genration so base on its value spring jpa will generate

tables for entity classes ,possible values for this are create ,update,create-drop,validate ,none

spring.jpa.generate-ddl (boolean) switches the feature on and off and is vendor independent.

Start spring boot application

After starting app you will see below log on console


  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.3.RELEASE)

2020-09-12 17:26:44.540  INFO 12852 --- [           main] com.example.demo.DemoApplication         : Starting DemoApplication on DESKTOP-V5245JB with PID 12852 (D:\SpringFramework\SpringBootwithHAndJPAAutoGenerateTables\target\classes started by admin in D:\SpringFramework\SpringBootwithHAndJPAAutoGenerateTables)
2020-09-12 17:26:44.543  INFO 12852 --- [           main] com.example.demo.DemoApplication         : No active profile set, falling back to default profiles: default
2020-09-12 17:26:45.294  INFO 12852 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!
2020-09-12 17:26:45.295  INFO 12852 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JDBC repositories in DEFAULT mode.
2020-09-12 17:26:45.357  INFO 12852 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 47ms. Found 0 JDBC repository interfaces.
2020-09-12 17:26:45.385  INFO 12852 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!
2020-09-12 17:26:45.385  INFO 12852 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFERRED mode.
2020-09-12 17:26:45.393  INFO 12852 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 2ms. Found 0 JPA repository interfaces.
2020-09-12 17:26:45.861  INFO 12852 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2020-09-12 17:26:45.868  INFO 12852 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2020-09-12 17:26:45.869  INFO 12852 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.37]
2020-09-12 17:26:45.979  INFO 12852 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2020-09-12 17:26:45.979  INFO 12852 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1387 ms
2020-09-12 17:26:46.033  INFO 12852 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-09-12 17:26:46.316  INFO 12852 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-09-12 17:26:46.322  INFO 12852 --- [           main] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:file:C:/data/dbtest'
2020-09-12 17:26:46.532  INFO 12852 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2020-09-12 17:26:46.670  INFO 12852 --- [         task-1] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2020-09-12 17:26:46.697  WARN 12852 --- [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2020-09-12 17:26:46.787  INFO 12852 --- [         task-1] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 5.4.20.Final
2020-09-12 17:26:47.266  INFO 12852 --- [         task-1] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2020-09-12 17:26:47.659  INFO 12852 --- [         task-1] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
2020-09-12 17:26:47.752  INFO 12852 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2020-09-12 17:26:47.754  INFO 12852 --- [           main] DeferredRepositoryInitializationListener : Triggering deferred initialization of Spring Data repositories…
2020-09-12 17:26:47.754  INFO 12852 --- [           main] DeferredRepositoryInitializationListener : Spring Data repositories initialized!
2020-09-12 17:26:47.778  INFO 12852 --- [           main] com.example.demo.DemoApplication         : Started DemoApplication in 3.538 seconds (JVM running for 5.291)
2020-09-12 17:26:48.377  INFO 12852 --- [         task-1] org.hibernate.tuple.PojoInstantiator     : HHH000182: No default (no-argument) constructor for class: com.example.demo.EmployeeInformation (class must be instantiated by Interceptor)
Hibernate: 
    
    drop table if exists country CASCADE 
Hibernate: 
    
    drop table if exists customer CASCADE 
Hibernate: 
    
    drop table if exists employee_information CASCADE 
Hibernate: 
    
    drop table if exists task CASCADE 
Hibernate: 
    
    drop sequence if exists hibernate_sequence
Hibernate: create sequence hibernate_sequence start with 1 increment by 1
Hibernate: 
    
    create table country (
       id integer not null,
        name varchar(255) not null,
        primary key (id)
    )
Hibernate: 
    
    create table customer (
       id bigint not null,
        first_name varchar(255),
        last_name varchar(255),
        primary key (id)
    )
Hibernate: 
    
    create table employee_information (
       id bigint not null,
        first_name varchar(255),
        last_name varchar(255),
        primary key (id)
    )
Hibernate: 
    
    create table task (
       id integer not null,
        description varchar(255),
        is_done boolean,
        target_date date,
        primary key (id)
    )
2020-09-12 17:26:48.708  INFO 12852 --- [         task-1] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2020-09-12 17:26:48.714  INFO 12852 --- [         task-1] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2020-09-12 17:39:04.343  INFO 12852 --- [nio-8080-exec-6] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2020-09-12 17:39:04.343  INFO 12852 --- [nio-8080-exec-6] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2020-09-12 17:39:04.350  INFO 12852 --- [nio-8080-exec-6] o.s.web.servlet.DispatcherServlet        : Completed initialization in 7 ms
2020-09-12 17:39:04.451  INFO 12852 --- [nio-8080-exec-6] i.StatisticalLoggingSessionEventListener : Session Metrics {
    0 nanoseconds spent acquiring 0 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    0 nanoseconds spent preparing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}
2020-09-12 17:39:04.582  INFO 12852 --- [nio-8080-exec-6] i.StatisticalLoggingSessionEventListener : Session Metrics {
    0 nanoseconds spent acquiring 0 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    0 nanoseconds spent preparing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

As you can see in log its has created tables when we have started app ad also shown sql on console as we have added property to show sql.

Now we will check on h2 db