Generate a database update scripts with Hibernate/Spring

Question

I have a project where we use to rely on hibernate to update database with changes (hibernate.hbm2ddl.auto=update)... even on prod... I am migrating that to use liquibase instead.

My only concern is that not everyone is a sql guru in my team, so I am looking for a simple way to generate the sql script that hibernate would have done if it was updating the database.

We are all using Intellij IDEA but couldn't find this feature.

Do you know any tool capable of doing that?

Thank you

Is this useful: http://www.jetbrains.com/idea/webhelp/using-hibernate-console.html#generateScripts — JamesB, Nov 12 '13 at 23:50
Not at all ^^. AFAIK, with IDEA you can generate the full DB DDL but the incremental script to update your Database according to your JPA mapping. — tibo, Nov 13 '13 at 00:34
Working link to jetbrains: https://www.jetbrains.com/help/idea/working-with-the-hibernate-console.html#d898341e340 — Shervin Asgari, Jun 18 '18 at 11:44

score 12 · Answer 1 · answered Mar 20 '16 at 13:22

To actually display the scripts generated by Hibernate from your mappings, you can:

as suggested by Andrei I, add those two lines to your application.properties:

spring.jpa.hibernate.ddl-auto=validate
logging.level.org.hibernate.tool.hbm2ddl=DEBUG

Source for Hibernate 4.3: SchemaUpdate.java

or manually generate those scripts with the following code:

LocalSessionFactoryBuilder sessionFactory = new LocalSessionFactoryBuilder(dataSource);
sessionFactory.scanPackages("your.package.containing.entities");
Dialect dialect = new MySQL5Dialect(); // select your dialect
DatabaseMetadata metadata = new DatabaseMetadata(dataSource.getConnection(), dialect, sessionFactory);
List<SchemaUpdateScript> scripts = sessionFactory.generateSchemaUpdateScriptList(dialect, metadata);

Formatter formatter = FormatStyle.DDL.getFormatter();
for (SchemaUpdateScript script : scripts) {
   System.err.println(formatter.format(script.getScript()) + ";");
}

You can even add this code in a @Test, as described here.

Good luck!

do you have a working solution for Hibernate 5.2? Thanks – drenda Mar 15 '18 at 22:44 — drenda, Mar 15 '18 at 22:44

score 3 · Answer 2 · answered Dec 24 '15 at 09:37

Full schema dumped with mvn hibernate4:export and pom.xml:

<!-- To export full DDL schema as it seeing by Hibernate run: mvn hibernate4:export -->
<plugin>
    <!-- http://mydevnotes.nicus.it/2013/03/generate-ddl-with-maven-jpa-hibernate-4.html -->
    <groupId>de.juplo</groupId>
    <artifactId>hibernate4-maven-plugin</artifactId>
    <version>1.1.0</version>
    <configuration>
        <hibernateDialect>org.hibernate.dialect.MySQLDialect</hibernateDialect>
        <delimiter>;</delimiter>
        <target>SCRIPT</target>
        <outputFile>${project.build.directory}/schema-hibernate4-maven-plugin.sql</outputFile>
    </configuration>
</plugin>

You may copy necessary parts.

With LiquiBase you have more options by generating diff between DB and Hibernate mapping file / annotations by mvn liquibase:diff and corresponding part in pom.xml:

<plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>${liquibase.version}</version>
    <configuration>
        <changeLogFile>${basedir}/src/main/resources/sql/master.xml</changeLogFile>
        <propertyFile>${liquibase.profile}</propertyFile>
        <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
        <logging>debug</logging>
        <outputFileEncoding>utf-8</outputFileEncoding>
        <driver>com.mysql.jdbc.Driver</driver>
        <!-- <url>jdbc:mysql://localhost:3306/app?autoReconnect=true&amp;characterEncoding=utf-8</url> -->
        <!-- <username>AAAAAA</username> -->
        <!-- <password>BBBBBB</password> -->
        <defaultSchemaName>testx</defaultSchemaName>
        <changelogSchemaName>testx</changelogSchemaName>

        <!-- For mvn liquibase:updateSQL -->
        <migrationSqlOutputFile>migration.sql</migrationSqlOutputFile>

        <referenceUrl>hibernate:spring:com.app.domain?dialect=org.hibernate.dialect.MySQLDialect</referenceUrl>
        <diffChangeLogFile>changelogDiff.xml</diffChangeLogFile>
        <diffTypes>tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints</diffTypes>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
            <version>${spring-data.version}</version>
        </dependency>
        <!-- https://github.com/liquibase/liquibase-hibernate/wiki -->
        <dependency>
            <groupId>org.liquibase.ext</groupId>
            <artifactId>liquibase-hibernate4.2</artifactId>
            <version>3.5</version>
        </dependency>
    </dependencies>
</plugin>

You may read my notes about http://tips.defun.work/liquibase.html

score 2 · Answer 3 · edited May 23 '17 at 11:47

Well, I don't know an extremely easy solution, but may be the following will help you:

on your local machines enable your logging lib to show the DDL queries. For example take a look here for an example and here for hibernate log categories.

After saving all changes to your local DB (with hibernate.hbm2ddl.auto=update) you will have wanted output. On production use hibernate.hbm2ddl.auto=validate.

Eventually you might consider doing that on a special instance of server, with all DDL logging data saved in a special file.

Also you could check, whether hibernate puts the update queries when hibernate.hbm2ddl.auto=validate.

score 2 · Answer 4 · answered Feb 20 '15 at 09:09

Please have a look at the following article:

Schema generation script from Hibernate

It's a bit old, but adjusted a bit should do the trick:

Replace "create" with "update" in the configuration object
Adjust the base package name of your domain
Only generate it with the dialect you need. In the example three dialects are used for Oracle, MySQL and HSQL.

Good luck

Generate a database update scripts with Hibernate/Spring

4 Answers4

Linked