Automatic schema generation

From Null-pointer

Contents 1 Automatic schema generation 2 Customizing the schema 3 Running the tool 3.1 SchemaExport Command Line Options 4 Properties 5 SchemaExport Connection Properties 6 Using Ant 7 Incremental schema updates 7.1 SchemaUpdate Command Line Options 8 Using Ant for incremental schema updates 9 Schema validation 10 SchemaValidator Command Line Options 11 Using Ant for schema validation

Automatic schema generation

DDL may be generated from your mapping files by a Hibernate utility. The generated schema includes referential integrity constraints (primary and foreign keys) for entity and collection tables. Tables and sequences are also created for mapped identifier generators.

You must specify a SQL Dialect via the hibernate.dialect property when using this tool, as DDL is highly vendor specific.

First, customize your mapping files to improve the generated schema.

Customizing the schema

Many Hibernate mapping elements define optional attributes named length, precision and scale. You may set the length, precision and scale of a column with this attribute.

<property name="zip" length="5"/>
<property name="balance" precision="12" scale="2"/>

Some tags also accept a not-null attribute (for generating a NOT NULL constraint on table columns) and a unique attribute (for generating UNIQUE constraint on table columns).

<many-to-one name="bar" column="barId" not-null="true"/>
<element column="serialNumber" type="long" not-null="true" unique="true"/>

A unique-key attribute may be used to group columns in a single unique key constraint. Currently, the specified value of the unique-key attribute is not used to name the constraint in the generated DDL, only to group the columns in the mapping file.

<many-to-one name="org" column="orgId" unique-key="OrgEmployeeId"/>
<property name="employeeId" unique-key="OrgEmployee"/>

An index attribute specifies the name of an index that will be created using the mapped column or columns. Multiple columns may be grouped into the same index, simply by specifying the same index name.

<property name="lastName" index="CustName"/>
<property name="firstName" index="CustName"/>

A foreign-key attribute may be used to override the name of any generated foreign key constraint.

<many-to-one name="bar" column="barId" foreign-key="FKFooBar"/>

Many mapping elements also accept a child <column> element. This is particularly useful for mapping multi-column types:

<property name="name" type="my.customtypes.Name"/>
    <column name="last" not-null="true" index="bar_idx" length="30"/>
    <column name="first" not-null="true" index="bar_idx" length="20"/>
    <column name="initial"/>
</property>

The default attribute lets you specify a default value for a column (you should assign the same value to the mapped property before saving a new instance of the mapped class).

<property name="credits" type="integer" insert="false">
    <column name="credits" default="10"/>
</property>
<version name="version" type="integer" insert="false">
    <column name="version" default="0"/>
</property>

The sql-type attribute allows the user to override the default mapping of a Hibernate type to SQL datatype.

<property name="balance" type="float">
    <column name="balance" sql-type="decimal(13,3)"/>
</property>

The check attribute allows you to specify a check constraint.

<property name="foo" type="integer">
    <column name="foo" check="foo > 10"/>
</property>
<class name="Foo" table="foos" check="bar < 100.0">
    ...
    <property name="bar" type="float"/>
</class>

Table 20.1. Summary

Attribute	Values	Interpretation
length	number	column length
precision	number column	decimal precision
scale	number column	decimal scale
not-null	false	specfies that the column should be non-nullable
unique	false	specifies that the column should have a unique constraint
index	index_name	specifies the name of a (multi-column) index
unique-key	unique_key_name	specifies the name of a multi-column unique constraint
foreign-key	foreign_key_name	specifies the name of the foreign key constraint generated for an association, for a <one-to-one>, <many-to-one>, <key>, or <many-to-many> mapping element. Note that inverse="true" sides will not be considered by SchemaExport.
sql-type	SQL column	type overrides the default column type (attribute of <column> element only)
default	SQL expression	specify a default value for the column
check	SQL expression	create an SQL check constraint on either column or table

The <comment> element allows you to specify comments for the generated schema.

<class name="Customer" table="CurCust">
    <comment>Current customers only</comment>
    ...
</class>
<property name="balance">
    <column name="bal">
        <comment>Balance in USD</comment>
    </column>
</property>

This results in a comment on table or comment on column statement in the generated DDL (where supported).

Running the tool

The SchemaExport tool writes a DDL script to standard out and/or executes the DDL statements.

java -cp hibernate_classpaths org.hibernate.tool.hbm2ddl.SchemaExport options mapping_files

SchemaExport Command Line Options

Option	Description
--quiet	don't output the script to stdout
--drop	only drop the tables
--create	only create the tables
--text	don't export to the database
--output=my_schema.ddl	output the ddl script to a file
--naming=eg.MyNamingStrategy	select a NamingStrategy
--config=hibernate.cfg.xml	read Hibernate configuration from an XML file
--properties=hibernate.properties	read database properties from a file
--format	format the generated SQL nicely in the script
--delimiter=;	set an end of line delimiter for the script

You may even embed SchemaExport in your application:

Configuration cfg = ....;
new SchemaExport(cfg).create(false, true);

Properties

Database properties may be specified

as system properties with -D<property>

in hibernate.properties

in a named properties file with --properties

The needed properties are:

SchemaExport Connection Properties

Property Name Description hibernate.connection.driver_class jdbc driver class hibernate.connection.url jdbc url hibernate.connection.username database user hibernate.connection.password user password hibernate.dialect dialect

Using Ant

You can call SchemaExport from your Ant build script:

<target name="schemaexport">
    <taskdef name="schemaexport"
        classname="org.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>

Incremental schema updates

The SchemaUpdate tool will update an existing schema with "incremental" changes. Note that SchemaUpdate depends heavily upon the JDBC metadata API, so it will not work with all JDBC drivers.

java -cp hibernate_classpaths org.hibernate.tool.hbm2ddl.SchemaUpdate options mapping_files

SchemaUpdate Command Line Options

Option	Description
--quiet	don't output the script to stdout
--text	don't export the script to the database
--naming=eg.MyNamingStrategy	select a NamingStrategy
--properties=hibernate.properties	read database properties from a file
--config=hibernate.cfg.xml	specify a .cfg.xml file

You may embed SchemaUpdate in your application:

Configuration cfg = ....;
new SchemaUpdate(cfg).execute(false);

Using Ant for incremental schema updates

You can call SchemaUpdate from the Ant script:

<target name="schemaupdate">
    <taskdef name="schemaupdate"
        classname="org.hibernate.tool.hbm2ddl.SchemaUpdateTask"
        classpathref="class.path"/>
    
    <schemaupdate
        properties="hibernate.properties"
        quiet="no">
        <fileset dir="src">
            <include name="**/*.hbm.xml"/>
        </fileset>
    </schemaupdate>
</target>

Schema validation

The SchemaValidator tool will validate that the existing database schema "matches" your mapping documents. Note that SchemaValidator depends heavily upon the JDBC metadata API, so it will not work with all JDBC drivers. This tool is extremely useful for testing.

java -cp hibernate_classpaths org.hibernate.tool.hbm2ddl.SchemaValidator options mapping_files

SchemaValidator Command Line Options

Option	Description
--naming=eg.MyNamingStrategy	select a NamingStrategy
--properties=hibernate.properties	read database properties from a file
--config=hibernate.cfg.xml	specify a .cfg.xml file

You may embed SchemaValidator in your application:

Configuration cfg = ....;
new SchemaValidator(cfg).validate();

Using Ant for schema validation

You can call SchemaValidator from the Ant script:

<target name="schemavalidate">
    <taskdef name="schemavalidator"
        classname="org.hibernate.tool.hbm2ddl.SchemaValidatorTask"
        classpathref="class.path"/>
    
    <schemavalidator
        properties="hibernate.properties">
        <fileset dir="src">
            <include name="**/*.hbm.xml"/>
        </fileset>
    </schemaupdate>
</target>