hibernate.hbm2ddl.auto

In our earlier post we saw how creating the SessionFactory object led to execution of DDL, if the hm2ddl property was configured. The SchemaExport or hbm2ddl can be used to generate DDL from the mapping files( or annotations). According to the Hibernate guide
"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."

I used it before to generate the ddls and now decided to study the tool in detail.
NOTE:The database must exist for the SchemaExport utility to work correctly.
I created two model classes, Item and Shelf. Some of the domain rules were :

• Each Item has a name and a unique Serial No.
• Multiple Items can be placed on the same shelf.
• However no two items with the same name can be placed on the same shelf.

The mapping files are as follows:

ItemMapping.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.menagerie.domain.entity.Item" table="ITEM">

        <id name="id" type="long">
            <column name="ID" />
            <generator class="native" />
        </id>

        <property name="name" type="string" index="searchIndex"
            length="100" not-null="true" unique="false" unique-key="itemShelfCombo">
            <column name="NAME" />
        </property>

        <property name="cost" precision="6" scale="2" type="big_decimal">
            <column name="COST" default="0" />
        </property>

        <property name="bestPrice" type="big_decimal">
            <column name="BEST_PRICE" sql-type="decimal(13,3)" check="BEST_PRICE > 0" />
            <!-- <comment>The Best Price cannot fall below 0</comment> -->
        </property>

        <many-to-one name="shelf" column="shelfId" 
              class="com.menagerie.domain.entity.Shelf"
              not-null="true" unique-key="itemShelfCombo" foreign-key="Item_FK_Constraint_1" />

        <property name="serialNo" type="string" length="120"
            not-null="true" unique="true">
            <column name="SERIAL_NO" />
        </property>
    </class>

</hibernate-mapping>

Shelf.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.menagerie.domain.entity.Shelf" table="SHELF">
        <id name="id" type="long">
            <column name="ID" />
            <generator class="native" />
        </id>
    </class>
</hibernate-mapping>

The generated DDL is as follows:

drop table if exists ITEM
drop table if exists SHELF
 create table ITEM (
        ID bigint not null auto_increment,
        NAME varchar(255),
        COST numeric(19,2) default 0,
        BEST_PRICE decimal(13,3) check (BEST_PRICE > 0),
        shelfId bigint not null,
        SERIAL_NO varchar(255),
        primary key (ID),
        unique (NAME, shelfId)
    )
  create table SHELF (
        ID bigint not null auto_increment,
        primary key (ID)
    )
create index searchIndex on ITEM (NAME)
alter table ITEM 
        add index Item_FK_Constraint_1 (shelfId), 
        add constraint Item_FK_Constraint_1

The generated sql indicates:

1. The sql-type attribute was applied to the column BEST_PRICE. 
2. The not-null constraint were applied to the foreign key field (shelfId) 
3. The unique and not-null constraints were however not applied to the name column. 
4. The scale, precision and default attributes were applied correctly. 
5. The named index "searchIndex" was applied on the name column. 
6. The Foreign key constraint was applied with the provided name The Unique key constraint "itemShelfCombo" was applied. 
7. The Unique and not-null constraint was not applied to the column SERIAL_NO.

I then modified the item hbm file as follows:

<property name="name" type="string" index="searchIndex"
            length="100" not-null="true" unique="false" 
            unique-key="itemShelfCombo">
    <column name="NAME" not-null="true"/>
</property>
<property name="serialNo" type="string" length="120"
    unique="true" unique-key="ITEM_UK_Constraint_1">
    <column name="SERIAL_NO" not-null="true"/>
</property>

The new generated sql is as follows:

create table ITEM (
        ID bigint not null auto_increment,
        NAME varchar(255) not null,
        COST numeric(19,2) default 0,
        BEST_PRICE decimal(13,3) check (BEST_PRICE > 0),
        shelfId bigint not null,
        SERIAL_NO varchar(255) not null,
        primary key (ID),
        unique (NAME, shelfId),
        unique (SERIAL_NO)
    )

Thus providing a name for the unique constraint and adding the not-null attribute to the column element fixed the above issues.
A minor point of note here would be that the name of the hbm file can be anything.
The name used "ItemMapping.hbm.xml" is not same as the class Item or the table ITEM. Using the same name for the mapping document as the Model class however is a good practice and leads to better readability. In the next article I'll cover the other settings of the SchemaExport tool.