Auto generate data schema from JPA annotated entity classes

I'm using JPA (Hibernate's implementation) to annotate entity classes to persist to a relational database (MySQL or SQL Server). Is there an easy way to auto generate the database schema (table creation scripts) from the annotated classes?

I'm still in the prototyping phase and anticipate frequent schema changes. I would like to be able to specify and change the data model from the annotated code. Grails is similar in that it generates the database from the domain classes.

Solution 1:

You can use hbm2ddl from Hibernate. The docs are here.

Solution 2:

Generate create and drop script for given JPA entities

We use this code to generate the drop and create statements: Just construct this class with all entity classes and call create/dropTableScript.

If needed you can use a persitence.xml and persitance unit name instead. Just say something and I post the code too.

import java.util.Collection;
import java.util.Properties;

import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.dialect.Dialect;
import org.hibernate.ejb.Ejb3Configuration;

/**
 * SQL Creator for Tables according to JPA/Hibernate annotations.
 *
 * Use:
 *
 * {@link #createTablesScript()} To create the table creationg script
 *
 * {@link #dropTablesScript()} to create the table destruction script
 * 
 */
public class SqlTableCreator {

    private final AnnotationConfiguration hibernateConfiguration;
    private final Properties dialectProps;

    public SqlTableCreator(final Collection<Class<?>> entities) {

        final Ejb3Configuration ejb3Configuration = new Ejb3Configuration();
        for (final Class<?> entity : entities) {
            ejb3Configuration.addAnnotatedClass(entity);
        }

        dialectProps = new Properties();
        dialectProps.put("hibernate.dialect", "org.hibernate.dialect.SQLServerDialect");

        hibernateConfiguration = ejb3Configuration.getHibernateConfiguration();
    }

    /**
     * Create the SQL script to create all tables.
     * 
     * @return A {@link String} representing the SQL script.
     */
    public String createTablesScript() {
        final StringBuilder script = new StringBuilder();

        final String[] creationScript = hibernateConfiguration.generateSchemaCreationScript(Dialect
                .getDialect(dialectProps));
        for (final String string : creationScript) {
            script.append(string).append(";\n");
        }
        script.append("\ngo\n\n");

        return script.toString();
    }

    /**
     * Create the SQL script to drop all tables.
     * 
     * @return A {@link String} representing the SQL script.
     */
    public String dropTablesScript() {
        final StringBuilder script = new StringBuilder();

        final String[] creationScript = hibernateConfiguration.generateDropSchemaScript(Dialect
                .getDialect(dialectProps));
        for (final String string : creationScript) {
            script.append(string).append(";\n");
        }
        script.append("\ngo\n\n");

        return script.toString();
    }
}

Solution 3:

As Hibernate 4.3+ now implements JPA 2.1 the appropriate way to generate DDL scripts is to use following set of JPA 2.1 properties :

<property name="javax.persistence.schema-generation.scripts.action" value="create"/>
<property name="javax.persistence.schema-generation.create-source" value="metadata"/>
<property name="javax.persistence.schema-generation.scripts.create-target" value="target/jpa/sql/create-schema.sql"/>

As it will be run at runtime, you may want to execute this DDL generation at build. There is no supported official maven plugin anymore for Hibernate4 probably because Hibernate team is moving to Gradle.

Anyway, this is the JPA 2.1 approach to generate this script programmatically :

import java.io.IOException;
import java.util.Properties;

import javax.persistence.Persistence;

import org.hibernate.jpa.AvailableSettings;

public class JpaSchemaExport {

    public static void main(String[] args) throws IOException {
        execute(args[0], args[1]);
        System.exit(0);
    }

    public static void execute(String persistenceUnitName, String destination) {
        System.out.println("Generating DDL create script to : " + destination);

        final Properties persistenceProperties = new Properties();

        // XXX force persistence properties : remove database target
        persistenceProperties.setProperty(org.hibernate.cfg.AvailableSettings.HBM2DDL_AUTO, "");
        persistenceProperties.setProperty(AvailableSettings.SCHEMA_GEN_DATABASE_ACTION, "none");

        // XXX force persistence properties : define create script target from metadata to destination
        // persistenceProperties.setProperty(AvailableSettings.SCHEMA_GEN_CREATE_SCHEMAS, "true");
        persistenceProperties.setProperty(AvailableSettings.SCHEMA_GEN_SCRIPTS_ACTION, "create");
        persistenceProperties.setProperty(AvailableSettings.SCHEMA_GEN_CREATE_SOURCE, "metadata");
        persistenceProperties.setProperty(AvailableSettings.SCHEMA_GEN_SCRIPTS_CREATE_TARGET, destination);

        Persistence.generateSchema(persistenceUnitName, persistenceProperties);
    }

}

As you can see it's very simple !

You can now use this in an AntTask, or MAVEN build like this (for MAVEN) :

            <plugin>
                <artifactId>maven-antrun-plugin</artifactId>
                <version>1.7</version>
                <executions>
                    <execution>
                        <id>generate-ddl-create</id>
                        <phase>process-classes</phase>
                        <goals>
                            <goal>run</goal>
                        </goals>
                        <configuration>
                            <target>
                                <!-- ANT Task definition -->
                                <java classname="com.orange.tools.jpa.JpaSchemaExport"
                                    fork="true" failonerror="true">
                                    <arg value="${persistenceUnitName}" />
                                    <arg value="target/jpa/sql/schema-create.sql" />
                                    <!-- reference to the passed-in classpath reference -->
                                    <classpath refid="maven.compile.classpath" />
                                </java>
                            </target>
                        </configuration>

                    </execution>
                </executions>
            </plugin>

Solution 4:

As a related note: Documentation for generating database schemas using EclipseLink JPA can be found here.

Solution 5:

Here's an explaination of how to use the hibernate SchemaExport class to do exactly what you want.

http://jandrewthompson.blogspot.com/2009/10/how-to-generate-ddl-scripts-from.html