Generating Update and Drop Scripts

• It can be run at development time, even if you don't have access to the target database.

• It doesn't rely on driver metadata.

• It may be necessary if your application's database connection is not allowed to perform DDL.

• It allows you to send the application's database requirements to a database administrator.

Like SchemaUpdate, SchemaExport can be run from the command line or from Ant. An example is also shown of how to use SchemaExport to simultaneously generate scripts for several databases.

Command-Line Script Generation

You can run SchemaExport from the command line using the command java net.sf.hibernate.tool.hbm2ddl.SchemaExport options mapping_files. The possible options are as shown in Table 11.2.

Table 11.2. SchemaExport Command-Line Options

Option	Description
--quiet	Don't output the script to the console.
--drop	Only generate drop-table statements.
--text	Generate script but don't perform against the database.
--output=my_schema.sql	Specify the file name to output the script.
--config=hibernate.cfg.xml	Specify the Hibernate configuration XML file.
--properties=hibernate.properties	Specify the Hibernate configuration properties from a file.
--format	Format the generated SQL nicely in the script.
--delimiter=;	Set an end-of-line delimiter for the script.

Ant Task Script Generation

Listing 11.3 shows an Ant task that can be used to generate a script. The meaning of the options is as shown in Table 11.2.

Listing 11.3. SchemaExport Ant Task

<target name="schemaexport">
    <taskdef name="schemaexport"
        classname="net.sf.hibernate.tool.hbm2ddl
             .SchemaExportTask"
        classpathref="class.path"/>

    <schemaexport
        properties="hibernate.properties"
        quiet="no"
        text="no"
        drop="no"
        delimiter=";"
        output="schema-export.sql">
        <fileset dir="src">
            <include name="**/*.hbm.xml"/>
        </fileset>
    </schemaexport>
</target>

Generating Multiple Scripts

Hibernate has the advantageous ability of making it easier to support a wide range of databases. By taking advantage of SchemaExport, you can generate schema generation scripts for a wide variety of databases at development time and include them in the application distribution. This is obviously no substitute for testing, as Hibernate relies on the underlying database for a wide variety of features, but it can be helpful if you are interested in using an application with a new database.

Table 6.5 shows the list of dialects included with Hibernate 2.1.2. The sample code shown in Listing 11.4 takes advantage of this list to generate a set of schema generation scripts for a wide suite of databases. The suite of dialects is looped through, with an attempt made to generate scripts for the sample application shown in Chapter 3.

Listing 11.4. Generating Multiple Scripts

package com.cascadetg.ch11;

/** Various Hibernate-related imports */
import net.sf.hibernate.*;
import net.sf.hibernate.cfg.*;
import net.sf.hibernate.tool.hbm2ddl.SchemaExport;
import java.util.Properties;

public class SchemaGeneratorExample
{
    // System constants for the current platform directory
         token
    static String fileSep =
         System.getProperty("file.separator");

    /** We use this session factory to create our sessions */
    public static SessionFactory sessionFactory;

    static String[] db_dialects =
    { "DB2", //
            "net.sf.hibernate.dialect.DB2Dialect", //
            "DB2400", //
            "net.sf.hibernate.dialect.DB2400Dialect", //
            "Firebird", //
            "net.sf.hibernate.dialect.FirebirdDialect", //
            "FrontBase", //
            "net.sf.hibernate.dialect.FrontBaseDialect", //
            "Generic", //
            "net.sf.hibernate.dialect.GenericDialect", //
            "HypersonicSQL", //
            "net.sf.hibernate.dialect.HSQLDialect", //
            "Informix", //
            "net.sf.hibernate.dialect.InformixDialect", //
            "Informix9", //
            "net.sf.hibernate.dialect.Informix9Dialect", //
            "Ingres", //
            "net.sf.hibernate.dialect.IngresDialect", //
            "Interbase", //
            "net.sf.hibernate.dialect.InterbaseDialect", //
            "Mckoi SQL", //
            "net.sf.hibernate.dialect.MckoiDialect", //
            "Microsoft SQL Server", //
            "net.sf.hibernate.dialect.SQLServerDialect", //
            "MySQL", //
            "net.sf.hibernate.dialect.MySQLDialect", //
            "Oracle 9", //
            "net.sf.hibernate.dialect.Oracle9Dialect", //
            "Oracle", //
            "net.sf.hibernate.dialect.OracleDialect", //
            "Pointbase", //
            "net.sf.hibernate.dialect.PointbaseDialect", //
            "PostgreSQL", //
            "net.sf.hibernate.dialect.PostgreSQLDialect", //
            "Progress", //
            "net.sf.hibernate.dialect.ProgressDialect", //
            "SAP DB", //
            "net.sf.hibernate.dialect.SAPDBDialect", //
            "Sybase Anywhere", //
            "net.sf.hibernate.dialect.SybaseAnywhereDialect",
            "Sybase 11.9.2", //
            "net.sf.hibernate.dialect.Sybase11_9_2Dialect", //
            "Sybase", //
            "net.sf.hibernate.dialect.SybaseDialect",};

    public static void main(String[] args)
    {
        initialization();
    }

    /**
     * Loads the Hibernate configuration information, sets up the
     * database and the Hibernate session factory.
     */
    public static void initialization()
    {
        System.out.println("initialization");
        try
        {
            Configuration myConfiguration = new
                 Configuration();

            myConfiguration
                    .addClass(com.cascadetg.ch03.Owner.class);
            myConfiguration
                    .addClass(com.cascadetg.ch03.Artifact
                         .class);

            Properties myProperties = new Properties();

            for (int i = 0; i < db_dialects.length; i = i + 2)
            {
                String dialect_name = db_dialects[i];
                String dialect_class = db_dialects[i + 1];

                String dialect_file =
                     dialect_name.toLowerCase();
                dialect_file = dialect_file.replace(' ', '_');
                dialect_file += (".sql");

                String path = "com" + fileSep + "cascadetg"
                        + fileSep + "ch11" + fileSep;

                System.out.println("Generating " +
                     dialect_name);

                // Note that this is the only Hibernate property
                // set.  In particular, there is no JDBC
                // connectivity data, nor are we specifying a
                // driver!
                myProperties.put("hibernate.dialect",
                        dialect_class);
                try
                {
                    // Load the *.hbm.xml files as set in the
                    // config, and set the dialect.
                    SchemaExport mySchemaExport = new
                         SchemaExport(
                            myConfiguration, myProperties);

                    mySchemaExport.setDelimiter(";");

                    // Despite the name, the generated create
                    // scripts WILL include drop statements at
                    // the top of the script!
                    mySchemaExport.setOutputFile(path + "create_"
                            + dialect_file);
                    mySchemaExport.create(false, false) ;

                    // Generates DROP statements only
                    mySchemaExport.setOutputFile(path + "drop_"
                            + dialect_file);
                    mySchemaExport.drop(false, false);

                    System.out.println(dialect_name + " OK.");

                } catch (Exception e)
                {
                    System.out.println(e.getMessage());
                }
            }

        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}

Running the application in Listing 11.4 produces the results shown in Listing 11.5. As can be seen, the native identity generator is not supported by many databases. For broader support, a Hibernate-driven identity generator would be a better choice to support a wider range of databases (for more information on identity generation, see Chapter 6). Figure 11.1 shows the resulting schema script files.

Listing 11.5. Generating Multiple Scripts

initialization
Generating DB2
DB2 OK.
Generating DB2400
DB2400 OK.
Generating Firebird
Dialect does not support identity-key generation
Generating FrontBase
Dialect does not support identity-key generation
Generating Generic
Dialect does not support identity-key generation
Generating HypersonicSQL
HypersonicSQL OK.
Generating Informix
Informix OK.
Generating Informix9
Informix9 OK.
Generating Ingres
Dialect does not support identity-key generation
Generating Interbase
Dialect does not support identity-key generation
Generating Mckoi SQL
Dialect does not support identity-key generation
Generating Microsoft SQL Server
Microsoft SQL Server OK.
Generating MySQL
MySQL OK.
Generating Oracle 9
Dialect does not support identity-key generation
Generating Oracle
Dialect does not support identity-key generation
Generating Pointbase
Dialect does not support identity-key generation
Generating PostgreSQL
Dialect does not support identity-key generation
Generating Progress
Dialect does not support identity-key generation
Generating SAP DB
Dialect does not support identity-key generation
Generating Sybase Anywhere
Sybase Anywhere OK.
Generating Sybase 11.9.2
Sybase 11.9.2 OK.
Generating Sybase
Sybase OK.