Spring Boot in memory database H2 doesn't load data from file on initialization

Question

I have an issue with loading data into an in-memory database on application initialization. I have created schema.sql and data.sql files containing table structure and initial data.

schema.sql :

CREATE TABLE users (
  id          INT PRIMARY KEY,
  username    VARCHAR(64) NOT NULL,
  password    VARCHAR(64) 
);

and data.sql :

INSERT INTO users (id, username, password) VALUES
  (1, 'usr1', 'bigSecret'),
  (2, 'usr2', 'topSecret');

I am using JpaRepository for working with data layer:

public interface UserRepository extends JpaRepository<User, Long> {
}

And I also configure application.properties

spring.datasource.initialize=true
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=- 1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

But when I call

List<User> users = userRepository.findAll();

User entity

@Entity
@Table(name = "users")
public class User {

  @Id
  @GeneratedValue
  private Long id;
  private String username;
  private String password;

  public User() {  }

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public String getUsername() {
    return username;
  }

  public void setUsername(String username) {
    this.username = username;
  }

  public String getPassword() {
    return password;
  }

  public void setPassword(String password) {
    this.password = password;
  }
}

I get an empty list, but I should get two pre-populated users from my in-memory H2 database. What's wrong with in memory database? Thanks.

Potiche

where did you place those scripts? – Periscope

I'm wondering if your URL is OK with the extra space DB_CLOSE_DELAY=- 1; => DB_CLOSE_DELAY=-1; – Amylase

can you show your User entity – Clemmie

@Maciej Kowalski I have placed scripts into /src/main/resources and it seems that they are read by application. – Potiche

@Amylase I have removed space and there is the same result - no data extracted from file – Potiche

Answer 1

You can always try to run those scripts per specification of h2, where you should add an INIT script in your connection url (being one of the options):

jdbc:h2:mem:test;INIT=RUNSCRIPT FROM '~/schema.sql'\;RUNSCRIPT FROM '~/data.sql'"

This functionality is enabled via the INIT property. Note that multiple commands may be passed to INIT, but the semicolon delimiter must be escaped, as in the example below.

Update

Be aware that having these options in your application.properties:

spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=true
spring.datasource.initialize=true

may cause some clashing during startup. So you should always aim for one or the other, but never both at the same time. For simple cases just these alone are sufficient to auto build tables and reload after shutdown & startup

Periscope

I have tried to add sql scripts (schema.sql and data.sql) to INIT in spring.datasource.url property at application.conf, but I got exception on application launching - telling that table is already created (org.h2.jdbc.JdbcSQLException: Table "USERS" already exists; SQL statement), and I think it is because of spring.datasource.initialize=true. So... it seems that INIT and spring.datasource.initialize=true won't work together. @Maciej Kowalski – Potiche

The correct settings for application.conf to make it working with in memory database initialized with data are: spring.datasource.initialize=true spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE spring.datasource.driverClassName=org.h2.Driver spring.datasource.username=sa spring.datasource.password= and the most important, don't forget to disable Hibernate database initialization property spring.jpa.hibernate.ddl-auto=none because default value create-drop will removes all data from tables inserted before. And you will have empty tables. – Potiche

yes, that spring.jpa.hibernate.ddl-auto probably have gotten in the way. Glad it finally works for you. – Periscope

spring.jpa.hibernate.ddl-auto=none is causing for me h2 not to create any table at all.... – Rayraya

thanks @tyomka. your suggestion to set spring.jpa.hibernate.ddl-auto=none was key to make it working. It saved my time. – Asclepiadaceous

I spent hours trying to figure out why the insertion didn't work until I found that spring.jpa.hibernate.ddl-auto default's value is create-drop. All the configurations may be right but if this property is not specifically declared, then the data will be deleted, as @Potiche said – Gentle

Answer 2

I solved a similar problem when I added the following lines to the application.properties:

spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.session.jdbc.initialize-schema=always
spring.sql.init.data-locations=classpath:schema.sql,classpath:data.sql

Bridewell

Answer 3

a similar problem could occur also when Maven filters out the actual schema.sql and data.sql scripts from the final target folder where the application runs - please check that these files are indeed where they are supposed to be

Explode