CODEWITHSUNDEEP
hibernatespring-bootspring-dataspring-data-jpa
Casey
Casey

Reputation: 6326

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

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:

I do not want to:

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)

Upvotes: 75

Views: 88154

Answers (5)

YaMann
YaMann

Reputation: 11

Other way: SpringBootTest + application.properties with above parameters.

Upvotes: 0

Werner Altewischer
Werner Altewischer

Reputation: 10466

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);
    }
}

Upvotes: 6

JITHIN_PATHROSE
JITHIN_PATHROSE

Reputation: 1406

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>

Upvotes: 0

thkapasi
thkapasi

Reputation: 239

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

Upvotes: 20

Casey
Casey

Reputation: 6326

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.

Upvotes: 135

Related Questions