Log sql from hibernate if spring.jpa.hibernate.ddl-auto=update

Question

I have a spring boo application and I cant seem to see the SQL in the logs if I set spring.jpa.hibernate.ddl-auto=update in application properties.

What I find weird is that I can see the SQL generated if I set the property to create-drop spring.jpa.hibernate.ddl-auto=create-drop

I dont understand why it works in one case and not in the other and I dont want to drop the database everytime I deploy.

As far as logging properties i have them set like so

spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL94Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.use-new-id-generator-mappings=true
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
spring.jpa.properties.hibernate.format_sql=true

logging.level.org.hibernate=INFO
#this line shows the sql statement in the logs
logging.level.org.hibernate.tool.hbm2ddl=trace
logging.level.org.hibernate.tool.hbm2ddl.SchemaUpdate = trace
#this line shows sql values in the logs
logging.level.org.hibernate.type.descriptor.sql=trace
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE

AKrush95 · Accepted Answer · 2019-04-22 16:50:28Z

0

I never let hibernate migrate the schema directly. The following class dumps all necessary DDL changes to the log and a file. I recommend to put the SQL in a Flyway migration afterwards.

public class SchemaUpdateService implements InitializingBean {


    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd--HHmm");

    private static final Logger log = LoggerFactory.getLogger(SchemaUpdateService.class);

    // Simply here to ensure a fully build up hibernate stuff.
    @Autowired
    LocalContainerEntityManagerFactoryBean factoryBean;


    @SuppressWarnings({"deprecation", "unused"})
    @Override
    public void afterPropertiesSet() throws Exception {

        String fileName = System.getProperty("java.io.tmpdir") + "/database-migration-" + formatter.format(LocalDateTime.now()) + ".sql";

        Metadata metadata = HibernateInfoHolder.getMetadata();
        SessionFactoryServiceRegistry serviceRegistry = HibernateInfoHolder.getServiceRegistry();
        org.hibernate.tool.hbm2ddl.SchemaUpdate schemaUpdate = new org.hibernate.tool.hbm2ddl.SchemaUpdate();
        schemaUpdate.setDelimiter(";");
        schemaUpdate.setOutputFile(fileName);
        schemaUpdate.setFormat(true);

        log.warn("--------------------------------------------------------------------------------------------------");
        log.warn("Starting SCHEMA MIGRATION lookup, please add the following SQL code (if any) to a flyway migration");
        log.warn("Working on schema: " +  factoryBean.getJpaPropertyMap().get("schema_name") );
        schemaUpdate.execute(  EnumSet.of(TargetType.SCRIPT, TargetType.STDOUT), metadata, serviceRegistry);

        File file = new File(fileName);
        if (file.exists() && file.length() != 0) {  // migrations present.
            log.warn("Migrations also written to: " + fileName);
        } else if (file.exists()) {  // delete empty files
            log.warn("No migrations");
            file.delete();
        }

        log.warn("END OF SCHEMA MIGRATION lookup");
        log.warn("--------------------------------------------------------------------------------------------------");
    }
}

edited Apr 22, 2019 at 16:50

AKrush95

1,4616 gold badges21 silver badges35 bronze badges

answered Apr 11, 2018 at 13:35

fkurth

9181 gold badge8 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Miksel Over a year ago

Thanks for replying, yea the plan was indeed to log the SQL so i could then copy paste into the Flyway migration file. You just have a better plan that i had. I'm having a problem with your solution, the metada is always null. From what I have seen I think i need an Integrator to populate HibernateInfoHolder, I have done that but I dont think the Integrator is running.

fkurth Over a year ago

You should instantiate this class as a spring bean (@Service) to ensure hibernate metadata fully built.

Miksel Over a year ago

Is there a reason why it does not output creation of tables or column, is there something that will output all the SQL that is sent to postgres so i can have a correct migration file?

fkurth Over a year ago

i tested this against mysql and oracle, and there the creation of tables and columns are shown.

Collectives™ on Stack Overflow

Log sql from hibernate if spring.jpa.hibernate.ddl-auto=update

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related