Initialise a database using Hibernate
spring.jpa.hibernate.ddl-auto= [none, validate, update, create-drop] controls how to generate database tables.
- create: Hibernate first drops existing tables, then creates new tables.
- update: Comparing with existing tables or columns in the database, and update the existing according to the diff. It never deletes the existing tables or columns even if they are no more required by the application.
- create-drop: similar to create, with the addition that Hibernate will drop the database after all operations are completed. Typically used for unit testing.
- validate: Hibernate only validate whether the tables and columns exist, otherwise it throws an exception.
- none: this value effectively turns off the DDL generation.
Spring boot may recognize database type and gives default value.
If it finds that it is an embedded database: spring.jpa.hibernate.ddl-auto=create-drop;
so maybe you need to turn it off; otherwise, hibernate will automatically create the schema in line with entities.
If it finds that it is a real database, then spring.jpa.hibernate.ddl-auto=none;
It will automatically find schema.sql and data.sql to create tables and populating data.
spring.jpa.show-sql=true displaying SQL in the console.
spring.jpa.properties.hibernate.format_sql=true Formatting the SQL
//doesn't work
spring.datasource.initialize=false turn off initialising using scripts.
Spring Boot can automatically create the schema (DDL scripts) of your DataSource and initialize it (DML scripts). It loads SQL from the standard root classpath locations: schema.sql and data.sql, respectively. In addition, Spring Boot processes the schema-${platform}.sql and data-${platform}.sql files (if present), where platform is the value of spring.datasource.platform. This allows you to switch to database-specific scripts if necessary. For example, you might choose to set it to the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql, and so on).
You can set spring.jpa.hibernate.ddl-auto explicitly and the standard Hibernate property values are none, validate, update, create, and create-drop. Spring Boot chooses a default value for you based on whether it thinks your database is embedded. It defaults to create-drop if no schema manager has been detected or none in all other cases. An embedded database is detected by looking at the Connection type. hsqldb, h2, and derby are embedded, and others are not. Be careful when switching from in-memory to a ‘real’ database that you do not make assumptions about the existence of the tables and data in the new platform. You either have to set ddl-auto explicitly or use one of the other mechanisms to initialize the database.
Spring provides a JPA-specific property which Hibernate uses for DDL generation: spring.jpa.hibernate.ddl-auto.
The standard Hibernate property values are: create, update, create-drop, validate and none:
create – Hibernate first drops existing tables, then creates new tables
update – the object model created based on the mappings (annotations or XML) is compared with the existing schema, and then Hibernate updates the schema according to the diff. It never deletes the existing tables or columns even if they are no more required by the application
create-drop – similar to create, with the addition that Hibernate will drop the database after all operations are completed. Typically used for unit testing
validate – Hibernate only validates whether the tables and columns exist, otherwise it throws an exception
none – this value effectively turns off the DDL generation
Spring Boot internally defaults this parameter value to create-drop if no schema manager has been detected, otherwise none for all other cases.
