Spring Boot doesn't work when I add sql script

Question

I have a simple Restfull app. It works. But, when I add .sql files - the app doesn't work. I add schema.sql

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

CREATE TABLE country (
  id SERIAL PRIMARY KEY NOT NULL ,
  county_name CHAR (30) NOT NULL UNIQUE );

CREATE TABLE city (
  id SERIAL PRIMARY KEY NOT NULL ,
  city_name CHAR (30) NOT NULL,
  country_id INTEGER REFERENCES country,
  UNIQUE (city_name, country_id));

CREATE TABLE street (
  id SERIAL PRIMARY KEY NOT NULL,
  street_name CHAR (30) NOT NULL,
  city_id INTEGER REFERENCES city,
  building_number CHAR(10),
  UNIQUE (street_name, city_id));

and data.sql

INSERT INTO country (county_name) VALUES ('Ukraine');
INSERT INTO country (county_name) VALUES ('France');
INSERT INTO country (county_name) VALUES ('Italy');
INSERT INTO country (county_name) VALUES ('German');
INSERT INTO country (county_name) VALUES ('England');

application.properties

spring.jpa.database=postgresql
spring.datasource.platform=postgres
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.database.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/ubr
spring.datasource.username=postgres
spring.datasource.password=1
server.port=8080

After first run - it hasn't an error, but after second run I have a stacktrace with many exeptions. What I do wrong?

Stephane Nicoll · Accepted Answer · 2016-07-17 14:08:42Z

4

You are mixing two different features.

spring.jpa.hibernate.ddl-auto=create-drop operates when you want Hibernate to automatically update the schema.

schema.sql and data.sql management is a Spring Boot feature. The doc is pretty explicit about JPA usage

If you want to use the schema.sql initialization in a JPA app (with Hibernate) then ddl-auto=create-drop will lead to errors if Hibernate tries to create the same tables. To avoid those errors set ddl-auto explicitly to "" (preferable) or "none". Whether or not you use ddl-auto=create-drop you can always use data.sql to initialize new data.

answered Jul 17, 2016 at 14:08

Stephane Nicoll

33.3k10 gold badges104 silver badges96 bronze badges

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

1 Comment

Mi Lu Over a year ago

I changed my .properties: spring.jpa.hibernate.ddl-auto=update; spring.datasourse.data=/resources/data.sql spring.datasourse.schema=/resources/schema.sql; spring.datasource.continueOnError=true and it works. All I did right or something wrong?

Collectives™ on Stack Overflow

Spring Boot doesn't work when I add sql script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related