Load an embedded H2 database with initial data in Spring Boot

Load an embedded H2 database with initial data in Spring Boot

1. Application yml / properties

  
  
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:h2:mem:e2e;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS local_crefo_db
    username: sa
    password:
    hikari:
      maximumPoolSize: 20
      minimumIdle: 1
    initialization-mode: embedded
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    database: h2
    show-sql: false
    properties:
      hibernate.hbm2ddl.auto: create-drop
      hibernate.show_sql: false
      hibernate.default_schema: local_crefo_db
  h2:
    console:
      enabled: true
      path: /h2-console

logging:
  level:
    ROOT: INFO
    org.springframework.jdbc: DEBUG
    com.zaxxer.hikari: DEBUG


2. Pom.xml



<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- postgresql jdbc -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>


3. New schema.sql under src/main/resources

Example:

create schema if not exists local_db; set schema local_db; drop table if exists SOMETABLE; create table SOMETABLE( COL_1 varchar(255) not null, COL_2 timestamp not null, COL_3 text not null, COL_4 varchar(255) not null, primary key (COL_1 ) );

To add data create new data.sql under the same directory, and apply your insert statements.