Spring boot and Hibernate: print/log DDL

Question

After I've added one or more classes with database mappings (JPA/hibernate), I would like Hibernate to print out the necessary schema updates so I can execute them on the database (through FlyWay for example). I do not want the updates to be executed automatically.

The only property that seems to give some control over this is the following

org.hibernate.tool.hbm2ddl=validate|update|create|create-drop|none

I don't want to update/change anything automatically. I want to set this to validate or none. When I do this, I don't get to see the generated schema.

I classical spring application, I used to use the hibernate SchemaExport class to print the DDL.

SchemaExport schemaExport = new SchemaExport(cfg);
schemaExport.execute(true, false, false, false);

Is there anything similar I can use in Spring Boot?

If you set it to validate/none, then you're explicitly telling it not to generate the DDL. Spring Boot is just standard Spring with some convention over configuration features to minimise the amount of config you need to do. So can still use `SchemaExport`, pretty much as before. — Steve, Jul 31 '14 at 08:25
Agreed but I don't know where to get the `Configuration cfg` parameter to pass to `SchemaExport`. I can't do it after `ConfigurableApplicationContext context = SpringApplication.run(Application.class, args);` since the app is already crashed by then if the database does not have the correct schema. I have set hbm2ddl to 'validate'. Maybe setting this to 'none' might solve this, but I would rather keep that extra check. — bertvh, Jul 31 '14 at 08:41
Have you tried setting it to `none`? The whole point of using `validate` is to fail the start-up if the schema is not valid. And it sounds like you explicitly wish the application to start up despite the fact that the database is not in sync, but without applying migrations. That's what the `none` setting is for. — Steve, Jul 31 '14 at 09:43
Possible duplicate of [How to generate a ddl creation script with a modern Spring Boot + Data JPA and Hibernate setup?](https://stackoverflow.com/questions/36966337/how-to-generate-a-ddl-creation-script-with-a-modern-spring-boot-data-jpa-and-h) — lanoxx, Feb 13 '18 at 10:17

score 32 · Answer 1 · answered Aug 01 '14 at 17:33

32

This is what I do...

First I make my entity changes, then I set these to:

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

Then

re-run my app and let hibernate make the changes to the database.
Go into the logs and copy the sql that hibernate used to update the db
Paste that sql into a new Flyway script
Shudown Boot App
Drop the local database
change ddl-auto back to validate
Restart Boot App
Test to make sure that Flyway made the correct updates. Hibernate and Flyway will now be in sync.

answered Aug 01 '14 at 17:33

Chris Savory

2,597
1
17
27

1

These lines should go in application.properties at the root of your classpath – hd1 Aug 01 '14 at 17:45
1

I had to also include "debug=true" in my application.properties file in addition to the above properties, to get the DDL SQL to show up in the logs. – sdouglass Jan 15 '16 at 18:16
7

You should also include `logging.level.org.hibernate.tool.hbm2ddl=DEBUG` in `application.properties` else those logs never show up. Note that this only allows debug logs related to schema updates, instead of everything, which can overflow your console. – bekce Aug 23 '16 at 13:59
1

IMHO https://stackoverflow.com/a/36966419/474034 provides a much better solution than this. – lanoxx Feb 12 '18 at 12:03

score -1 · Answer 2 · answered Jun 14 '16 at 12:11

The solution of setting the show-sql didn't work for me even with debug turned on, so i ended up writing and running this simple class.

public class SchemaExporter{

public static org.hibernate.cfg.Configuration getConfiguration() {
    org.hibernate.cfg.Configuration cfg = new org.hibernate.cfg.Configuration();
    ClassPathScanningCandidateComponentProvider scanner = new ClassPathScanningCandidateComponentProvider(false);
    scanner.addIncludeFilter(new AnnotationTypeFilter(Entity.class));
    for (BeanDefinition bd : scanner.findCandidateComponents("com.package.where.my.entitybeans.are")) {
        String name = bd.getBeanClassName();
        try {
            System.out.println("Added annotated entity class " + bd.getBeanClassName());
            cfg.addAnnotatedClass(Class.forName(name));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    cfg.setProperty("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
    cfg.setProperty("hibernate.show_sql", "true");
    cfg.setProperty("hibernate.format_sql", "true");
    cfg.setProperty("hibernate.hbm2ddl.auto", "update");
    cfg.setProperty("hibernate.ejb.naming_strategy", "org.hibernate.cfg.ImprovedNamingStrategy");

    cfg.setProperty("hibernate.connection.url", CONNECTIONURL);
    cfg.setProperty("hibernate.connection.username", USERNAME);
    cfg.setProperty("hibernate.connection.password", PWD);
    cfg.setProperty("hibernate.connection.driver", DRIVER);
    return cfg;
}

public static void main(String[] args) {
    SchemaExport export = new SchemaExport(getConfiguration());
    export.setDelimiter(";");
    export.setHaltOnError(true);
    export.setFormat(true);
    export.create(true,true);
}

}

Running it i can see the DDL in the console and continue as Chris suggested

Spring boot and Hibernate: print/log DDL

2 Answers2