CODEWITHSUNDEEP
postgresqlhibernatejpaspring-boot
Paghillect
Paghillect

Reputation: 848

Spring Boot fails to create database schema

I have the following entities in my Spring Boot web application all defined in the entities package:

AccessPath.java
Component.java
ComponentInstance.java
ComponentOrder.java
Customer.java
HardwareComponent.java
HardwareGraphicsComponent.java
HardwareProcessingComponent.java
HardwareRamComponent.java
HardwareStorageComponent.java
Invoice.java
Manager.java
Order.java
Product.java
Report.java
ScmManager.java
ScmStaff.java
SoftwareComponent.java
Staff.java
Supplier.java
User.java
WarehouseManager.java

I'm using a properly configured PostgreSQL instance as the database:

spring.thymeleaf.cache=false
#
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/foundationdb
spring.datasource.username=foundationdb
spring.datasource.password=
#
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop

When running the application with gradle bootRun I get a lot of error messages regarding invalid SQL statements. It seems like the framework is trying to create the tables in the wrong order and basically fails to create tables for any entity with some sort of relationship. For instance the SQL for creating the order tables fails with the following error most likely because the customer table to which order has a foreign key isn't created yet.

2017-06-09 10:51:35.737 ERROR 85908 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: create table order (id int8 not null, assembly_date date, confirmation_date date, creation_date date, shipping_date date, status varchar(255), customer_username varchar(255), product_id int8, primary key (id))
2017-06-09 10:51:35.737 ERROR 85908 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: syntax error at or near "order"

The only tables created at the end are these:

List of relations
Schema |            Name             | Type  |    Owner
--------+-----------------------------+-------+--------------
public | component                   | table | foundationdb
public | component_instance          | table | foundationdb
public | component_order             | table | foundationdb
public | invoice                     | table | foundationdb
public | product                     | table | foundationdb
public | product_software_components | table | foundationdb
public | report                      | table | foundationdb
public | supplier                    | table | foundationdb

My entities were scattered across different packages at first and I tried putting them all in a single package to streamline the process but that didn't help.

This is definitely a framework related issue as testing with in-memory h2 or HSQLDB instances results in similar errors.

The source code for all these classes is available here: https://gist.github.com/Parsoa/f463fbef548f3a678c1e067e9aad5b21

Any help is appreciated.

Upvotes: 1

Views: 844

Answers (1)

JB Nizet
JB Nizet

Reputation: 691635

order is a reserved SQL keyword (order by ...). Choose another name for your table.

As the error message shows, it has nothing to do with foreign keys:

ERROR: syntax error at or near "order"

Upvotes: 4

Related Questions