generate sql scripts

Question

is there any hibernate utitliy that i can use to connect to database. list all the tables, and generate sql script for "creating tables + data" and save as *.sql ?

Answer 1

Export data from a database as sql

Use the liquibase opensource project

LiquiBase is an open source (LGPL), database-independent library for tracking, managing and applying database changes. It is built on a simple premise: All database changes (structure and data) are stored in an XML-based descriptive manner and checked into source control.

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

Answer 2

If your are using MS Access database, I recommend you use MDBScript, it is very easy to use.