Do or don't I need to create a table that I want to prepopulate with H2?

Question

I'm confused by the errors I get when trying to create an in-memory H2 DB for my Spring Boot application. The relevant configuration is

db.url=jdbc:h2:mem:test;MODE=MySQL;DB_CLOSE_DELAY=-1;INIT=runscript from 'classpath:create.sql' hibernate.hbm2ddl.auto=create

And create.sql:

CREATE TABLE `cities` ( `name` varchar(45) NOT NULL, PRIMARY KEY (`name`) ) ; INSERT INTO `cities` VALUES ('JAEN'),('ALBACETE');

But I get the error Caused by: org.h2.jdbc.JdbcSQLException: Table "CITIES" already exists;

Weird is, if I remove the CREATE TABLE statement, I get:

Caused by: org.h2.jdbc.JdbcSQLException: Table "CITIES" not found;

The only thing that works is using DROP TABLE IF EXISTS, but well, I don't think I should need to.

What's going on? What's the proper way of pre-populating static data into an H2 memory DB?

vexan · Accepted Answer · 2016-06-03 23:46:19Z

1) Hibernate way: use import.sql file or specify files

spring.jpa.properties.hibernate.hbm2ddl.import_files=file1.sql,file2.sql

2) Spring Boot: use default schema.sql & data.sql files or specify files through properties

spring.datasource.schema = file1.sql spring.datasource.data = file1.sql, file2.sql

1 Answer 1