How to generate a ddl creation script with a modern Spring Boot + Data JPA and Hibernate setup?

Question

Currently, I'm using the default @SpringBootApplication annotation with the following properties in application.properties:

spring.datasource.url=jdbc:mysql://localhost/dbname
spring.datasource.username=X
spring.datasource.password=X
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.hibernate.naming_strategy=my.package.CustomNamingStrategy

Since JPA 2.1, I should be able to use the javax.persistence.schema-generation.* properties, but setting them in my application.properties seems to have no effect.

I've seen examples like this that wire up a whole bunch of extra beans, but they aren't using Mysql. And in any case, doing it like that requires me to configure many options that spring is taking care of for me now.

My goals are to:

Generate a schema creation sql script in the MYSQL dialect
without a database connection being required
Output the script in the build directory
Also generating hibernate envers tables would be a huge plus.

I do not want to:

Create/drop schemas on a live database

Lib versions:

   hibernate          : 4.3.11.FINAL
   spring framework   : 4.2.5.RELEASE
   spring-boot        : 1.3.3.RELEASE
   spring-data-jpa    : 1.10.1.RELEASE   // for  querydsl 4 support
   spring-data-commons: 1.12.1.RELEASE   // for  querydsl 4 support

(Using gradle, not maven)

Casey · Accepted Answer · 2016-10-03T09:34:04.397

131

Ah, right after I posted this question a section of the spring data docs caught my eye:

73.5 Configure JPA properties In addition all properties in spring.jpa.properties.* are passed through as normal JPA properties (with the prefix stripped) when the local EntityManagerFactory is created.

So, to answer my own question: prefix the javax.persistence properties with spring.jpa.properties:

spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=create
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=create.sql

After doing this, the schema file was generated automatically in the project root.

edited Oct 03 '16 at 09:34

answered May 01 '16 at 12:02

Casey

6,166
3
35
42

Ammended the answer, essentially after adding that config, the file was created automatically in the generated sources dir. – Casey Oct 03 '16 at 09:34
10

I saw that the file is generated at runtime. Is there a way to generate it 'offline'? – Alessandro Dionisi Apr 05 '17 at 14:46
5

is there a way to add ; after each of the ddl commands? is it possible for this to run without create-drop/create ddl ? is it possible to run without running the whole application ? – Michail Michailidis Nov 09 '17 at 18:42
1

@MichailMichailidis For running it without running the whole app I would recommend checkout out flyway to manage your schema: https://flywaydb.org/ – Casey Nov 15 '17 at 06:35
4

What I'd like to do is generate the DDL to a text file, to make it easier to set up the boilerplate work for a Flyway migration, not to execute it automatically... – Wouter Lievens Jul 11 '18 at 21:32
1

When will it be generated ? – JITHIN_PATHROSE Mar 21 '19 at 07:07
1

Thank you it works, this itself is not enough, please refer this answer as well, https://stackoverflow.com/questions/48423854/missing-semicolons-at-line-end-of-jpa-generated-sql-script – silentsudo May 02 '19 at 03:46
Is there any way to filter the scope? Something like: "generate just from entities from package X" – marcellorvalle Jul 12 '19 at 17:23
I am not sure, that first row is needed. If I want to generate only the script the first declaration is obsolete. – hariprasad Aug 05 '19 at 10:40
3

What if I want to get "update.sql" for existing database instead of create DDLs to empty database? – Ismail Yavuz Dec 18 '19 at 11:13
It appends instead of replace the file. Can this be changed? – Adriano dos Santos Fernandes May 02 '22 at 13:30
Nowadays, Aug 2023, it is more likely that you are using the Jakarta Persistence, so the property names would begin with `spring.jpa.properties.jakarta.persistence.` as opposed to `spring.jpa.properties.javax.persistence.` – peterh Aug 25 '23 at 10:23

score 18 · Answer 2 · edited Mar 07 '19 at 08:50

18

This is yml specific configuration for making spring boot generate ddl creation script in root folder:

spring:
  jpa:
    properties:
      javax:
        persistence:
          schema-generation:
            create-source: metadata
            scripts:
              action: create
              create-target: create.sql

edited Mar 07 '19 at 08:50

Aditya T

1,566
2
13
26

answered Apr 03 '18 at 12:37

thkapasi

219
2
4

10

Please provide some info to your answer. – Jim Jones Apr 03 '18 at 12:40
Here is the same for *.properties file spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata spring.jpa.properties.javax.persistence.schema-generation.scripts.action=create spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=create.sql – Orkhan Hasanli Jun 11 '20 at 01:23
It appends instead of replace the file. Can this be changed? – Adriano dos Santos Fernandes May 02 '22 at 13:30

score 6 · Answer 3 · answered Nov 04 '22 at 10:51

The following code will allow you to generate the DDL for all discovered entities in a stand-alone fashion (independently from Spring Boot). This allows you to generate the schema without having to start the main application.

It uses the following dependencies:

org.hibernate:hibernate-core
org.reflections:reflections

import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.hibernate.tool.schema.TargetType;
import org.reflections.Reflections;
import org.reflections.util.ConfigurationBuilder;

import javax.persistence.Entity;
import java.util.EnumSet;
import java.util.Set;

public class SchemaGenerator {
    public static void main(String... args) {
        new SchemaGenerator().generateSchema();
    }

    private void generateSchema() {
        var serviceRegistry = new StandardServiceRegistryBuilder()
                .applySetting("hibernate.dialect", "<fully qualifified dialect class name>")
                .build();
        var entities = scanForEntities("<package1>", "<package2>");
        MetadataSources metadataSources = new MetadataSources(serviceRegistry);
        entities.forEach(metadataSources::addAnnotatedClass);
        Metadata metadata = metadataSources.buildMetadata();
        SchemaExport schemaExport = new SchemaExport();
        schemaExport.setFormat(true);
        schemaExport.setOutputFile("<output file name>");
        schemaExport.createOnly(EnumSet.of(TargetType.SCRIPT), metadata);
    }

    private Set<Class<?>> scanForEntities(String... packages) {
        var reflections = new Reflections(
                new ConfigurationBuilder()
                        .forPackages(packages)
        );
       return reflections.getTypesAnnotatedWith(Entity.class);
    }
}

JITHIN_PATHROSE · Answer 4 · 2019-08-05T11:48:43.943

Updating your jpa properties will generate the scripts for you.

            <prop key="javax.persistence.schema-generation.scripts.action">drop-and-create</prop>
            <prop key="javax.persistence.schema-generation.scripts.create-target">./create_mssql.sql</prop>
            <prop key="javax.persistence.schema-generation.scripts.drop-target">./drop_mssql.sql</prop>

This will generate the scripts in the given location. There are other properties as well which can be used on various use-cases, please refer here

The whole configuration will look like this

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="my-persistence-unit" transaction-type="JTA">
    <description>Forge Persistence Unit</description>
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>

      <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
      <property name="javax.persistence.sql-load-script-source" value="META-INF/data.sql"/>
    </properties>
  </persistence-unit>
</persistence>

you are asking why it is not working https://stackoverflow.com/questions/55275380/not-able-to-generate-ddl-schema-for-my-spring-entites-in-my-spring-application/55286744#55286744 and posting same answer? — Peter Šály, Mar 21 '19 at 18:28
Can you put there more structured code example? I put your example into `jpaProperties` section under `org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean` in `aplicationContext.xml`, but it does nothing. — hariprasad, Aug 05 '19 at 10:31

How to generate a ddl creation script with a modern Spring Boot + Data JPA and Hibernate setup?

4 Answers4

Linked