Generate a database update scripts with Hibernate/

问题:

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

回答1:

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!

回答2:

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.

回答3:

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

回答4:

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