Auto generate data schema from JPA annotated entity classes

Question

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.

Check out http://stackoverflow.com/questions/779479/reverse-engineer-ddl-from-jpa-entities — James McMahon, May 15 '09 at 19:58

score 14 · Accepted Answer · edited Jan 26 '22 at 14:59

14

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

edited Jan 26 '22 at 14:59

Community

1
1

answered Nov 18 '08 at 00:20

Vincent Ramdhanie

102,349
23
137
192

11

He asked about generating the schemas solely from the annotated classes. My team wants to do the same thing. I believe that hbm2dll only works with the .hbm.xml mapping files which my team doesn't want to use. I don't think the OP does either. – Omniwombat Jul 17 '09 at 19:32
1

According to "Manning Java Persistence with Hibernate" yes you can with hbm2ddl: "The prerequisite in Hibernate for automatic generation of SQL DDL is always a Hibernate mapping metadata definition, either in XML mapping files or in Java source-code annotations." – dendini May 27 '13 at 15:33

score 13 · Answer 2 · edited May 30 '17 at 15:33

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();
    }
}

Quite nice solution! But using directly persistence.xml would be much more comfortable for us. Could you please post code using persistence.xml? Thanks in advance! — Piotr Sobczyk, Apr 26 '12 at 10:55
This line: public SqlTableCreator(final Collection> entities) should be final Collection entities — r590, Jan 25 '17 at 00:24
@r590 the code block had some problems in the formatting, but the source was correct. I placed an edit. — Mindwin Remember Monica, May 30 '17 at 14:48
I'd like to know how this works with more modern version of HIbernate like Hibernate 5.x — IcedDante, Jul 08 '19 at 18:44

score 10 · Answer 3 · answered Dec 05 '14 at 10:22

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>

java.lang.UnsupportedOperationException: The application must supply JDBC connections — Peter Šály, Dec 22 '16 at 08:35
http://stackoverflow.com/questions/34897410/auto-generate-data-schema-from-jpa-2-1-annotated-entity-classes-without-a-databa — Peter Šály, Dec 22 '16 at 08:35

score 9 · Answer 4 · edited May 26 '16 at 08:11

9

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

edited May 26 '16 at 08:11

answered Dec 04 '08 at 19:33

hohonuuli

1,974
15
15

1

Thanks for that. My question was answered on the first google hit thanks to you. – AlanObject Nov 25 '11 at 17:39

score 6 · Answer 5 · edited Aug 06 '14 at 15:49

6

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

edited Aug 06 '14 at 15:49

peterh

11,875
18
85
108

answered Oct 28 '09 at 15:08

Andrew Thompson

127
1
1

Attempting this solution gives compile errors using JDK1.8 on references to List – r590 Jan 24 '17 at 23:41

score 2 · Answer 6 · answered Oct 19 '12 at 08:36

If you prefer configuring in Spring then this should be helpful:

 <!-- CONTAINER-MANAGED JPA Entity manager factory (No need for persistence.xml)-->
    <bean id="emf" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="jpaVendorAdapter" ref="jpaVendorAdapter"/>
    <!-- Fine Grained JPA properties Create-Drop Records -->
    <property name="jpaProperties">
    <props>
    <prop key="hibernate.hbm2ddl.auto">create</prop>
    </props>
    </property> 
    </bean> 
     <!-- The JPA vendor -->
    <bean id="jpaVendorAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <!-- <property name="database" value="MySQL"/> -->
    <property name="showSql" value="true"/>
    <!--  <property name="generateDdl" value="true"/>  -->
    <property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect"/>      
    </bean> 
     <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="emf" />
     </bean>

score 2 · Answer 7 · answered Aug 27 '13 at 12:25

You can use maven plugin to achieve this.

       <plugin>
            <!-- run command "mvn hibernate3:hbm2ddl" to generate DLL -->
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>hibernate3-maven-plugin</artifactId>
            <version>3.0</version>
            <configuration>
                <hibernatetool>
                    <classpath>
                        <path location="${project.build.directory}/classes" />
                        <path location="${project.basedir}/src/main/resources/META-INF/" />                                               
                    </classpath>   

                    <jpaconfiguration persistenceunit="galleryPersistenceUnit" />                     
                    <hbm2ddl create="true" export="false" destdir="${project.basedir}/target" drop="true" outputfilename="mysql.sql" format="true" console="true"/>
                </hibernatetool>
            </configuration>
        </plugin>

score 1 · Answer 8 · answered Aug 30 '13 at 04:28

<property name="hibernate.hbm2ddl.auto" value="update"/>

Add the above code in the persistence.xml under properties tag. "update" will create the table when first time you run your code, after that, only update the table structures if any changes in domain object.

score 1 · Answer 9 · answered Nov 03 '15 at 13:32

With EclipseLink, you should add property:

<property name="eclipselink.ddl-generation" value="create-tables"/>

As it is said here: http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/p_ddl_generation.htm

My persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="appDB" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>LocalMySQL</jta-data-source>
        <class>entity.Us</class>
        <class>entity.Btl</class>
        <class>entity.Co</class>
        <properties>
            <property name="eclipselink.ddl-generation" value="create-tables"/>
        </properties>
    </persistence-unit>
</persistence>

Auto generate data schema from JPA annotated entity classes

9 Answers9

Linked