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
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!
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&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
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.
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:
Good luck
