Hibernate 5. Generate SQL DDL into file

Question

I tried using this class:

Hibernate/JPA: Check generated sql before updating DB Schema (like .NET EF migrations)

I have the following code:

package com.mypackage.jpa.util;

import java.io.File;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class SchemaGenerator {

    private Configuration cfg;

    public static void main(String[] args) throws Exception {

        File f = new File(".");
        String directory = f.getAbsoluteFile() + "/src/main/resources/ddl/generated/";

        String packageName[] = { "com.mypackage.jpa", "com.mypackage.jpa.legacy", "com.mypackage.jpa.local",
                "com.mypackage.jpa.local.impl" };

        SchemaGenerator gen = new SchemaGenerator(packageName);
        gen.generate(Dialect.MYSQL, directory);

    }

    @SuppressWarnings("rawtypes")
    public SchemaGenerator(String[] packagesName) throws Exception {
        cfg = new Configuration();
        cfg.setProperty("hibernate.hbm2ddl.auto", "create");

        for (String packageName : packagesName) {
            for (Class clazz : getClasses(packageName)) {
                cfg.addAnnotatedClass(clazz);
            }
        }
    }

    @SuppressWarnings("rawtypes")
    private List<Class> getClasses(String packageName) throws Exception {
        File directory = null;
        try {
            ClassLoader cld = getClassLoader();
            URL resource = getResource(packageName, cld);
            directory = new File(resource.getFile());
        } catch (NullPointerException ex) {
            throw new ClassNotFoundException(packageName + " (" + directory + ") does not appear to be a valid package");
        }
        return collectClasses(packageName, directory);
    }

    private ClassLoader getClassLoader() throws ClassNotFoundException {
        ClassLoader cld = Thread.currentThread().getContextClassLoader();
        if (cld == null) {
            throw new ClassNotFoundException("Can't get class loader.");
        }
        return cld;
    }

    private URL getResource(String packageName, ClassLoader cld) throws ClassNotFoundException {
        String path = packageName.replace('.', '/');
        URL resource = cld.getResource(path);
        if (resource == null) {
            throw new ClassNotFoundException("No resource for " + path);
        }
        return resource;
    }

    @SuppressWarnings("rawtypes")
    private List<Class> collectClasses(String packageName, File directory) throws ClassNotFoundException {
        List<Class> classes = new ArrayList<>();
        if (directory.exists()) {
            String[] files = directory.list();
            for (String file : files) {
                if (file.endsWith(".class")) {
                    // removes the .class extension
                    classes.add(Class.forName(packageName + '.' + file.substring(0, file.length() - 6)));
                }
            }
        } else {
            throw new ClassNotFoundException(packageName + " is not a valid package");
        }
        return classes;
    }

    private void generate(Dialect dialect, String directory) {
        cfg.setProperty("hibernate.dialect", dialect.getDialectClass());
        SchemaExport export = new SchemaExport(cfg);
        export.setDelimiter(";");
        export.setOutputFile(directory + "ddl_" + dialect.name().toLowerCase() + ".sql");
        export.setFormat(true);
        export.execute(true, false, false, false);
    }

    private static enum Dialect {
        ORACLE("org.hibernate.dialect.Oracle10gDialect"), MYSQL("org.hibernate.dialect.MySQLDialect"), HSQL(
                "org.hibernate.dialect.HSQLDialect"), H2("org.hibernate.dialect.H2Dialect");

        private String dialectClass;

        private Dialect(String dialectClass) {
            this.dialectClass = dialectClass;
        }

        public String getDialectClass() {
            return dialectClass;
        }
    }
}

And I get the following error:

Exception in thread "main" java.lang.UnsupportedOperationException: Attempt to use unsupported SchemaExport constructor accepting org.hibernate.cfg.Configuration; one of the forms accepting org.hibernate.boot.spi.MetadataImplementor should be used instead at org.hibernate.tool.hbm2ddl.SchemaExport.(SchemaExport.java:250) at cu.x.util.SchemaGenerator.generate(SchemaGenerator.java:116) at cu.x.util.SchemaGenerator.main(SchemaGenerator.java:32) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)

I change my code with the following:

ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(cfg.getProperties()).build();
MetadataImplementor metadataImplementor = (MetadataImplementor)
        new MetadataSources(serviceRegistry).buildMetadata();
SchemaExport export = new SchemaExport(metadataImplementor);

But it generates an empty .sql file. I'm using annotated classes. What could be happening?

Yes, my native language is Spanish. I can read English perfectly but writing badly. In my work it is only necessary to read in English, not write or speak. — felix, Nov 16 '15 at 12:19
My apologies, that was intended to be a remark on the reason for my edits. I was not trying to needlessly insult your English. — leigero, Nov 16 '15 at 14:26

score 6 · Answer 1 · answered Nov 17 '15 at 15:58

Apparently the Configuration class can not be used. We must use the MetadataSources class to add the annotated classes.

private void generate(Dialect dialect, String directory, String[] packagesName) throws Exception {

    MetadataSources metadata = new MetadataSources(
            new StandardServiceRegistryBuilder()
                    .applySetting("hibernate.dialect", dialect.getDialectClass())
                    .build());

    for (String packageName : packagesName) {
        log.info("packageName: " + packageName);
        for (Class clazz : getClasses(packageName)) {
            log.info("Class: " + clazz);
            metadata.addAnnotatedClass(clazz);
        }
    }

    SchemaExport export = new SchemaExport(
            (MetadataImplementor) metadata.buildMetadata()
    );

    export.setDelimiter(";");
    export.setOutputFile(directory + "ddl_" + dialect.name().toLowerCase() + ".sql");
    export.setFormat(true);
    export.execute(true, false, false, false);
}

Hi felix, I ran into a similar problem and also found a solution. Maybe it helps you too: http://stackoverflow.com/a/41894432/974186 — René Link, Jan 27 '17 at 12:54

score 4 · Answer 2 · edited Jul 21 '20 at 12:57

The solution of felix don't work anymore on hibernate 5.2 here is a version which is compatible

private void generate(Class dialect, String directory, String... packagesName) throws Exception {

    MetadataSources metadata = new MetadataSources(
            new StandardServiceRegistryBuilder()
                    .applySetting("hibernate.dialect", dialect.getName())
                    .build());

    for (String packageName : packagesName) {
        LOG.info("packageName: " + packageName);
        for (Class clazz : getClasses(packageName)) {
            LOG.info("Class: " + clazz);
            metadata.addAnnotatedClass(clazz);
        }
    }

    MetadataImplementor metadataImplementor = (MetadataImplementor) metadata.buildMetadata();
    SchemaExport export = new SchemaExport();

    export.setDelimiter(";");
    String filename = directory + "ddl_" + dialect.getSimpleName().toLowerCase() + ".sql";
    export.setOutputFile(filename);
    export.setFormat(true);

    //can change the output here
    EnumSet<TargetType> enumSet = EnumSet.of(TargetType.STDOUT);
    export.execute(enumSet, SchemaExport.Action.CREATE, metadataImplementor);
}

would be nice with packages. getClasses found where ? – mjs Sep 26 '20 at 20:17 — mjs, Sep 26 '20 at 20:17

score 3 · Answer 3 · answered Jan 01 '19 at 12:44

Normally tools that dumps the JPA schema are based on SchemaExport tool, which reads only the static metadata.

There is a Maven/Gradle plugin https://github.com/Devskiller/jpa2ddl which generates the JPA schema. In includes all properties, namings strategies, user types, etc.

You can also use it to generate automated schema migrations for Flyway.

score 2 · Answer 4 · answered Nov 30 '18 at 09:47

I wrote a class for generating sql script depends on packages with entites.

Used version of hibernate is '5.3.7.Final'

public final class HibernateExporter {

    private static final Logger LOG = LoggerFactory.getLogger(HibernateExporter.class);
    private static final String OUTPUT_FILE = "schema.sql";
    private static final String DIALECT = "org.hibernate.dialect.H2Dialect";

    private List<String> entityPackages;

    private HibernateExporter(List<String> entityPackages) {
        this.entityPackages = entityPackages;
    }

    public static void main(String[] args) {
        final List<String> entityPackages = Collections.singletonList("pakage.with.entites");

        HibernateExporter exporter = new HibernateExporter(entityPackages);
        exporter.export();
    }

    private void export() {
        SchemaExport export = new SchemaExport();
        export.setOutputFile(OUTPUT_FILE);
        export.setFormat(true);
        export.setDelimiter(";");
        EnumSet<TargetType> types = EnumSet.of(TargetType.SCRIPT);
        Metadata metadata = createMetadataSources().buildMetadata();
        export.execute(types, Action.CREATE, metadata);
    }

    private MetadataSources createMetadataSources() {
        MetadataSources metadata = new MetadataSources(
                new StandardServiceRegistryBuilder()
                        .applySetting("hibernate.dialect", DIALECT)
                        .build());

        for (String entityPackage : entityPackages) {
            final Reflections reflections = new Reflections(entityPackage);
            for (Class<?> cl : reflections.getTypesAnnotatedWith(MappedSuperclass.class)) {
                metadata.addAnnotatedClass(cl);
                LOG.info(String.format("Mapped = %s", cl.getName()));
            }
            for (Class<?> cl : reflections.getTypesAnnotatedWith(Entity.class)) {
                metadata.addAnnotatedClass(cl);
                LOG.info(String.format("Mapped = %s", cl.getName()));
            }
        }
        return metadata;
    }
}

I used _org.reflections.Reflections_ from _org.reflections:reflections:0.9.8_ — Vladimir, Feb 20 '19 at 10:31

Hibernate 5. Generate SQL DDL into file

4 Answers4

Linked