Auto generate data schema from JPA 2.1 annotated entity classes without a database connection

Question

Two years ago I was working on a project using:

• spring 4.0.3.RELEASE
• jpa 2.0
• hibernate 4.2.7.Final
• java 1.6.X

This project has a maven task hibernate3-maven-plugin which allow us to generate a database schema without any connection to a database (MySQL).

Now we are upgrading this project with:

• java 1.8
• jpa 2.1
• spring 4.2.4.RELEASE
• hibernate 5.0.6.Final

I understand that hibernate3-maven-plugin does not work on JPA 2.1 and hibernate > 4.3.

All the solution I have found need a connection to a database.

For instance: Auto generate data schema from JPA annotated entity classes.

Does anyone know how to generate a database schema offline? All I have is a persistence.xml with all the Entity classes listed.

Kaifeng

This has nothing to do with spring here, please remove the tag – Pressurecook

Answer 1

I was able to mix your Hibernate solution with JPA2.1:

I am now able to add the entity classes from the persistence.xml

This way I can generate the SQl file outside the jar where the entities are located.

This is a temporary solution till hibernate fix this bug

Thanks for your help.

/**
 * 
 */
package com.stackoverflow.common.util.schema;

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

import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.metamodel.ManagedType;
import javax.persistence.metamodel.Metamodel;

import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.BootstrapServiceRegistry;
import org.hibernate.boot.registry.BootstrapServiceRegistryBuilder;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.boot.spi.MetadataImplementor;
import org.hibernate.cfg.Environment;
import org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl;
import org.hibernate.jpa.AvailableSettings;
import org.hibernate.tool.hbm2ddl.SchemaExport;

import org.hibernate.dialect.MySQL5InnoDBDialect;

/**
 *
 */
public class JPA21Hibernate5ExportSchema {

    private static final String JDBC_DRIVER = "org.h2.Driver";
    private static final String JDBC_URL = "jdbc:h2:mem:export;DB_CLOSE_DELAY=-1";
    private static final String JDBC_USERNAME = "sa";
    private static final String JDBC_PASSWORD = "";

    /**
     * 
     */
    public JPA21Hibernate5ExportSchema() {

    }

    public static void main(String[] args) {
        try {
            JPA21Hibernate5ExportSchema hes = new JPA21Hibernate5ExportSchema();
            hes.export(args[0], args[1]);
            System.exit(0);
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(1);
        }

    }

    public void export(String persistenceUnitName, String sqlFile) throws IOException, ClassNotFoundException {

        final BootstrapServiceRegistry bsr = new BootstrapServiceRegistryBuilder().build();
        final MetadataSources metadataSources = new MetadataSources(bsr);

        final StandardServiceRegistryBuilder srrBuilder = new StandardServiceRegistryBuilder(bsr)
                .applySetting(Environment.CONNECTION_PROVIDER, DriverManagerConnectionProviderImpl.class.getName())
                .applySetting(Environment.DIALECT, MySQL5InnoDBDialect.class.getName())
                .applySetting(Environment.URL, JDBC_URL).applySetting(Environment.USER, JDBC_USERNAME)
                .applySetting(Environment.PASS, JDBC_PASSWORD);

        // Use the persistence metamodel to retrieve the Entities classes
        Metamodel metamodel = this.getMetamodel(persistenceUnitName);
        for (final ManagedType<?> managedType : metamodel.getManagedTypes()) {
            metadataSources.addAnnotatedClass(managedType.getJavaType());
        }

        final StandardServiceRegistry ssr = (StandardServiceRegistry) srrBuilder.build();
        final MetadataBuilder metadataBuilder = metadataSources.getMetadataBuilder(ssr);

        final SchemaExport exporter = new SchemaExport((MetadataImplementor) metadataBuilder.build());
        exporter.setOutputFile(sqlFile);
        exporter.setDelimiter(";");
        exporter.setFormat(true);
        exporter.create(false, true);

    }

    /**
     * Retrieve the JPA metamodel from the persistence unit name
     * 
     * @param persistenceUnitName
     * @return
     */
    private Metamodel getMetamodel(String persistenceUnitName) {
        final Properties persistenceProperties = new Properties();
        persistenceProperties.setProperty(AvailableSettings.JDBC_DRIVER, JDBC_DRIVER);
        persistenceProperties.setProperty(AvailableSettings.JDBC_URL, JDBC_URL);
        persistenceProperties.setProperty(AvailableSettings.JDBC_USER, "sa");
        persistenceProperties.setProperty(AvailableSettings.JDBC_PASSWORD, "");
        persistenceProperties.setProperty(org.hibernate.cfg.AvailableSettings.DIALECT,
                MySQL5InnoDBDialect.class.getName());

        final EntityManagerFactory emf = Persistence.createEntityManagerFactory(persistenceUnitName,
                persistenceProperties);
        return emf.getMetamodel();
    }

}

Kaifeng

Answer 2

I follow your idea of using h2 with Mysql dialect but using JPA Persistence.generateSchema(...).

It does work except all commands are not separated by a semi-column...

How can this been done using JPA 2.1?

Otherwise I will switch to your solution.

import java.util.Properties;

import javax.persistence.Persistence;
import javax.persistence.PersistenceException;

import org.hibernate.jpa.AvailableSettings;

/**
 * Generate DDL with hibernate 4+/5:
 * https://mcmap.net/q/375781/-generate-ddl-script-at-maven-build-with-hibernate4-jpa-2-1/27314166#27314166
 * @author dmary
 * 
 */
public class Jpa21SchemaExport {

    /**
     * 
     */
    public Jpa21SchemaExport() {
        // TODO Auto-generated constructor stub
    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        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);

        persistenceProperties.setProperty(AvailableSettings.JDBC_DRIVER,"org.h2.Driver");
        persistenceProperties.setProperty(AvailableSettings.JDBC_URL, "jdbc:h2:mem:export");
        persistenceProperties.setProperty(AvailableSettings.JDBC_USER, "sa");
        persistenceProperties.setProperty(AvailableSettings.JDBC_PASSWORD, "");

        persistenceProperties.setProperty(org.hibernate.cfg.AvailableSettings.DIALECT, "com.wiztivi.sdk.persistence.MySQL5InnoDBUTF8Dialect");

        try
        {
            Persistence.generateSchema(persistenceUnitName, persistenceProperties);
        } catch (PersistenceException pe)
        {
            System.err.println("DDL generation failed: ");
            pe.printStackTrace(System.err); 
        }
    }

Kaifeng

Having no semicolon is a known bug: hibernate.atlassian.net/browse/HHH-10278 – Kaifeng

Strange that app. does not exists without System.exit(0) – Purvey

The easiest workaround is the following: // generate sql with semicolon - workaround for HHH-10278 String systemLineSeparator = System.getProperty("line.separator"); System.setProperty("line.separator", ';' + systemLineSeparator); Persistence.generateSchema(persistenceUnitName, persistenceProperties); – Goshawk

Answer 3

As the other question, you can use hbm2ddl and an embedded database to provide a connection.

For exemple using H2 database (require h2, scannotation, hibernate and common-io):

package com.stackoverflow;
import java.io.File;
import java.io.FileWriter;
import java.io.InputStream;
import java.net.URL;
import java.util.Set;
import javax.persistence.Entity;
import org.apache.commons.io.IOUtils;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.connection.DriverManagerConnectionProvider;
import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.scannotation.AnnotationDB;

public class ExportShema {

    private static final String OUTPUT_SQL_FILE = "target/database.sql";
    private static final String INIT_FILE = "init.sql";
    private static final String DB_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
    private static final String DB_USERNAME = "sa";
    private static final String DB_PASSWORD = "";
    private static final File HBM_DIRECTORY = new File("src/main/resources/com/stackoverflow/domain/");

    public static void main(final String[] args) throws Exception {
        final Configuration cfg = new Configuration();
        cfg.setProperty(Environment.CONNECTION_PROVIDER, DriverManagerConnectionProvider.class.getName());
        //for postgrest schema
        cfg.setProperty(Environment.DIALECT, PostgreSQLDialect.class.getName());
        cfg.setProperty(Environment.URL, DB_URL);
        cfg.setProperty(Environment.USER, DB_USERNAME);
        cfg.setProperty(Environment.PASS, DB_PASSWORD);

        //If you have HBM + annotated class
        cfg.addDirectory(HBM_DIRECTORY);

        final AnnotationDB db = new AnnotationDB();
        db.scanArchives(new URL("file:target/classes/"));
        final Set<String> clazzNames = db.getAnnotationIndex().get(Entity.class.getName());
        for (final String clazzName : clazzNames) {
            cfg.addAnnotatedClass(Class.forName(clazzName));
        }

        final SchemaExport exporter = new SchemaExport(cfg);
        exporter.setOutputFile(OUTPUT_SQL_FILE);
        exporter.setDelimiter(";");
        exporter.setFormat(true);
        exporter.create(false, true);

        try (final InputStream init_file = ExportShema.class.getResourceAsStream(INIT_FILE)) {
            if (init_file != null) {
                final File output = new File(OUTPUT_SQL_FILE);
                try (final FileWriter fw = new FileWriter(output, true)) {
                    final String eol = System.getProperty("line.separator");
                    fw.append(eol + eol);
                    fw.append(IOUtils.toString(init_file));
                }
            }
        }
}
}

you can do this in a unit test or create an annotation processor.

Staphylorrhaphy

I came across this solution but I did not know if the generated SQL is 100ù Mysql compliant. Do you know where I can find this info? – Kaifeng

It will depend on how well Mysql Dialect class is implemented. I use it for postgresql and it works fine. – Staphylorrhaphy