Creating tables and importing data from .sql files in Spring boot

Question

Current apporach:

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/db_name
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto=update

spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.connection.zeroDateTimeBehavior=convertToNull
spring.datasource.initialization-mode=always
spring.jpa.properties.hibernate.hbm2ddl.import_files=<name>.sql 
spring.datasource.platform=mysql

Not sure what am I missing, and why in this configuration, the .sql files are not executed?

Romil Patel · Accepted Answer · 2019-08-17 13:36:16Z

2

UPDATE

We can use

spring.datasource.schema= # Schema (DDL) script resource references.
spring.datasource.data= # Data (DML) script resource references.

No need to change the SQL filenames
Can Keep schema generation and insertion in the same file
Can specify multiple files

spring.datasource.schema = classpath:/abc.sql,classpath:/abc2.sql

NOTE:

For schema generation and insertion in the same file do not use spring.datasource.data, we have to use spring.datasource.schema
Keep all files in src/main/resources
set spring.jpa.hibernate.ddl-auto=none

Initial Answer

Spring boot already configures Hibernate to create your schema based on your entities. To create it using SQL (in src/main/resources) files set

spring.jpa.hibernate.ddl-auto=none

Create schema.sql (to create the table) and data.sql (to insert the records) in src/main/resources

schema.sql

CREATE TABLE country (
    id   INTEGER      NOT NULL AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (id)
);

data.sql

INSERT INTO country (name) VALUES ('India');
INSERT INTO country (name) VALUES ('Brazil');
INSERT INTO country (name) VALUES ('USA');
INSERT INTO country (name) VALUES ('Italy');

application.properties

spring.datasource.platform=mysql
spring.datasource.initialization-mode=always
spring.datasource.url=jdbc:mysql://localhost:3306/db_name?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

edited Aug 17, 2019 at 13:36

answered Aug 16, 2019 at 16:56

Romil Patel

14k9 gold badges54 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Schroedinger Over a year ago

is there a way to do it without renaming the files to be data.sql and schema.sql. In fact I' ve data and schemas in one file. In other words, is it possible to make it worksin such condiitons: other name than you specified and with mixed schemas and data in one .sql file?

Romil Patel Over a year ago

Hello, @Schroedinger I have updated the answer, please accept and upvote the answer if it was helpful to you

Collectives™ on Stack Overflow

Creating tables and importing data from .sql files in Spring boot

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related