Generate database schema DDL script with JPA or Hibernate
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?xml version="1.0" encoding="UTF-8" ?> <persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0" xmlns="http://java.sun.com/xml/ns/persistence"> <persistence-unit name="h2" transaction-type="RESOURCE_LOCAL"> <class>UserEntity</class> <exclude-unlisted-classes /> <properties> <property name="hibernate.connection.url" value="jdbc:h2:mem:test" /> <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" /> <property name="javax.persistence.schema-generation.scripts.action" value="drop-and-create" /> <property name="javax.persistence.schema-generation.scripts.create-target" value="db-schema.jpa.ddl" /> <property name="javax.persistence.schema-generation.scripts.drop-target" value="db-schema.jpa.ddl" /> </properties> </persistence-unit> </persistence> |
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.
|
1 2 3 4 5 6 7 |
void createSchemaWithJPA() { final String persistenceUnitName = "h2"; EntityManager em = Persistence.createEntityManagerFactory(persistenceUnitName).createEntityManager(); em.close(); em.getEntityManagerFactory().close(); } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="connection.url">jdbc:h2:mem:test</property> <property name="dialect">org.hibernate.dialect.H2Dialect</property> <mapping class="UserEntity" /> </session-factory> </hibernate-configuration> |
Generating DDL scripts is a little bit more complex than using JPA but still straightforward. Listing 4 shows the needed code snippet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
void createSchemaWithHibernate4() throws Exception { Configuration hibernateConfiguration = new Configuration() // .configure(GenerateDatabaseSchema.class.getClassLoader().getResource("META-INF/hibernate.cfg.xml")); Dialect dialect = (Dialect) Class.forName(hibernateConfiguration.getProperty("dialect")).newInstance(); String[] dropStatements = hibernateConfiguration.generateDropSchemaScript(dialect); String[] createStatements = hibernateConfiguration.generateSchemaCreationScript(dialect); Stream<String> statements = Stream.concat(Arrays.stream(dropStatements), Arrays.stream(createStatements)); try (FileOutputStream fos = new FileOutputStream("db-schema.hibernate4.ddl"); BufferedWriter out = new BufferedWriter(new OutputStreamWriter(fos));) { for (String sql : statements.collect(Collectors.toList())) { out.write(sql); out.newLine(); } } } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
void createSchemaWithHibernate5() { StandardServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder() // .configure("META-INF/hibernate.cfg.xml") // .build(); Metadata metadata = new MetadataSources(serviceRegistry) // .buildMetadata(); new SchemaExport() // .setOutputFile("db-schema.hibernate5.ddl") // .create(EnumSet.of(TargetType.SCRIPT), metadata); metadata.buildSessionFactory().close(); } |
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
