CODEWITHSUNDEEP
javaspringspring-data-jpah2
Gabriel Vendramini
Gabriel Vendramini

Reputation: 81

Error with H2 not creating the schema with my data.sql file

I have this project that I just started and using a model from my teacher I created a simple class to be mapped in the H2. So far no problems I run the application and the table is generated and I try some insert commands and they are fine but when I add the data.sql at the resources folder the project refuses to generate the schema.

Here is my application.properties file:

# DATABASE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.datasource.username=sa
spring.datasource.password=
    
# JPA
spring.jpa.hibernate.ddl-auto=update

# H2
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

The SuperHero.class:

@Entity
@Table(name = "super_hero")
@Data
public class SuperHero {

@Id
@Column(name="id", updatable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@Column(name = "name", nullable = false, columnDefinition = "TEXT")
private String name;
}

The data.sql file:

INSERT INTO super_hero(id, name) VALUES (1, 'Super Man');

This is the error:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 
'dataSourceScriptDatabaseInitializer' defined in class path resource 
[org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: 
Invocation of init method failed; nested exception is 
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script 
statement #1 of URL [file:/C:/Users/gabri/git/plexus-super-heroes/target/classes/data.sql]: INSERT 
INTO SUPER_HERO(id, name) VALUES (1, 'Super Man'); nested exception is 
org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "SUPER_HERO" not found; SQL statement:
INSERT INTO SUPER_HERO(id, name) VALUES (1, 'Super Man') [42102-200]

I tried to change to lowercase and upercase the table name, recreate the project from the scratch but keep getting this error.

Upvotes: 3

Views: 4247

Answers (4)

kinath_ru
kinath_ru

Reputation: 4678

As of SpringBoot 3.2.1 in order to specify the schema.sql file and data.sql file you can use following configurations in application.properties.

spring.sql.init.schema-locations=classpath:sql/schema.sql
spring.sql.init.data-locations=classpath:sql/data.sql

As of above configuration, sql files need to be specified inside resources/sql directory. Note that sql directory is created manually inside the resources folder.

enter image description here

To control the initialization of the database spring.sql.init.mode configuration could be used.

Following is a sample application.properties file.

logging.level.org.springframework.jdbc.datasource.init.ScriptUtils=debug
spring.sql.init.mode=always
spring.sql.init.schema-locations=classpath:sql/schema.sql
spring.sql.init.data-locations=classpath:sql/data.sql
spring.jpa.hibernate.ddl-auto=none

More details on above configurations could be found here

Upvotes: 0

ram ki
ram ki

Reputation: 1

for me also import.sql works fine, I try more experiments with data.sql, I think for in-memory database models better to use import.sql

Upvotes: 0

Gabriel Vendramini
Gabriel Vendramini

Reputation: 81

Many thanks! After renaming the file it worked just fine. I still don't understand why in the other project with the same pom dependencies only with one is for java 1.8 and mine with java 11 this was happening.

Upvotes: 0

Saurabh Singh
Saurabh Singh

Reputation: 218

You can put your statements in import.sql instead of data.sql and retry. Hibernate uses this file to initialize the table.

Upvotes: 4

Related Questions