Java, Web and Mobile

Blog

Generate database schema DDL script with JPA or Hibernate

|
Java|Java EE

Using an object-relational mapping framework like Hibernate disburdens developers from creating complex DDL scripts to setup an application’s database schema. However, in real life such scripts are still needed. Such, you may need to pass these scripts to your DBA or to setup a test environment on your own. I have done that several times using a semi-automated approach: I started the application on my local machine creating the schema on my local database and run a schema export using tools like DbVisualizer or Toad afterwards. This way takes a lot of time and invites to make mistakes.

So, I wrote a small helper class that takes the configuration of my object-relational mapping framework and generates the desired DDL script. By time I have added support for Hibernate in version 3, 4 and 5 and vendor-independent JPA as well.

JPA

Let’s start for those who are using JPA. Assume we have a persistence.xml as shown in listing 1.

It doesn’t matter if you use transaction-type RESOURCE_LOCAL (Java SE) or JTA (Java EE) or another provider than Hibernate. Important are the three last properties javax.persistence.schema-generation.scripts.* telling JPA whether and where to create DDL scripts. Now, you just need to initialize an EntityManager as I do in listing 2.

You can write a unit test to run that code. It will create a file named “db-schema.jpa.ddl” containing drop and create statements for all your registered classes.

Hibernate 3 and 4

Using native Hibernate you will need to define hibernate.cfg.xml as I did in listing 3.

Generating DDL scripts is a little bit more complex than using JPA but still straightforward. Listing 4 shows the needed code snippet.

That’s all, Hibernate will create file “db-schema.hibernate4.ddl” including all statements for you.

Hibernate 5

If you are using Hibernate as of version 5, the hibernate.cfg.xml file remains untouched. However, the syntax to setup and run the configuration has been optimized. Listing 5 shows the appropriate code to initialize Hibernate’s session factory. The output will be much the same.

Conclusion

The post has shown how to generate DDL scripts based on your project’s configuration. It is a very basic but not a perfect approach. Drop and re-create scripts can be used in test environments or for an initial Go-Live scenario. Migration of an existing database schema is not possible. This can be achieved using Liquibase (which is much more powerful in general).

Happy coding,
Andreas